Recently I got a requirement to export error data to a newly created excel file every time the SQL Server Integration Services 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.

1_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig-1

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

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
[PRIMARY]

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’)

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

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

2_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig-2

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

Refer Fig-3

3_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig-3

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

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

4_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

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]

5_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig-5

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

6_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

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.

7_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig – 7

8_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig – 8

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

“\\\\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”

9_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig – 9

10_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig – 10

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

11_MS_SQL_Server_Integration_Services_2012_Create_New_Excel_File_Dynamically_to_Export_Data

Fig – 11

 

Regards

Bhagwan Singh Jatav

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook