sql server - SSIS convert object variables to string in expression builder

Question

I have a few queries in my SSIS package where I need to loop through a bunch of table names pulled from a query. In he foreach container it puts the table names into a variable, and this works fine.

In the foreach container I then have an 'Execute SQL Task' which executes a variable which contains the SQL code to extract all the column names into a single string:

"SELECT CAST((SELECT 
'[' + STRING_AGG(CAST(c.[COLUMN_NAME] AS nvarchar(MAX)) , '], [') + ']' AS ColumnName
FROM INFORMATION_SCHEMA.TABLES t 
LEFT JOIN  INFORMATION_SCHEMA.COLUMNS c  ON t.TABLE_NAME = c.TABLE_NAME AND c.TABLE_SCHEMA = t.TABLE_SCHEMA 
WHERE t.TABLE_SCHEMA = 'hist' AND t.TABLE_NAME = '"
+
@[User::TableName] 
+
"' GROUP BY t.TABLE_NAME)
 AS nvarchar(MAX)) AS ColumnNames"

This string I would like to use in an expression in another variable where the column names are needed in a query. But I cannot seem to store them in a variable which is a string, as it is an object. Whenever I try to store it in a variable which is a string, then it fails saying that that it cannot store an object in a string variable.

I have tried casting it as a nvarchar(MAX) as has been suggested and accepted as a solution in this post: SSIS convert object variable to string in expression builder

I have also tried the Script Task option, but as I am not experienced in this, I cannot seem to make this work.

Right now the Execute SQL Task returns a ResultSet as a Single Row, and in the Parameter Mapping I've selected the string variable as input, and in the Result Set I have it to return the object into an object variable for the purpose. This does not fail, but when I try to execute the next query where the column names are needed, it fails because the variable is empty, meaning that nothing is put into the string variable where I need the column names.

Is there any way I can put the string value into the string variable so I can use it for other expressions?

question from:https://stackoverflow.com/questions/66061590/ssis-convert-object-variables-to-string-in-expression-builder

Answer 1

Waitting for answers