0

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

Improve this question
4
  • Does you dynamic SELECT result in the same number of columns with each column being of the same data type? Commented Jun 11, 2018 at 13:28
  • If table B is a SQL table I would useinformation schema, as you can get DATA_TYPE which is nice Commented Jun 11, 2018 at 13:32
  • No @SamKolli, the number of columns is variable but they are all the same type Commented Jun 11, 2018 at 14:50
  • If metadata changes at runtime, you cannot reuse the same data flow. If you want to stick with SSIS, you can go with generating a package programmatically with BIML or the SSIS object model. If object model, try EzAPI or my library (example: github.com/samskolli/Pegasus/blob/master/Pegasus.Demo/…; replace code appropriately). Commented Jun 11, 2018 at 22:58

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I tried your solution, but I am getting nothing in my package variable. I am not sure that I am doing it the right way. This is my SQL statement : DECLARE @columnList VARCHAR(MAX) SELECT @columnList = COALESCE(@columnList+',' ,'') + columnName FROM [dbo].[DimSecuredColumns] SET ? = 'SELECT DISTINCT ' + @columnList + ' FROM dbo.Fact' and I use parameter mapping to map ? to my package variable. @Tab Alleman
You could take the script in your question, and just change EXEC(@sql) to SELECT @sql. Specify that your SQL will return a scalar value (single row), and map it to your variable in the result set. No need for parameters.
It doesn't fully work. I had to use an object-type package variable in order to return my query string in it, then I used a Script Task to convert the object-type to string-type, and finally use the string-type variable in my source component. But there is the problem. The source component give an error as there is no value in this variable before the execution of the package. If I assign a default value, It execute, but I get an error as the source output column changed. So I'm asking myself if what I am trying to do(dynamic column selection) is possible with a SSIS. Thank you
Now that you mention it, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.