SSIS Package to Dynamically Create an Excel File with DateTime from SQL Server Table

How to Dynamically Create an Excel File with Date Time from SQL Server Table using SSIS Package on each Execution
Senario :
Suppose I have the Employee source Data in the SQL Server Table which will update periodically.
The data in the Source Table "[Emp_Details]" as follows :

Whenever I run the SSIS Package, I wants to Export/Save this data from Source Table to Excel and Save the Output as "EmpDtlsByDept_&Date Time suffix as follows :

Please note that, Each time when we run the Package the a New output file should create with latest data, and Save with Current Date Time Stamp Suffix.

Now Lets create an SSIS Package to accomplish this Scenario as follows...

-----------------------------------------------------

Phase-1 : Design the Data Flow :

-----------------------------------------------------

Add a DataFlow task to Control Flow and then Design the Flow 

A) Add a OLEDB Source Inside the Data Flow and Connect to Source Table
This OLEDB source will be connected to the Source Table "[Emp_Details]" 

B) Connect to the Type Conversion Transformation and Convert the Source Columns as per the Excel Destination Format (nvarchar)

Most of the Cases we will have the issue with the String Datatypes which we have to Convert as follows..

C) Connect to the Excel Destination and Set the Properties to Dynamically Create a runtime Excel Destination Template as follows :

Go to Excel Destination Editor...

Next Click on the "New" in the Excel Destination Editor>Connection Manager , to Create a New Excel File..

Next Select the Excel Version ( I chosen Excel 2007 or Higher ), and Click on the "Browse"

to Select the Folder where we want to Create a Dynamic Excel Template...

Next give the Template Name with Extension ( I given as .xlsx as I have chosen 2007 or higher version) and then click Open..

Next Click OK.

Next Click on "New" to Create an Excel Template with Sheet Name dynamically..

When You click on New it will popup an Warning Message box, Just say OK to it.

Next it will Popup a Create Table window with Create Table Statement with all the available Columns(Source Columns and Converted Columns) and with a Default table name as "Excel Destination"

I have selected only the Source Column names which I want to show in destination Excel and also I renamed the Table name as "Emp_Data" , by which a Tab in destination Excel Template.

CREATE TABLE `Emp_Data` (
    `Emp_Id` INT,
    `Emp_Name` VARCHAR(255),
    `Gender` VARCHAR(10),
    `Job_Id` INT,
    `Dept_Id` INT,
    `Joining_Date` DATE,
    `Basic_Sal` DOUBLE PRECISION,
    `Dept_Name` VARCHAR(255)
)

After You modified the Query, say OK. Now it will create an Excel sheet as "Emp_Data$" which we need select as follows..

Next we need to Map the Source Columns(the Converted Columns) with Excel from Mapping Tab as follows...

Now the Excel Template is created in the Template location as "EmpDtlsByDept_Template.xlsx" with tab name as "Emp_Data" with required column name as shown below...

Now the Excel Connection is Ready, I renamed it as shown below

-----------------------------------------------------

Phase-2 : Create the Required Variables to Create Dynamic Output Files:

-----------------------------------------------------

Here I have Created 3 Static Variables and 2 Dynamic Variables as follows...

OutputFileName : This is Static variable in which we store the OutPut File Naming convention as "EmpDtlsByDept"

OutputFolderPath : This is Static variable in which we store the OutPut Folder Name "T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\" where we save the Ouput Files  

TemplateFilePath : This is Static variable in which we store the the Template Full Path Name "T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Template\

EmpDtlsByDept_Template.xlsx".

DateTime :

This is a Dynamic Variable used to Store the Date Time value (Eg : "20171117_011150" ) Calculated from GetDate() funcation using below Expression..

REPLACE(REPLACE( REPLACE(SUBSTRING((DT_WSTR,50)GETDATE(),1,19),"-",""),":","")," ","_")

OutputFileFullPath :

This is an Important Dynamic Variable used in the Package to create and Output File dynamically with Date Time suffix to the Template File name done by File System Task.

This file will passed dynamically to the Excel Destination connection in the Data Flow, to save the output.

This variable value is Calculated using the below Expression..

@[User::OutputFolderPath] + @[User::OutputFileName] +"_"+ @[User::DateTime]+".xlsx"

Eg : T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Desti\Output\

EmpDtlsByDept_20171117_012202.xlsx

-----------------------------------------------------

Phase-3 : Set the ExcelFilePath as Dynamic  :
-----------------------------------------------------

Make the EmpDtls_Destination connection to the Excel File is dynamic, by assigning the ExcelFilePath to a Variable from Properties>Expressions as shown below...

ExcelFilePath = @[User::OutputFileFullPath]

Since We make the Destination Excel Connection as a Dynamic, we need to make the DelayValidation=True for EmpDtls_Destination as well as for DataFlow Task as follows...

Next Set the DelayValidation=True from the Properties window of the EmpDtls_Destination

Next set the DelayValidation=True for DataFlow Task by selecting the DataFlow Task then go to the Properties window as follows...

-----------------------------------------------------
Phase-4 : Connect the File System Task to DataFlow and Set properties to Pass the Template with Date Time suffix to the Excel Destination Connection:
-----------------------------------------------------

A) Add a File System Task and Connect to the Data Flow

B)Set the Source Template and Destination File Name variables to File System Task 

-----------------------------------------------------
Phase-5 : Finally Save the whole Package then Close and Re-Open then Run the Package
-----------------------------------------------------

-----------------------------------------------------
Final Output :
Now the final Output Files are generated with Date Time suffix in each run.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

-------------------------------------------------------------------------------------------------------