Solved

Foreach Loop Containet problem in SSIS

Posted on 2008-06-23
4
893 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
  • 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now