0

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.

PrinceKayastha
  • 115
  • 6
  • So, what is your question here. You've set out your goal, but haven't asked anything. What are you having trouble with? – Thom A Nov 29 '18 at 18:50
  • 1
    Review this [answer](https://stackoverflow.com/questions/13961534/how-do-i-pass-value-to-a-stored-procedure-parameter-in-ole-db-source-component/13976990#13976990) and let me know that it doesn't cover – billinkc Nov 29 '18 at 18:56
  • @Larnu, thanks for pointing that out. The problem is now added in the question. – PrinceKayastha Nov 30 '18 at 04:50
  • @billinkc, thanks for the link. This was of great help. Ill let you know if anything comes up. – PrinceKayastha Nov 30 '18 at 04:50

2 Answers2

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.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
0

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.

userfl89
  • 4,610
  • 1
  • 9
  • 17