Hi all,
I have a problem with a Sort Transformation, I have a CSV file with 200'000 rows the csv file is about 30Mb. When the rows are processed in the Sort Transformation, SSIS generates around 160 temp files of about 10Mb each.
How can avoid so many temp files to be generated ?
Is there a way to change the order that "select * from #table" returns data? It doesn't appear to return data in the same order that it was inserted into the table. This wasn't a problem with the same code in SQL Server 2005.
View 5 Replies View RelatedHi Experts,
When you create a SSIS Project in Visual Studio and start adding many, many SSIS and several people do it, they are not being sorted when they get loaded into Visual Studio environment, unlike other objects such as web forms in ASP.NET or classes. as you can imagine, if you have about 100 of SSIS, it makes it very hard for anyone to find one SSIS to alter it.
all these packages are being stored in some xml file, right? so, my boss wants me to write a tool that will open this file and order SSIS by names to make it easier for anyone in a team. i was wondering if anyone has done something like this already, or started. any leads, comments will be appreciated.
thanks a bunch!
Using SQL Server 7 I have the following files hanging out and I'm not sure what they are for or if I can delete them.
My data and logs are on the same RAID 5 drives. They are located in the E:DATA and E:LOGS directories.
I have the following files on the root of my E: drive
tempdbData.ndf
tempdbLog.ndf
tempdbData1.ndf
tempdbLog1.ndf
this goes on up to tempdbData4.ndf. The data files are large up to 2 GBytes. The dates are not current except for the last 2 sequential files.
Anybody know what they are there for and my I delete any of these files?
Thanks and Thanks again......
Hi,
My scenario:
I have 4 different flat files types each having different no. of column, order of columns etc. I want to upload all the 4 types into the same destination table in the SQL database. Before uploading I need to apply transformation to each column in the flat files. The transformations could be like
1) Multipying the source column by 100
2) Put an if condition for 2 source columns and then select one column to be copied into the destination.
I have the flat files schema with me and also all the transformations that are required.
Question:
Can SSIS provide me with a component that can read the flat file schema and the transformations from the database and apply them to the source data and then upload it to the constant destination table? Can derived column transformation be provided with the input columns list and the transformation to be done on each dynamically?
Why I want this way?
In future there can be an addition of extra flat file formats and we want to keep the changes to the SSIS packages to he mininum. Just entereing the addiional schema and transformation details in the database should run the package on the new flat file successfully.
Thanks for your time.
Regards,
$wapnil
I re-started the SQL Service.
I did numerous commands with no luck.
Shrink DB, Shrink files and shrink DB.
I tried the GUI but it bombs out.
I have a custom Data Flow task that creates temp files to the system temp directory during processing. A lot of times, we'll use SSIS to do one data transformation, running and tweaking the package along the way... we do this in the designer ... if we notice something that's incorrect in the data view, we just hit the stop button and fix it. However, when we do this, the Cleanup() function isn't called, and my temp files are left in the temp directory, when they really ought to be disposed of.
Is there a method that gets called every time when the DtsDebugHost quits, whether it finished, didn't finish properly, or was stopped in the middle? What would be a good way (other than having some service that monitors what temp files are used by what processes) to clean up temp files after we don't need them?
~Steve
I am developing application with SQLCE2.0, NETCF1.0,Sp1,VS2003.
I found there is some files are created under "Temp" folder by the system with size "0B".
Why/when these file are created? Do I need to clean it periodicly? If not, will this cause exception like "Not enough storage is available to complete this operation"?
Thanks.
We are using the Import/Export wizard to create some simple packages to transfer tables. When doing a lrge number of tables, the 'Optimize for many tables' option is automatically selected (as noted in BOL). What we've found is that the package creates a bunch of temp files in the creator's Documents and Settings....Temp folder. Needless to say this package cannot be re-run later, nor scheduled, since the path referenced doesn't necessarily exist on the server. Is there a way to specify where these files should be created so thatthe package is re-usable and still be optimized?
Steve
Hello,
I created a SSIS solution for reading data from dbase and storing them in SQL Server. In a ForEachDirectory-Loop up to one thousand dbase files are read and stored. The system where the packages are running has 16 GB RAM.
For the first few hundred dbase files everything goes fine, but then, the RAM seems not to suffice any more and a temp file is created (I changed the path in BufferTempStoragePath).
How can it be that there is a need to create temp files if there is so much RAM available?
Why is the RAM filled more and more during the SSIS package execution?
Is there anything I can do to release some of it? (it is running in a loop and there is no need to store all the data)
Could it be caused by dbase?? (I use Microsoft Jet 4.0 OLE DB Provider)
Another thing is that the temp file is not stored in the path I set in BufferTempStoragePath.
There are sufficient permissions set, but temp file is still created in user temp folder...
Any kind of help is very much appreciated!
Best Regards,
Stefan
I am having with trying to import XLSX files into SQL 2012 64 Bit.
I have installed the Access driver (AccessDatabaseEngine_x64.exe)
I have configured the script to run the following SP
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
[Code] ....
So I first create my Temp Table
The run the SP above then I run the insert into the Temp table defined
INSERT INTO tempdb.dbo.TempTRBZ (IsNew,CoID, Zip, City, County,StateCode,Rate,Taxable,TaxShip,TaxLab,CountryID,StateID)
SELECT * FROM OPENROWSET( 'Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;Database=C:TempNotInTrbzJan.xlsx;HDR=YES','SELECT * FROM [Data$]')
[Code] ....
The error message I get back is
Msg 7303, Level 16, State 1, Line 4
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
What I have set wrong on the import? Using SSIS at this point is not a real option.
How do you sort files from the Foreach loop container?
I love this component, except I can't get it to sort by filename.
Nice to know how to sort by timestamps also.
Dave
Hi,
If you have two synchronous transformation components and the input of the second is connected to the output of the first, does the first transformation process (loop through) all rows in the buffer before outputting these rows to the second transformation? Or does the first transformation output each individual row to the second transormation as soon as it has finished processing it?
Thanks in advance,
Lawrie.
tell me the difference between Audit transformation and rowcount transformation.
Because audit and rowcount transformation will provide the environment variables.
Only difference i am finding is rowcount returns the count of rows its updating .
Apart from these is there any other difference?
Tell me the scenario where i need to use the audit transformation.
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
I want to get different sums from an item base. I have taken the number of every order (ord) from an order base and goes to the item-base like this:
Set Rss = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT array(sum(numitems)) from oitems where orderid= " & ord
Rss.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
I have not tried to run the sql- the array is just a dummy to show the idea.
Actually I want to make an array of the sum of items with the same numitems (i.e. the same productnumber)
if the sql runs I will write the array like this:
While Not rss.eof
response.write array(sum(numitems))
'or whatever way I have to write it
rss.Movenext
Wend
...anyone who has a good idea of how to do this?
Hi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
The DB Enginee is SQL2005 SP1. In MSSMS
the following query always finishes within 1 minute
SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= '3/19/2008' AND t1.EndTime <= '3/20/2008'
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
however, the following always takes 10+ minutes
declare @pStartDate datetime
declare @pEndDate datetime
set @pStartDate='3/19/2008'
set @pEndDate='3/20/2008'
SELECT *
FROM t1 INNER JOIN t2 ON t1.key= t2.key
where t1.StartTime >= @pStartDate AND t1.EndTime <= @pEndDate
AND t2.StartTime >= t1.StartTime AND t2.StartTime < t1.EndTime
Both return same result. The only diff. is the blue part. Why the second one performances so badly?
Thanks.
I am transferring data from oracle and getting below error message.
I using 4 data flow tasks with in a single control flow and all the 4 tasks quueries same table but populates data in to different sql tables based on the where contidion
[OLE DB Source 1 [853]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01652: unable to extend temp segment by 64 in tablespace TEMP ".
I have a data migration script that takes about an hour half to complete. The script conists of several insert statements that pull data from another sqlserver database while doing a fair amount of manipulation.
If I put a "go" in after every insert statement. The job finishes in 30Seconds. can some explain this huge performance change.
I have a table which visibility can be toggled by a text box. By default it is invisible. After it is made visible, clicking a sortable column header makes the table invisible. Does this mean sorting makes the table go back to its default visibility?
Hi all,
I'm sick of VS crashing when I add/remove DataViewers any idea why this could happen ?
Sébastien.
Are there any way I can make changes to my database without stopping the web site? I'm using SQL Server 2005 Express for my web shop. And each time I have to add or change a table or Stored Procedure I have to stop the site. What I do is to stop the web site (in IIS), restart the IIS and then open the database in the SQL Server 2005 Express Management tool. If I don't "release" the file first I get an error when I try to open the database file in the Management tool.But there must be a better way to do things? I also would like to take bacup of the database without stopping the site, but how? There must be others that have had the same problems? Regards, Sigurd
View 3 Replies View RelatedUntil today, I was always under the impression that left vs. right was determined by which side of the comparison operator the table was located.
In other words:
LEFT JOIN LeftTable.ID = RightTable.ID
would pull all the records from LeftTable and those that matched from from RightTable and that:
RIGHT JOIN RightTable.ID = LeftTable.ID
would pull exactly the same result set but I was wrong. So, if it is not the table position in relation to the comparison operator, is it simply that the tables listed first in the FROM clause aren the ones "Left" of those subsequently entered?
Hi everyone,
A relatively simple question but I have no been able to find an answer.
We have a SQL Server mirror Active/passive with High availability. So the usual Witness with failover.
However, we are using the Mirror server for reporting and letting the users query without limit. We sometimes have problems (slowness) with the Principle server and I'm wondering the following.
If the mirror server in high availability mode is maxed out will this affect the principle server because it takes longer to commit the data on both servers?
It seems obvious but I would like a clear response.
Thanks.
Hi Guys,
I've been programming with SQL 7 for about a year and my company has finally decided to go SQL 2k5.
I've come accross a really irritating error when writing to the DB via ADO in ASP pages. I have a column in a table that is auto-incremental.
In SQL server 7 you just make an ADODB.Command object and enter the SQL query 'insert into table (columns) values ('val...') now for SQL 7 I can completely leave out the auto-incremental column (called 'ErrorNo') and simply specify the other columns and values in my insert query.
e.g. where my table is called master_error:
ErrorNo int identity (1, 1) not null
,ReportedBy char(10)
,ExpectedFixDate datetime
With ErrorNo being an auto-incremental identity, my query would be
INSERT INTO master_error (ReportedBy, ExpectedFixDate) VALUES ('Ben','01 Sep 2007')
this works perfectly with ADODB.Command when writing to SQL Server 7 from IIS 5.0
however when I execute the exact same command on the exact same table using ADODB.Command writing to SQL Server 2005 from IIS 6.0 I get an 'error 500 internal server error'
IIS log states:
|166|80040e14|[Microsoft][ODBC_SQL_Server_Driver][SQL_Server]Cannot_insert_the_value_NULL_into_column_'ErrorNo'__table_'ReACToR_V1.0.dbo.master_error';_column_does_not_allow_nulls._INSERT_fails. 80 - 192.168.78.159 Mozilla/4.0+(compatible;+MSIE+7.0;+Windows+NT+5.1;+.NET+CLR+1.1.4322;+.NET+CLR+2.0.50727;+.NET+CLR+3.0.04506.30) 500 0 0
I thought perhaps SQL 2005 might have different syntax so I typed the query directly into SQL Server 2005's version of query analyser and guess what... it worked fine.
I can't tell where the error lies. I find it hard to beleive that the error is in the code of my ASP page as it works perfectly against a sql 7 db.
Any ideas?
Many Thanks,
Ben Ward
Hi There,
I have a database which I attached to Sql Server 2005 Express (With advanced features) and enabled full text search. I created a FT catalog and FT index on a column and can successfully run the following query:SELECT expertise
FROM faculty_user
WHERE FREETEXT (expertise, 'Berry' )My issue is that full text search requires user instances to be turned off. Simply setting "User Instance=False" over in my web.config's connection string (in VWD) does not solve the problem.I still get the following error and I think it may be caused by my ASPNETDB. I can't find my connection string to ASPNETDB to turn off user instances and I can't diagnose my other connection strings because of it.Generating user instances in SQL Server is disabled
Here is a sample of my other connection strings:
<add name="myConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|myDatabase.mdf;Integrated Security=True;User Instance=False" providerName="System.Data.SqlClient"/>
From what I understand, I should also not be attaching the database from my connection string like this because it relys on User Instances? I am officially confused! Thanks for reading, -Derek
Hello All & Thanks in advance for your help!Background:1) tblT_Documents is the primary parent transaction table that has 10fields and about 250,000 rows2) There are 9 child tables with each having 3 fields each, their ownPK; the FK back to the parent table; and the unique data for thattable. There is a one to many relation between the parent and each ofthe 9 child rows. Each child table has between 100,000 and 300,000rows.3) There are indexes on every field of the child tables (though Idon't believe that they are helping in this situation)4) The client needs to be presented a view that has 5 of the mainfields from the parent table, along with any and all correspondingdata from the child tables.5) The client will select this view by doing some pattern-matchingsearch on one of the child records' detail (e.g. field-name LIKE%search-item% - so much for the indexes...)Problem:When I do the simple join of just the parent with one of the children,the search works *fairly* well and returns the five parent fields andthe corresponding matching child field.However, as soon as I add any one of the other child records to simplydisplay it's unique data along with the previously obtained results,the resulting query hangs.Is the overall structure of the tables not conducive to this kind ofquery? Is this a situation where de-normalization will be required toobtain the desired results? Or, more hopefully, am I just an idiotand there is some simpler solution to this problem?!Thanks again for your assistance!- Ed
View 9 Replies View RelatedHi,
I have one database configured with the Recovery Model "Simple".
I am getting alot of full transaction log messages... is this supposed to happen?
Another question is:
Imagine i am in a middle of a big select into statement... and in another query i run the backup truncate log... am i going to loose information on the other batch ("select into?")??
Kind Regards,
Luis Simões
I have two machines running SQL Express that I am attempting to use as proxies for a client application as the security admin has deemed that outside clients can not connect directly to the main DB servers.
When I have one of the SQL Express boxes subscribed to my SQL 2005 standard server, everything is happy, but when I attempt to add the second SQL Express box as a subscriber, everything seems to blow up.
OK, I'm setup as a merge with updates and no filter since I want all of the servers to get all of the data. I'm also on a non-standard port but have my aliases setup, and I don't have any connection issues. When I run the subscription wizard from the management studio, it comes back with "Success", but about 2 seconds later, I get errors on both subscribers from the replication monitor. Oh, I am aslo setup for push subscriptions and am setup to run continuously.
Here is the error message from the rep. monitor.
Error messages:
No subscription is on this publication or article. (Source: MSSQLServer, Error number: 14050)
Get help: http://help/14050
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
What I find odd is that It only dies when I try to add a second subscriber, and then it kills both of them. Any help or ideas on where I should look would be most welcome.
Thanks!
When attempting to use the FTP task to download a file from a Mainframe system the task fails stating the filename as invalid because it doesn't begin with a "/".
Adding the slash to the front of the file name causes the mainframe to be unable to locate the file.
Commandline version of FTP.exe mimicks this behavior by working perfectly when the filename has no slash, and being unable to find the file when the slash is present.
Why does microsoft force a filename to start with a "/" and is there a way to make SSIS skip the validation phase for the FTP task?
Hi, this is my first post and I'm relatively new to SSIS so please go easy on me.
Without going into too much detail about it, I've set up a simple SSIS package which does this in a nutshell:
Foreach loop picks up all *.xls files in a given folder
1 - Puts the name of the current spreadsheet into a variable
2 - File System Task copies the current spreadsheet ("abc.xls") to a file called "work.xls"
3 - Data Flow task performs data extraction on "work.xls" and puts it into a SQL server database
4 - File System Task moves "abc.xls" into a "success" folder
Continues with loop - move onto next spreadsheet
This works fine, so long as the spreadsheets all have the same number of columns.
As soon as one of them has a column missing (believe me, this will happen - we're dealing with users here) the package falls over at step 3.
When the package comes across an erroneous spreadsheet, what I'd like to do is move the offending file to a failure folder (making step 4 either a success or failure file move) and carry on with the next one.
I know that you can have an error path (the red line) from any step within the dataflow task, but this doesn't help me because the error lies in the structure of the spreadsheet and not the contents.
I've already come up with a work around whereby each file is moved into the failures folder just after step 2, then moved from the failures folder into the success folder at step 4.
This almost gives me what I want, although of course the package still falls over whenever it encounters a dodgy looking spreadsheet.
Is there any way that I can get the package to do what I'm after?
Many thanks,
Simon
Greetings,
Facing a strange problem, but obviously not expected earlier on, I am trying to execute a stored procedure via ADO which refrences a linked server table and I get an error specifying
"
OLEDB provider SQLOLEDB does not contain table "<DatabaseName>"."<owner>"."<tableName>"
"
Has any one else encountered this problem before ?
regards
Govind