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?
