MS SQL Server Integration Services 2012 – 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.

• Before start working on this requirement please verify below Environmental Issues on your machine

• adhoc distributed queries should be enabled. 

• CLR integration should be enabled.  

• ACE driver should be installed and registered. Please refer fig-1 for ACE driver verification. 

• Create sample table and data to write into excel file. We have used below scripts to create table and sample data.

[PRIMARY]

• 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 have used own database and their table ([dbo].[Employee]))

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

• Now add a Data Flow Task and setup 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

EmpName,

EmployeeDepartment,

EmployeeDesignation

FROM [dbo].[Employee]

Fig-5

• Add data conversion for converting the all string fields from string to Unicode string. Please refer Fig-6

• 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 - 7

Fig - 8

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

• Now run package and verify the exported excel file:, here we have ran package three times and three files generated based on time stamp.

Fig - 11

• 
  Fig-1

CREATE TABLE [dbo].[Employee]

(            [EmpId] INT NOT NULL IDENTITY(1,1), 

[EmpName] [varchar](50) NOT NULL,

[EmployeeDepartment] [varchar](50) NOT NULL,

[EmployeeDesignation] [varchar](50) NOT NULL

) ON

INSERT Employee(EmpName,EmployeeDepartment,EmployeeDesignation) VALUES('Ajay Juneja','Admin','Associate - Admin')

INSERT Employee(EmpName,EmployeeDepartment,EmployeeDesignation) VALUES('Anoop Singh','Finance','Associate - Finance')

INSERT Employee(EmpName,EmployeeDepartment,EmployeeDesignation) VALUES('Prakash Singh','Finance','Sr. Associate - Finance')

INSERT Employee(EmpName,EmployeeDepartment,EmployeeDesignation) VALUES('Raman Kaushik','Engineering','Sr. Lead')

INSERT Employee(EmpName,EmployeeDepartment,EmployeeDesignation) VALUES('Nitin Kumar','Engineering','Consultant')

           Below sample template created for set up the package, now using sheet name as “Sample”

           

          Fig-2

            Refer Fig-3

            

            Fig-3

           CREATE TABLE Sample (EmpName LongText, EmployeeDepartment LongText, EmployeeDesignation LongText)

           

        Fig-4

           

          Fig - 6

             "\\\\Del1-lhp-24461\\ssis2012\\SampleData_"+RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) +

             RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2) + (DT_STR,4,1252)DATEPART("YYYY" ,GETDATE())  + "_" 

            + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" + Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2)  +""+ ".xlsx"

             

            Fig - 9

            

            Fig - 10