crcsupport
asked on
Foreach Loop Containet problem in SSIS
I am trying t o use Foreach Loop container (Foreach Item Enumerator) to do some tasks with each table on the list. I entered the list of all table names I want to work with. I created the variable to hold the table name. I add execute sql task in the container. I map the paramenter to previously created variable. I want to use this parameter in SQL statement but I am always getting an error:
-"drop table @tblName" 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.
-[Execute SQL Task] Error: Executing the query ""drop table '"+ @[User::TblName]+''''" failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is just 2 examples of what type of error I am getting. Can you please let me know how to set up and use parameters the right way ?
Thank you
-"drop table @tblName" 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.
-[Execute SQL Task] Error: Executing the query ""drop table '"+ @[User::TblName]+''''" failed with the following error: "Incorrect syntax near '+'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This is just 2 examples of what type of error I am getting. Can you please let me know how to set up and use parameters the right way ?
Thank you
Be sure that the ResultSet drop down box has "None" selected
ASKER
yes, it is "None"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when I put it in sp it worked without any problems. Any ideas why ? I think I'll just put all operations in sp.