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

   

7 Comments on “SQL Server Integration Services 2012 – Create New Excel File Dynamically to Export Data”

  1. Very nice stuff, Thanks for sharing.

    Can you pls, also tell, how to create once and modfiy further the same file?

    Thanks,

    Alok

  2. Hi Alok,

    You need template file to design SSIS package first time. Once the package setup is done you can delete the template.

    If you again want to modify the SSIS package output format then again you need modified template for making changes in existing SSIS package.

    Please let me know if you need more info.

    Regards

    Bhagwan

  3. Hi,

    Please follow the all steps specified in the blog, you need to create excel template first for designing this package, name can be anything now I am keeping “sample” and appending timestamp runtime.

    Regards

    Bhagwan

  4. Hi Bhagwan,

    I am facing a problem when the ‘excel file path’ is changed to an expression. The destination connection become void and its throws error. The expression is pointing to a new excel file which is not yet there rite?

    Any thoughts on this ? I am using SSIS 2012.
    Appreciate your help to resolve this.

    thanks,
    Hemesh

  5. Many Thanks for your help. I really appreciate it. After hours of search finally this code worked for me.
    By chance could you please also show if instead of creating an excel table do it through variable?

    Thanks

Leave a Reply

Your email address will not be published.