How to execute SQL Statement to make it as source and Load into Destination table in SSIS

Question

I have a table dbo.Test whose columns (ID,Query_Column)

ID Query_Column

1 SELECT CustomerKey, FirstName, LastName, MaritalStatus, Gender FROM CUSTOMER_DETAILS

In Query column I have a select statement which have some data.

Now In my SSIS Package I want to make it as Source columns and Load into Destination Table dynamically.

Source

CustomerKey
FirstName
LastName
MaritalStatus
Gender

Destination

CustomerKey
FirstName
LastName
MaritalStatus
Gender

Now In my SSIS Package I want to make it as Source columns and Load into Destination Table dynamically.

Can anyone suggest me on this .

Answer 1

SSIS is based on static metadata. So it will not work with dynamic columns.
You need to look at 3rd party products: CozyRoc

Another alternative would be to use SSIS Execute SQL Task from a dynamically composed SQL statement. Along the following:

INSERT INTO ...
SELECT ... FROM ...

Answer 2

SSIS must have column and table names at DESIGN time, not runtime. SSIS is not capable of dynamic transfer of data at runtime.

Answer 3

Hi @varaprasadmohan pinagadi ，

When creating the connection manager, select the date access mode as sql command. Insert the data set returned by your select statement into a temporary table and copy it to the command box.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.