Trying to use a variable as input in SQLStatement within a ForEach Loop

Question

Post reply

Trying to use a variable as input in SQLStatement within a ForEach Loop

kissa49

Old Hand

Points: 339
More actions
June 29, 2010 at 8:08 am

#224340

I am trying to use a variable as an input in an 'Execute SQL Task'.
I pre-set my object outside the ForEach container. In the foreach container I set up all the Variable Mappings. I know all that is done correctly because when I use a Script Task to output these parameters to the screan it works.
But when I set up my 'Execute SQL Task' with one of these variables in the Variable Mappings as 'Input', with Parameter Name set to 0, and then have this simple script in the SQLStatement:
SELECT NAME FROM SCHDTASK
WHERE ID = ? --@Task_ID
I get an error when I run this.
"[Execute SQL Task] Error: Executing the query "SELECT NAME FROM SCHDTASK WHERE ID = ?" 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 correctly.
If anyone has any idea what I am doing wrong here, I would really appreciate any kind of input.
Thank you!

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

I don't use SSIS so I have no clue but let my post serve as a "bump" to someone who may. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

What are the datatypes of the variables?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

kissa49 Old Hand Points: 339 More actions · Answer 3

The variable is an integer.

Looks like I figured it out though:

For 'Parameter Size' I put in 1 before. Now changed it to 3 and it works. No clue what exactly did it.

Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

According to BOL, the parameter size is used for the following:

Parameter Size
Provide the size of parameters that have variable length, such as strings and binary fields.
This setting ensures that the provider allocates sufficient space for variable-length parameter values.

Usually I set this to -1.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP