This is a fun SQL Server Integration Services (SSIS) project that will help you become familiar with creating an ETL packages. ETL stands for Extract >>> Transform >>> Load.
The Source File is a tab-delimited text file you should name “source.txt”. Copy/Paste the “raw” (view raw) text below into the file and save it to a folder on your desktop (or anywhere for that matter).
Now create 2 Excel Files with heading to match below and place them in the same folder. Call one “DestinationBigSalary.xls” and the second “DestinationSmallSalary.xls”.
Creating the SSIS Package
Open Sql Server Data Tools (SSDT). Use BIDS if SQL Server 2008 R2.
Select File >>> New >>> Project
Choose Integration Services Project and select a destination and name. I went with the default destination and named my project “ETL example.”
After selecting OK, your screen should look similar to this.
Right-Click in the Connection Manager tab at the bottom and select “New Flat File Connection”
Flat File Connection Manager Editor opens with “General” selected. In the Connection manager name I entered “Source File.” I browsed to the “source.txt” on the desktop, which we created at the beginning of this post.
Next, select “Columns” to verify that the columns are properly mapped.
Preview lets you see how the source file is divided and the initial rows that are skipped.
Click the OK button to create the connection to the source file.
Drag and drop the Data Flow Task from the SSIS Toolbox to the Control Flow tab window.
Select the Data Flow tab and drag the Flat File Source from the “Other Sources” section to the Container window.
Double Click the Flat File Source to open the source editor. You can edit the “Output Column” names at this point.
We want the data to flow 2 separate direction. We want the Salaries over 25,000 to go to one Excel Spreadsheet and the rest go to the second Spreadsheet. We will use the conditional Split to separate these records.
Drag the “Conditional Split” from the “Common” secton of the SSIS toolbar to the Container Window. Drag the Green Arrow for the Flat File Source to the Conditional Split.
Double Click the Conditional Split to open the Editor. You must convert the salary to DT_WSTR by adding to the front of the number. You can drag this to the front of the salary number from the “Type Casts” in the right column above. I set the Default output name to “Small Salary,” which will display the remaining records.