Export data from database to excel sheet
with 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 this 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 :)