SSIS : Using SQLStatement output records as Full Result Set to feed into for each loop container

Question

I am trying to use the output of a SQLStatement with Full Result Set Resultset from a SQL Task as an object to feed into foreach loop container.

The SQL statement is

SELECT * FROM INFORMATION_SCHEMA.TABLES
where TABLE_NAME like 'Dim%'

I want to pass the output of the query from source OLEDB SQL connection as objects to feed into the container to transfer tables to other OLEDB SQL connection.

My Parameter Settings are as follows:

• Variable Name = User::Tables_to_be_transferred
• Direction = Input
• Data Type = VARCHAR
• Parameter Name = NewParameter
• Parameter Size = -1

Result Set: - Result Name = 0 - Variable Name = User::Tables_to_be_transferred

But when I run the code I get the following error message :

    Error: 0xC002F210 at Select tables, Execute SQL Task: Executing the query "SELECT * FROM INFORMATION_SCHEMA.TABLES where TABL..." failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

How do I fix this? Any help would be appreciated.

Answer 1

You don't need the input parameter because your query doesn't have any parameters, so you're probably causing a problem by adding one.

Make sure User::Tables_to_be_transferred is an object-type variable, and only use it for the ResultSet. Do not use it as an input parameter.

Answer 2

You won't need to use parameters to return the result set. Since you already have ResultSet set to full, on the Result Set pane add an object-type variable in the Variable Name field and use 0 for the Result Name to assign the results of the Execute SQL Task to the this variable.