none
Help creating an excel file

    Question

  • I am trying to create an excel file from a table in sql server.

     I want to basically copy the template file I created into a new folder, then input data to this new file.

    However, I am having trouble because the package wont run because the excel connection manager is pointing to the file that has yet to be created.

    Monday, August 11, 2014 2:31 PM

Answers

  • Hi lrj1985,

    If I understand correctly, you would like to create Excel Connection Manager dynamically. In other word, you want to new an Excel file every time the package run. Am I right?

    If so, we can use expression in ExcelFilePath to name the file dynamically. We can create the destination sheet every time before the data flow task is running. Here are the detailed steps:

    1. Creae a dummy Excel in a specified folder
    2. New a Excel Connection Manager, and point to the file we created in step1. Yes, it is pre-created. But it won't be used while the package is running
    3. New a variable, use expression in it to dynamically set its value. This value is going to be used as the ExcelFilePath in runtime. The expression can be something like this: "D:\\Libraries\\Desktop\\Excel\\"  + SUBSTRING( (DT_WSTR, 30) GETDATE(), 1, 10)+   "_destination.xls"
    4. Right click the Excel Connection Manager we created in step2, click Property
    5. In the properties window, click Expressions
    6. In the Expression window, set Property as ExcelFilePath, and the value as variable we created in step 3
    7. Now, go to Control flow, add a Execute SQL Task before Data Flow task.
    8. In the Excel SQL Task, set connection type as Excel, connection as the connection we created in step1, and the SQL Statement as something like the following one
    CREATE TABLE `Excel Destination` (
        `Id` Double,
        `Value` LongText
    )

      9. Go ahead, and add the Data flow task

    After that, each time we run the package, a new Excel file will be created. We can then use File Sytem Task to move it to a specified folder.

    Thanks,
    Jinchun Chen

    • Marked as answer by lrj1985 Tuesday, August 12, 2014 7:07 AM
    Tuesday, August 12, 2014 5:59 AM
    Moderator

All replies

  • You need to define your Excel file beforehand.

    I suggest alternatively, you do that via the Database Export Wizard from scratch, saving the result as a package and then open.


    Arthur My Blog

    Monday, August 11, 2014 3:27 PM
    Moderator
  • Hi lrj1985,

    If I understand correctly, you would like to create Excel Connection Manager dynamically. In other word, you want to new an Excel file every time the package run. Am I right?

    If so, we can use expression in ExcelFilePath to name the file dynamically. We can create the destination sheet every time before the data flow task is running. Here are the detailed steps:

    1. Creae a dummy Excel in a specified folder
    2. New a Excel Connection Manager, and point to the file we created in step1. Yes, it is pre-created. But it won't be used while the package is running
    3. New a variable, use expression in it to dynamically set its value. This value is going to be used as the ExcelFilePath in runtime. The expression can be something like this: "D:\\Libraries\\Desktop\\Excel\\"  + SUBSTRING( (DT_WSTR, 30) GETDATE(), 1, 10)+   "_destination.xls"
    4. Right click the Excel Connection Manager we created in step2, click Property
    5. In the properties window, click Expressions
    6. In the Expression window, set Property as ExcelFilePath, and the value as variable we created in step 3
    7. Now, go to Control flow, add a Execute SQL Task before Data Flow task.
    8. In the Excel SQL Task, set connection type as Excel, connection as the connection we created in step1, and the SQL Statement as something like the following one
    CREATE TABLE `Excel Destination` (
        `Id` Double,
        `Value` LongText
    )

      9. Go ahead, and add the Data flow task

    After that, each time we run the package, a new Excel file will be created. We can then use File Sytem Task to move it to a specified folder.

    Thanks,
    Jinchun Chen

    • Marked as answer by lrj1985 Tuesday, August 12, 2014 7:07 AM
    Tuesday, August 12, 2014 5:59 AM
    Moderator
  • Hi lrj1985,

    I forgot to mention that we need to set the DelayValidation as True.

    Thanks,
    Jinchun Chen

    Tuesday, August 12, 2014 7:59 AM
    Moderator