CODEWITHSUNDEEP
sqlsql-serverdynamicssis
Aristo
Aristo

Reputation: 333

SSIS Select column name dynamically based on rows from another table

I would like to select dynamically column based on rows from another table using SSIS

Exemple : I have table A where I have the names of the column I want to query. So I want to query columns in table B that names are stored in table A.

I found a way to do it in SQL query but I don't know how to do so in SSIS.

DECLARE @columnList VARCHAR(MAX),@sql VARCHAR(MAX)

SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName
FROM [dbo].[tableA]


SET @sql = 'SELECT ' + @columnList + ' FROM dbo.TableB'

EXEC(@sql)

Any suggestion?

Thank you

Upvotes: 0

Views: 1871

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

Use an Execute SQL Task to query Table A and populate a string-type package variable with a SQL Query string using the column names stored there. The code you just added to your question will do, but instead of executing the @sql variable, you return it to your Execute SQL Task and populate your variable with it.

Then in your dataflow, use the "Query from Variable" option for your Source component and run the query stored in your string variable.

EDIT: On further inspection of your goal, I have to say it is NOT possible to create a dataflow where the columns can change. The meta-data of the dataflow is created at package creation time, and cannot be changed at run time.

Upvotes: 1

Related Questions