using parameterized procedure in execute SQL task in foreach loop

Question

Post reply

using parameterized procedure in execute SQL task in foreach loop

computer.mike

Right there with Babe

Points: 719
More actions
August 5, 2008 at 10:28 am

#126903

I have foreach loop that loops through a ADO enumerator (another table ) and reads a value in a column. In the foreach loop I have script task that displays a msgbox with the column value that works fine
MsgBox(Dts.Variables("User::Ref_Code").Value).......................works fine, so i know my variable is getting set within the loop.
below the script task, in the loop, i added an exec SQL task.
I want to pass "User::Ref_Code" as a parameter to a stored procedure in the loop.
Here is my SQL syntax in the task.... exec crimes.loadStatute ?............this work fine in query analyzer.
In SQL task i have BypassPrepare set to true, connection type = OLE DB.
Parameter mapping for sql task = user::Ref_Code, Input, Nvarchar, 0
here is error
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec crimes.loadStatute ?" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correct
I can't figurre out what is wrong, any help would be appreciated before I go crazy.
I have lloked at other posting and searched the web, but no luck so far.
Thanks,
Mike

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Check out this blog post:

http://zulfiqar.typepad.com/zulfiqars_web/2006/11/ssis_passing_pa.html

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Mukti SSCrazy Points: 2826 More actions · Answer 2

I am suspecting that you are getting some NULL values in your ADO resultset. Make sure that the ADO resultset (in foreach loop) does not get any NULL values. If the variable gets any null value it will fail in the Execute sql task as it will not be able to map it to NVarchar datatype, even though the script task will work because the . So in your query that feeds the foreach loop make sure you handle NULLs properly something like this.

Select COALESCE( , 'the value was null') as returnvalue

from

And you can handle this Null case in your loop.

HTH

~Mukti

computer.mike Right there with Babe Points: 719 More actions · Answer 3

I took out parameter and still can't get procedure to execute.

It works in the query builder pane of the SQL task, but not in package. I get error says can't find procedure.

the isStoredProcedure changes to false sometimes, by itself. Exec SQL task works outside of loop, but not in.

what could i be doing wrong????

Mukti SSCrazy Points: 2826 More actions · Answer 4

In your first post you mentioned that your are trying to run exec crimes.loadStatute.

Please make sure that in the loop you are using the Schema name properly and not using exec loadStatute.

Seems your procedure is in a different schema than dbo.

computer.mike Right there with Babe Points: 719 More actions · Answer 5

yes, i use the proper schema name. I took out the Crimes for the forum only.

Mukti SSCrazy Points: 2826 More actions · Answer 6

That sounds really very wierd. Could you please tell more about the loop, what else goes on there?

Is it possible that when you run the packages the connection changes dynamically (using config files or some variable) - trying to think what are the possibilities.

~Mukti

computer.mike Right there with Babe Points: 719 More actions · Answer 7

i have a script task in the loop and pass a column as a variable into the loop. it is reading each variable correctly. if i disable the exec SQL task, i will get mesgbox after messagebox with a different value, so i know the loop is working and it iterating through dataset. it is giving me the proper values.

script properties

ByPassPrepare = true

delayValidation = true

isStoreProcedure = true

ResultsetType = ResultsetType_None

SQLStatementSource = exec Crimes.LoadStatute

SQLStatementSourceType = DirectInput

in the Execute SQL task editor sometimes when i hit the build Query button i will get "The Exec SQL construct or statement is not supported", but i hit ok, the query builder window opens and i see EXEC CRIMES.loadstatute. i hit run and i get message that the query exectued properly. I open table in SSMS and see new data.

If i right click on task ( in the loop container) and select "Execute task" it works fine and i see new data in the database

i do not have a resultSet or parameters, but procedure requires no parameters.

i have set nocount on in procedure

if i copy and paste outside the loop it works.

it must be a property of the loop??

computer.mike Right there with Babe Points: 719 More actions · Answer 8

it ran once, then i tried again and it fails. I didn't change anything. I just tried to run it again. I thought it may be a connection issue so i restarted computer, but that did not help.

This has to be a bug is ssis. I read a few post on how QA of SSIS was bad and it is a less than perfect product.

Maybe I should start learning Oracle

computer.mike Right there with Babe Points: 719 More actions · Answer 9

rebuilt package from scratch and it works. File must have been corrupt or something.