My team loads source files on a weekly basis. Some are small (20,000) records and some are up to 30 million records or more in a few instances. What is the fastest way to load with least amount of effort. Most of our files are pipe delimited but could be comma delimited or sometimes fixed width. The problem with SSIS is that the metadata is static and you must trick it or recreated it each time. I wish it had a way to read the metadata from excel or header record and then load the data. I have tried an approach suggested by TechBrothersIT website using a script task but it loads millions of records slowly. Any ideas are greatly appreciated if anyone has run into this or can point me to some really good sites. Thanks!
I believe that using bulk insert will help to speed up the load process. If this question is related to the [SSIS 2012 Visual C# Script Task] question then you already have everything in place to change it based on the following: 1. Instead of reading the file line by line and then create and execute the insert statement which inserts just one row into the table, read just the first line from the file (to figure out how it is delimited) 2. Close and dispose the open stream at this time. 3. Using C# code generate the bulk insert statement. This will be one statement per table, not per row. 4. Execute the statement Here is the example of the line of code generating the bulk insert statement: // The line here refers to just one line, which could be any line, no need to read them all string delimiter = line.Contains("|") ? "|" : ","; string query = "bulk insert dbo.[" + filenameonly + "] from '" + filePath + "' with (fieldterminator = '" + delimiter + "', firstrow = 2)"; Here is the example of T-SQL solution which:reads first bunch of characters from the file to figure out delimiter; executes the bulk insert statement. The C# is easier to implement though, so the the script below is just an example showing how to figure out the way the data is delimited and then quickly insert it via bulk insert. -- Read the first 100 characters or so (change the number if needed), just enough -- to figure out whether the file is pipe-delimited or not. If not then lets -- assume that it is comma-delimited for the sake of this example declare @contents varchar(100) = ( select t.BulkColumn from openrowset(bulk 'C:\Temp\SomeTable.csv', single_clob) t ); declare @delimiter char(1) = case when charindex('|', @contents) > 0 then '|' else ',' end; -- This could be a dynamic statement created based on the delimiter, but let it be static. -- The assumption here is that the file has column headers in first row, so the row is excluded if @delimiter = '|' bulk insert dbo.SomeTable from 'C:\Temp\SomeTable.csv' with (fieldterminator = '|', firstrow = 2); else bulk insert dbo.SomeTable from 'C:\Temp\SomeTable.csv' with (fieldterminator = ',', firstrow = 2); go The script is attached [updated cs script] Hope this helps. Oleg : /storage/temp/