SSIS package variables not visable in Execute SQL Task

Question

Post reply

SSIS package variables not visable in Execute SQL Task

Viewing 5 posts - 16 through 19 (of 19 total)

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

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 1

Walter Tucholski (3/9/2006)

I am using SSIS 2005 and have variables defined at the package level. When I try to execute a query in an Execute SQL Task it doesn't recognize my variable. In fact the query won't even parse. Example:

variable name : V_ROW_CNT Int32 value=0

Sql Query: Select * from tablename where column_name > V_ROW_CNT

I also tried Select * from tablename where column_name > @V_ROW_CNT

and still the same problem.

I have hard time believing it is so hard to work with variables in SSIS. Why are they not visible in the other tasks?

Hi,

Your query is not executing as you are wrongly passing variable to ur execute sql task. Correct way is like this:

1) Write your sql something like this:

Select * from tablename where column_name > ?

Question mark (?) here is the placeholder where your variable value will come.

2) Go to your parameter section in Execute SQL Task. Add your variable there.

Now you are ready to go. Just execute your pkg. Everything will work fine.

Let me know in case i am not clear 😎

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

Anjan Wahwar SSCommitted Points: 1617 More actions · Answer 2

MyDoggieJessie

UPDATE _ReadyTables

SET Loaded = 1

WHERE [Table] = ?

AND ETLDate = (

SELECT ETLDate FROM dbo._ReadyTables

WHERE [Table] = ? AND Loaded = 0 )

I created a variable:

Variable Name: User::FL_PRICE

Direction: INPUT

Data Type: VARCHAR

Parameter Name: FileName

Parameter Size: 50

All I'm trying to do is update a simple table, setting the value to 1 for each file name in the ForEach Loop container.

What am I doing wrong???

Hi,

You are wrong in setting Parameter Name property. Parameter Name should be set as 0 for 1st variable (i.e ?) in your query and 1 for 2nd varibale.

Please let me know if its clear..

__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 3

Thanks,

I have changed the Paramter Name to 0 and still receive:

SSIS package "F-Tables.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "UPDATE _ReadyTables

SET Loaded = 1

WHERE [Table] ..." failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Foreach Loop Container 2: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "F-Tables.dtsx" finished: Failure.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 4

Elliott Whitlow

SSC Guru

Points: 102296

April 8, 2011 at 11:52 am

#1309327

Wow, I wonder if the OP is still around, this thread seemed to start in 2006...

CEWII

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 5

The issue was due to the fact I have 2 ?'s in my Execute SQL task, while they represent the exact same parameter, I had only created 1 parameter - so what I needed was:

PARAM #1

Variable Name: User::FL_PRICE

Direction: INPUT

Data Type: VARCHAR

Parameter Name: 0

Parameter Size: 50

PARAM #1 (exact same as #1)

Variable Name: User::FL_PRICE

Direction: INPUT

Data Type: VARCHAR

Parameter Name: 1 <-- Key difference

Parameter Size: 50

In retrospect the easiest thing to have done was to create a stored procedure that had the UPDATE statement in it, and simply passed in the first Parameter (2 hours of my life would have been spared!).

EXECUTE EasyUpdateStatement ?

TGIF...

😎

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience