Learn SSIS Microsoft SQL Server 2008: Part 2

Before reading this article, please go through the following articles:

• Learn SSIS Microsoft SQL Server 2008: Part1

SQL Server to Excel File Using SSIS

Dear reader, we learned a few basic concepts of SSIS in part 1. Today we will discuss anther powerful feature SSIS, data conversion. Data conversion is used to make the data type compatible from SQL Server to Excel. So today we will discuss and create the simple example for the specified mentioned topic.
 
Step 1: Open the business intelligence development studio.



Step 2: Drag and drop the data flow task to the control flow.


 
Step 3: Drag and drop the OLE DB Source on the data flow tab.


 
Step 4: Right-click on the OLE DB Source and select edit.

Step 5: Click the new button and create the new connection.




 
Step 6: You need to provide the necessary details of your SQL Server. Including the server name, password, and database name.


 
Step 7: Click the test connection button.

Step 8: Now select the data access mode -> SQL command mode and write the query for the SQL command text.



Step 9: Now drag and drop the data conversion control onto the data flow tab. This will help us to convert the data type from SQL Server to Excel.

Step 10: Now drag and drop the green line from the OLE DB source to the data conversion control on the data flow tab. This will link both controls to each other.


 
Step 11: Now select the data conversion tool and right-click and select the edit option.


 
Step 12: Now select all the data fields from the available input columns as shown in the picture. Now we need to convert the data type into Unicode and change the length to 100. This is a very important step, we must change the type appropriately, otherwise we will get the error. Then click the "Ok" button.


 
Step 13: In this step we need to create the new connection.


 
Step 14: Now select "Execl" from the connection manager type.

Step 15: Now select the Excel file path from the browse button. It's important that we must create the Excel file then select the Excel version.


 
Step 16: Now click the new button to create the table. In our case it's the sheet of the Excel file; you can change the sheet name and select the columns according to your requirements.


 
Step 17: Now we must select the name of the Excel sheet.


 
Step 18: Dear readers, now click the Mapping option. That is a very important step. Now we need to map the field from the source to the destination files. I have selected the copy columns as the input column. I will discuss these mappings in our advanced topic further. Click the "OK" button.


 
Step 19: Now our example is complete. We need to right-click the data flow tab and select the execute button.

Step 20: After the successful execution you will see the green highlighted color on the tools with the number of records shifted. But if you get a red highlighted color then it means you have made a mistake. You get the error on the progress tab. Now we need to open the Excel file that we have select as the source file and you will find the required transform.