Using a for Each loop with an object to create a parameter to loop on

Question

Using a for Each loop with an object to create a parameter to loop on

Debbie Edwards

SSCrazy Eights

Points: 9217
More actions
December 13, 2011 at 6:19 am

#253460

I have asked about this before and failed to understand but unfortunately asked my DBA team to create a job adding the package in multiple times using different paramters and they cant do it because they dont know how to?
So here I am again trying to figure out how to do this within the package.
I have an object with lots of years in it Created in an Execute SQL task
AC 06-07
AC 07-08
AC 08-09
The results set is set to User CreateVariable which is an object
I then have a for each loop. the collection uses the source variable of User CreateVariable. the Enumerator is Foreach ADO Enumerator
within the foreach loop I have an Execute SQL task than should execute a sotred procedure that adds the AC_Year of the parameter to a simple table.
The connection type is Adonet and the SQL Statement is EXEC dbo.usp_Test_Table_For_Parameters ?
Here is the error message I get back.
Execute SQL Task Error Executing the query EXEC dbo.usp_Test_Table_For_Parameters ? failed with the following error: Incorrect syntax near ?
I have added my test package.
Ive had some brilliant help before on this but I couldnt get it sorted. I thought Id start again with a much simplified version. If I can get my sest to work I should be on my way to getting the real thing working.
Any help would be appreciated
Debbie

Viewing 15 posts - 1 through 14 (of 14 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

See here. ADO.Net doesn't use the ? as a parameter marker. In any case, why are you doing this with a ForEach Loop? Sounds like the kind of update you can do in a single Execute SQL task.

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 2

This is just a basic test for something that does need a for each loop.

I set it to ADO rather than ado.net but am not getting this as an error

Executing the query EXEC dbo.usp_Test_Table_For_Parameters ?" failed with the following error No value given for one or more required parameters

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Check that your ForEach Loop is configured to write the value(s) from each row into variable(s), and that the variable(s) is mapped to the parameter(s) that you are passing to your procedure in the Execute SQL task. Also check whether the parameter enumeration for the connection type (ADO) that you are using is 0-based or 1-based.

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 4

Check that your ForEach Loop is configured to write the value(s) from each row into variable(s)

How do I check this?

…and that the variable(s) is mapped to the parameter(s) that you are passing to your procedure in the Execute SQL task. Again how do I check this? I think it is

Also check whether the parameter enumeration for the connection type (ADO) that you are using is 0-based or 1-based. I’m not sure what you mean here? The variable mapping index is set to 0 in the for each loop if that’s what you mean?

Ill need some more information on these 3 things, Im only a beginner at this….. sorry

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Read the link I posted earlier carefully. I think it says that for ADO, your parameters should appear as param1, param2, ... in both the parameter list and the SQL statement. Search the web for examples and tutorials on this sort of thing if you're new to it - you'll be amazed how much you find.

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 6

Thanks for your advice,

I dont want you to think I came straight on here to ask for help. I have done weeks of seaching on the internet and havent had much luck

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 7

Oh no....

I added Param1 to the SQL Statement and then checked the data written to the test table and this is what I have found.......

AC 06-07

Param1

Its taken the first parameter and has added param1 for every other data item. Bizarre. I have no idea what has happened.

Ive anyone can help....:blink:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

Bizarre. Please will you post your SQL statement?

Thanks

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 9

Brilliantly this keeps crashing when I try and add certain symbols. Ive readded the project as it stands because trying to add SQL Script into this hasnt gone well

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 10

SQL Statement EXEC dbo.usp_Test_Table_For_Parameters param1

param1

to the table.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 11

OK, two things. First, trust but verify. In other words, don't believe what people like me tell you without checking it out for yourself. Read that link again, and you'll see that I was wrong about the parameter markers (although in my defence I did qualify it with "I think"). You need ? instead of Param1 in your SQL statememnt. And second, you need to fill in the Parameter Mapping page of your Execute SQL Task Editor. This is where you put Param1, etc.

Try both those things, and it should work.

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 12

In fairness though the Microsoft guide is extremely none user friendly. I cant make heads nor tails of it. Also there are no great guides out there on how to do this. I have looked for them. The guide doesnt tell you what to do on the parameter mapping screen.

I have attempted to use http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/2999/

to help. Ive change to ? In the SQL bit Then in paramter mapping I have

Variable Name User AC_YR

Direction Input

DataType adVarChar

parameter 0

Parameter Size -1

but still failing

EXEC dbo.usp_Test_Table_For_Parameters ? failed with the following error

Value does not fall within the expected range

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Parameter name is Param1 for the first parameter, Param2 for the second, and so on.

John

Debbie Edwards SSCrazy Eights Points: 9217 More actions · Answer 14

Its WORKED!!!!!! :hehe::w00t::-D

Im going to do a few checks and then transfer to the real thing.

I canot thank you enough. Im going to update my help documentation with screenshots etc.

brilliant

Debbie