1) Know your file location! In this case I have a 10 GB txt file saved to my desktop
2) Open up Visual Studio > File > New > Project > Integration Services > OK
a.Make sure you have your SSIS toolbox displayed! If not…Select “SSIS Toolbox” from the “SSIS” drop-down
3) Drag a [Data Flow Task] onto the designer window and double click on the task
4) Drag [Source Assistant] onto the designer window within the Data Flow Task and select “Flat File” > “New…”
5) Select “Browse” and navigate to your file (C:\Users\Login\Desktop\LargeFile.txt), select your file and then in the Flat File Connection Manager Editor go to the “Columns” tab to preview your data.
a.The editor will automatically read the data and choose row delimiters and column delimiters. You can adjust them in this editor if it the automated process was incorrect. This is where you can specify a tab delimited file, etc.
6) The “Advanced” tab allows you to rename columns, define data types, specify the size of the data, etc. The default datatype and size is a string with 50 characters.
7) TIP: if you have a file with 10, 50, 100 or 500 columns, changing the datatype and size within the editor can be a hassle. To make bulk edits to many columns in your connection, go to your “Solution Explorer” and right-click on the package (Package.dtsx) and view the code. Then, do a simple find-and-replace on the column width that you would like to replace. In the example I am making each column 500 characters instead of 50. Make sure you save the file!
8) Your last step is to add a destination table by dragging the [Destination Assistant] into your designer window > SQL Server > New.
9) Drag the blue arrow to the destination > double-click on the destination > Create your new table by selecting [New…] and map all of your columns by toggling to the “Mappings” tab.
10) Finish up by selecting “Ok” and you are ready to run your package.
a.You can run the package in the design window or save the package and run it using the SSIS Execution Wizard (or you can execute it a number of other ways)
Importing several GBs of data should take a matter of minutes. Best of Luck!
By: Joe Cramer
Posted by admin