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

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