Click here to Skip to main content
12,511,734 members (44,435 online)
Click here to Skip to main content
Add your own
alternative version

Stats

84.1K views
7 bookmarked
Posted

SSIS Package: Export Data from Database, Daily to New Excel Without Using Script Task

, 13 Jun 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Export data from a SQL Server database table to an Excel sheet day wise.

Export data from database to Excel sheet with a new Excel per day.

SSIS => SQL Server Integration Services, is used for ETL (Extract, Transform and Load) type of work. It is advanced version of DTS we can say. Here we can schedule packages as jobs and it will execute without human intervention.

In this article we will export the data from SQL database table to Excel sheet day wise.

To start with, you will need to install SQL Server 2008 with BIDS (Business Intelligence Development Studio)

Now moving forward the first step would be go to

File => New Project => Select "Business Intelligence Projects" from left panel and "Integration Service Project" from right panel. Give proper name and save at desired location.

On OK click, it will open Package.dtsx.

Create a Template folder somewhere on your hard drive. In that folder create a sample excel with just the headers that you want in the final excel.

We will use this template to create new excel every day with same format and new data.

Select File System Task from toolbox and drag on Control Flow Tab.

This task will be used to create excel every day before the actual code runs.

Change the name of task to if you want.

To change the properties of task, double click on it. Popup File System Task Editor will open up

Set the Source Connection to Template excel. Operation to Copy file (Which is set by default)

While setting the source connection do below things..

Click within the area in front of Source connection and select <new connection..>.

Below popup should be displayed.. 

Select Existing File there and click on browse, select the template excel over here

Click on ok.

Now let’s set the destination connection.

Set IsDestinationPathVariable to true.

And select the Destination variable

Change the name of variable if you want and click on Ok.

Set OverwriteDestination to true or false according to need

Now Select the Variable in left panel and press F4 to check the properties of it.

  

Set EvaluateExpression to true.. Now change the expression and set to below

Now go to Control Flow tab and double click on "Data Flow Task".  It will switch to Data Flow Tab. Now check the toolbox it has been changed now.

It will contain three sections, source, destination and transformation. Now drag and drop the OLE DB Source, Data Conversion and Excel Destination to Data Flow Pane respectively.

And connect the green output of OLE DB Source to Data Conversion (Which will be useful for converting between non-unicode and unicode data) to Excel Destination as below

Now double click on OLEDB source and set the connection to whatever database you have.

Once this is done go to Columns section as below 

After that double click on excel destination and set the excel connection to Template excel for now.

Click on ok and do the modifications as in below figure. I’m selecting Data sheet of my Template excel workbook as it contains final columns that will exist in your excel workbook.

Once this is done, Click on mappings and do the mappings as necessary.

After source and destination settings are done. Right click on Data Conversion task and select edit Advanced Properties. And set the input output column names and data type to Unicode strings.  

Click on Ok. Data Conversion part is done.

Netx is again go to Excel Connection Manager, which is visible in below pane. Click on expression tab and map the Excel file path property to the variable that we created earlier as shown below

Select dataflow task and Set the delay validation to true, to avoid getting some errors at compile time related to excel sheet which may come.

Run the application now and package will copy template excel sheet from Template folder to destination folder with new name which will include today’s date. In the next date package will copy the data from database to excel sheet.

Happy Learning

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

TheDhruv
United States United States
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Questiondynamically generate excel and import data of table using ssis Pin
Member 1057360417-Jun-14 21:21
memberMember 1057360417-Jun-14 21:21 
QuestionVariable is used as a source and destination is empty. Pin
skcwebworld31-Dec-13 3:40
memberskcwebworld31-Dec-13 3:40 
SuggestionAnother Approach Pin
Member 243431314-Jan-13 6:18
memberMember 243431314-Jan-13 6:18 
QuestionNew to SSIS Pin
Andy Harbert24-Oct-12 6:26
memberAndy Harbert24-Oct-12 6:26 
QuestionError in the project Pin
hhhida27-Sep-12 4:06
memberhhhida27-Sep-12 4:06 
Questiondata is not copying to excel Pin
popxc7z15-Sep-12 0:59
memberpopxc7z15-Sep-12 0:59 
QuestionQueryRegardsSSIS Pin
Member 858933114-Sep-12 2:27
memberMember 858933114-Sep-12 2:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160929.1 | Last Updated 13 Jun 2012
Article Copyright 2012 by TheDhruv
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid