SQL Server Integration Services (SSIS) – Part 2 – Transforming SQL Data to Excel Sheet

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 18601 red flag
Rating: 5 out of 5  
 2 vote(s)

This article is the 2nd part of the series of article on SSIS packaging and transformations.


 Download source code for SQL Server Integration Services (SSIS) – Part 2 – Transforming SQL Data to Excel Sheet

Introduction

In this article we will see how data can be transferred from SQL Server DB to Excel sheet.  Refer to my previous article (Part 1) on the basics of SSIS and steps to create the first SSIS package at the below url

http://www.dotnetfunda.com/articles/article1164-sql-server-integration-services-ssis-part-1-basics-of-ssis-and-creati-.aspx


Steps to achieve transforming data from SQL DB to Excel:

Step 1 and Step 2 – Refer to my previous article on the steps at the below url

http://www.dotnetfunda.com/articles/article1164-sql-server-integration-services-ssis-part-1-basics-of-ssis-and-creati-.aspx

Step 3: Since our task is to transform the data from SQL Server DB to Excel sheet , add a Data Flow task in the control task tab of package.dtsx as shown in the below screen and double click on the task which will redirect to the Data Flow tab


Step 4: In Data Flow tab add an OLE DB Source task and configure it to the database where we need to perform the transformations and select the table as shown in the below screen. In order to follow the steps on how to configure the SQL DB configuration check my previous article as stated above.


Step 5: Now add the destination source (Excel Data source) as shown in the below screen and make the configuration. To do the configuration first drag the green arrow from SQL data source to the Excel destination tasks and then double click on the excel destination task it will open the below screen for configuration


Step 6: Do the mapping of the columns from both the source and the destination tasks as shown in the below screen


Step 7: Once everything is configured (Source and destination) Press F5 to execute the task and you can find the result at the path where we specified the excel sheet. Check the download section to see the excel sheet which is created with this project.



Conclusion

In this article we have seen how to transform the data from DB to Excel sheet:



Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Posted by: Tripati_tutu on: 2/21/2011 | Points: 25
Nice article in SSIS.
Really this is helpful for us....
Posted by: Karthikanbarasan on: 2/21/2011 | Points: 25
Thanks Tripati_tutu...
Posted by: Mohankind on: 2/22/2012 | Points: 25
Hay Karthik Anbarasan,

When i was trying to work out for this exercise, i was getting datatype mismatch error. Can you please explain me how to configure datatype of columns to be specified in excel sheet???


Login to post response

Comment using Facebook(Author doesn't get notification)