SSIS - Create New Excel File Dynamically to Export Data

Recently I got a requirement to export error data to a newly created excel file every time the SSIS package runs. The challenge here was to create new excel file with time stamp every time the package ran and export data to that newly created file.

In this post will demonstrate this step by step.

• Create a template file to export the data. You need this template file to set up the package for the first time. Once the setup is done you can delete the template.

• Create connection managers:
  • Add Excel Connection Manager pointing to the template excel file created before.
  • Add OLEDB Connection Manager pointing to the desired database (here I am using AdventureWorks2008R2 database)

• Add an Execute SQL Task and use the below script as SQL Statement as shown in fig-2:

CREATE TABLE Data (`DepartmentID` LongText, `Name` LongText, `GroupName` LongText)

Fig-1

Fig-2

• Now setup a Data Flow Task to export the data.
  • Add a Data Flow Task to the package. Open the Data Flow Task, inside Data Flow tab add a OLEDB Source, select SQL Command as data access mode and use your query to fetch the required data. I will use the below query:

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department

• Add an Excel Destination to the package. In the excel destination editor select Table or View as Data Access Mode, select the name of excel sheet and do the required mapping. Set ValidateExternalMetadata property to False.

Fig-3

• Now go to Expressions property of Excel Connection Manager and set ExcelFilePath with below expression:

"S:\\ETL Lab\\CreateNewExcel\\ExportData_"+REPLACE((DT_STR, 20, 1252)(DT_DBTIMESTAMP)@[System::StartTime], ":", "")+".xls"

and the package is ready to run.

You can download the sample package here.