How to import Data from SQL Server Table to EXCEL file (With DateTime) Using SSIS

Mukesh March 19, 2023

Step 1:- Create a Data Flow Task and add an OLE DB Connection. For Simplicity, we are only taking only 2 Columns and 10 Rows Through SQL Command.

Step 2:- Create an Excel Connection, We will be creating a . XLS Template File for now, the reason we create a . XLS file is that the Excel file with the $Sheet should exist in advance in order to work on it, SSIS will not create a . XLS file for us Dynamically in this case.

It’s Better to create a new Folder TEMPLATE to create the. XLS Template later we will Copy this template in Output Folder with the date and time appended to it and then insert data using SSIS.

Step 3:- Create a data Conversion to convert Non-Unicode to Unicode and map the columns accordingly.

Step 4:- At this point, a Template file is created for us to work on, Now we will be creating 3 Variables to store the Template Path (Source), Output Path (Target), and Output File Path (Target).

Step 5:- Below we have created three variables

1.) Template_Folder:- To store the path of Template Stored.

2.) Destination_Folder:- To store the path of the Destination Folder.

3.) Destination_Filename :- Expression to create filename

@[User::Destination_Folder]+ “ASA_” +replace(substring( (DT_WSTR, 30) GETDATE(),1,19),”:”,””) +”.xls”

Step 6:- At this point Create a File System task and Assign the Source and Destination variables So that a Copy of the Excel Template Will be copied from the Source path to Destination Path so that the file already exists beforehand.

Step 7:- Right Click on Excel Connection Manager and Click on Expression, in Property Expression Editor under Property select ExcelFilePath and select the destination variable that we created.