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.
Now drag the Excel Destination to the Container window. Connect the Split with the Destination by dragging the green arrow. This will open the Output Selection window where I chose “Big Salary’ from the drop-down.
Double-click the Excel Destination. The editor window allows you to select the path to the Excel spreadsheet. You can also choose the Excel Sheet that you will be updating.
Select “Mappings” to view how the input columns will map the the output columns.
You can see in the following screenshot, a message that states “cannot convert between unicode and non-unicode string data types.” To resolve this issue we must place a Data Conversion item between the Conditional Split and the Excel Destination.
Highlight the arrow/line between Split and Excel Destination, then delete. Now drag and drop Data Conversion to the Container window and connect.
Double Click Data Conversion and change Data Type to “Unicode string [DT_WSTR]. I also like to change the Output Alias to “converted.name”. Change all 4 fields.
Next we must remap the Excel Destination to include the new converted records. Double Click Excel Destination and choose Mappings. Change/select from the Input Column the converted names.
Do the same steps for Small Salary Destination. Final should look like this with no errors.
Run the Debug to output the proper data to the respective Excel Files. The “Show output from:” “Debug” will show in the bottom right-hand corner. When finished, select Debug >>> Stop Debugging.
Check your 2 spreadsheets and each should have the proper records according to the Condition in the Control Split Transformation Editor.