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

Question

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.

Answer 1

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

---

2. 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:

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#