CODEWITHSUNDEEP
sql-serversql-server-2008ssissql-server-data-toolsssdt-bi
Srikar mogaliraju
Srikar mogaliraju

Reputation: 215

Using Object Datatype Variable as a table name to Query in SSIS

I have a scenario to create and populate data in Excel sheet dynamically. Data is available in Sql Server. I am capturing the Sql table data into Object Variable and then In the Execute Sql Task with Excel Connection how can i use this Object Variable as a table to do a select * into SheetName from [User::Object] ?

Note: The column names are not constant.They change for every run.

Upvotes: 1

Views: 1949

Answers (1)

Alexander Volok
Alexander Volok

Reputation: 5940

There are a few ways to get this task done:

  1. Via ADO Enumerator:

    By using the foreach loop task and choosing ADO Enumerator.

    An example: Implementing Foreach Looping Logic in SSIS

  1. The data source as an ADO recordset:

    This is perhaps a harder way because the data source to be created programmatically using C# by parsing incoming object variable. Then, it can be used to load data into excel destination by leveraging a mere dataflow:

enter image description here

An example: Using The SSIS Object Variable As A Data Flow Source

Update: Because of new requirement - column names are dynamically assigned:

The Excel file also to be programmatically created using C#:

  1. How to create Excel file in C#
  2. Create Excel Files in C#

Upvotes: 1

Related Questions