Create SSIS package with Dynamic Variables

2
votes

We have a new requirement to create an SSIS package. when package got executed it should copy certain table data into a excel file.

Tablename, excel file path, excel file name is dynamic (it is based on the user input). I am new to SSIS, and I have worked only with static values. How to implement the above scenario in SSIS?

1
sqlsql-serverssis
From where the user inputs are coming (application, SSMS, schedule, etc.)? - Aka Guymelef
Are the Excel file structures, and the Table structures going to be exactly the same every time? - Raj More
@Aka Guymelef , It come from the Application and We thought to pass the inputs as parameter to a stored procedure. - bmsqldev
@Raj More, Table Structure Obviously different and excel strucure is depend on the table stucture. - bmsqldev

1 Answers

1
votes

This is not easy in SSIS, because you need a Data Conversion component between your table source and your Excel destination. That Data Conversion component must have different columns every time you have a query with different output columns.

On the web I found some solutions, but none of them worked for me. So I wrote my own solution, that basically consists of the following steps:

  • Get the contents of an SQL script file (in that file you can put a select query).
  • Create a temporary table from the script code (for determining the structure of the output).
  • Generate and execute a script for creating the Excel worksheet.
  • Dynamically generate a temporary package for executing the SQL script with an Excel file as output.
  • Execute and drop the dynamic package.

Currently I am writing an article on www.codeproject.com to share what I have developed. However, it may take a while until it is finshed.