sqlexcelssis

Dynamically Create Excel Sheets using SSIS

I have a request to create an excel report with sheet containing data for a specific employee (1 sheet per employee). I would like to accomplish this by dynamically using variables or parameters. I've used "Execute SQL Task" with both.

Package

Variable

Execute SQL Task with variable

Direct Input with Parameters

Parameter Mapping

Execute SQL Task with Parameters

However, when ran, I get the following error:

[Execute SQL Task] Error: Executing the query "CREATE TABLE 'Employee' ( Date Created DA..." failed with the following error: "Syntax error in CREATE TABLE statement.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Your help would be greatly appreciated!

Solution

  • I figured it out... It seems like there may have been a refresh issue or something of the sort. Nevertheless, here's where I ended up:

    1. Copy the Template: the template needs at least one existing sheet for this to work. I copied the file from a template folder to a "Pending" folder.

    2. Insert the List of Employees into a variable: take the employee records and insert them into a variable using "Recordset Destination"

    3. Create Excel Sheet for each Employee: using a "Foreach Loop Container", put a "Execute SQL Task" and Data Flow Task inside then set the Variable Mapping to an empty variable (I used "Employees"). See below for more details

    Execute SQL Task

    The configuration for the Execute SQL Task

    Variable: Sheet Name

    The variable referenced in the Execute SQL Task. Please note, the Employee variable does not need REPLACE, LEFT and FINDSTRING functions. The data I'm referencing needed to transformed.

    Data Flow Task: Excel Destination

    The Excel Destination in the Data Task Flow

    Variable: Sheet Name Only

    The variable referenced in the Excel Destination. As previously mentioned, the functions were needed in my case but are not required. The "$" is the only required piece aside from the variable.

    ForEach Loop Container: Collection

    The collection settings for the ForEach Loop Container

    ForEach Loop Container: Variable Mapping

    The variable mapping settings in the ForEach Loop Container