SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

SSIS – Create New Excel File Dynamically to Export Data

Posted by Prashant on January 12, 2013


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

ExportExcel-Package11
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.

ExportExcel-Package12

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.

12 Responses to “SSIS – Create New Excel File Dynamically to Export Data”

  1. vijay said

    could you please send me the sample file for the same

  2. Graeme Tout said

    I have been using this method with Excel 2003 files with success, but it does not seem to work for Excel 2007. Excel 2007 seems to ignore the attribute specifications in the s create table, and just defaults everything to NVARCHAR255). If there are any fields longer than 255, the package crashes with a truncation error.

    Any suggestions (besides avoiding Excel, and especially Excel 2007)?
    Thanks,

    Graeme

    • Prashant said

      Graeme, Try putting Extended Properties=”Excel 12.0 Xml;HDR=YES;IMEX=1″ in your excel connection manager connection string. IMEX=1 treats intermixed source data columns as text. If you have header row in your data keep HDR=YES or else set HDR=NO.This should solve the issue.

  3. Dharmraj said

    Hello Prashant

    could you please send me the sample file for the same ?

  4. Mike said

    Prashant,
    Thanks for detailing this. I have one problem. After the spreadsheet is created and populated, I want to email (using sp_send_dbmail) the file from within the same package. The call to sp_send_dbmail is hanging. I’m guessing this if because SQL Server does not close the connection to the Excel file after the data Flow task completes. When I try to open the Excel file from Windows Explorer during the hanging of sp_send_dbmail, Windows Explorer hangs too! I tried with RetainSameConnection = False and True, no difference.
    So my question is, how do I close the connection to the Excel file so it is available to email?
    Do I have to write a .NET script to access the connection and manually close it? If yes, any idea how?

    Thanks,

    Mike

    • Prashant said

      Mike,
      When you set RetainSameConnection = False, the connection is automatically closed after the task completed. So I believe there is no need to close the connection explicitly. Have you tried to send the file from management studio using sp_send_dbmail with the file created by SSIS package?

      I have tried to replicate the same on my machine it works perfect. Please refer my sample package from http://sdrv.ms/13NJ41Z and let me know if you stuck somewhere.

      –Prashant

  5. Tim said

    Till last step everything i have set up but in the last step where it says:

    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.
    —–

    Now, here my excel connection manager is already pointing to template excel file created before. But in this last step it is again asking to set ExcelFilePath. This will lead to the error. Right ?

    —-

    This is the error I am getting using above approach:

    [Excel Destination [1570]] Error: Opening a rowset for “Sheet1$” failed. Check that the object exists in the database.

    [DTS.Pipeline] Error: component “Excel Destination” (1570) failed the pre-execute phase and returned error code 0xC02020E8.

    • Prashant said

      Tim,

      Excel connection manager points to template excel just for initial setup. Once the setup is done it uses the sheet name created as used in ‘Create New Excel’ SQL Task in the file that is being used in the expression of excel connection manager. Seems like you stiil have the reference to Sheet1 of template file. Change the expression as mention in post this will solve the error.

      In case of confusion you can refer the sample package mentioned in the post.

      Thanks,
      Prashant

  6. iammil said

    could you please send file package to my email please?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 262 other followers

%d bloggers like this: