Solved

Foreach Loop Containet problem in SSIS

Posted on 2008-06-23
4
898 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:crcsupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21849914
Be sure that the ResultSet drop down box has "None" selected
0
 
LVL 1

Author Comment

by:crcsupport
ID: 21849941
yes, it is "None"
0
 
LVL 31

Accepted Solution

by:
James Murrell earned 170 total points
ID: 21849978
other websites cooments

try and make a stored proc with same qury it will provide better error message
0
 
LVL 1

Author Comment

by:crcsupport
ID: 21857992
when I put it in sp it worked without any problems. Any ideas why ? I think I'll just put all operations in sp.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question