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!
asked Oct 05, 2017 at 02:42 AM in Default
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:
Here is the example of the line of code generating the bulk insert statement:
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.
The script is attached updated cs script
Hope this helps.