ARTICLE

Learn SSIS Microsoft SQL Server 2008: Part 2

Posted by Naveed Zaman Articles | SQL June 29, 2013
This article explains SSIS data conversion, a powerful feature of Microsoft SQL server.
Reader Level:

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

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.

image1.jpg

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

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

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

image4.jpg

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

image5.jpg

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

image7.jpg
 
Step 7: Click the test connection button.

image8.jpg

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

image9.jpg

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.

image10.jpg

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.

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

image12.jpg
 
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.

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

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

image15.jpg

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.

image16.jpg
 
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.

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

image18.jpg
 
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.

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

image20.jpg

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.

image21.jpg

image22.jpg

COMMENT USING

Trending up