Ole db source issue in Data flow

Question

Post reply

Ole db source issue in Data flow

Chris-475469

SSCrazy

Points: 2986
More actions
April 18, 2019 at 5:33 am

#3442820

Hi,
I'm experiencing a frustrating issue when trying to call a proc in an OLE DB source task. I'm using the SQL command from variable data access mode but I can see that it isn't evaluating my variable correctly.
My variable (with ValidateAsExpression set to True) uses an expression to create a sql command like "EXEC ProcName 'Param'" where the value of Param comes from a variable who's value I set using an EXEC SQL task. Below is the expression:
"EXEC ProcName " + "'" + @[User::vDateThreshold] + "'"
If I use a variable in my source that references a static value it works fine, but the issue seems to be when I use a variable which reference another variable in its expression.
Has anyone else come across this issue?
I'm using this method because I've had a similar issue when trying to use a parameter with the sql command data access method.
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246955 More actions · Answer 1

What is the data type of @[User::vDateThreshold]?

When you say: "I can see that it isn’t evaluating my variable correctly", what are you seeing, exactly\?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chris-475469 SSCrazy Points: 2986 More actions · Answer 2

That variable uses the string data type.

I know the variable is being set as expected by the EXEC SQL task but my other string variable called vSQLString uses an expression that references vDateThreshold, and when I set my ole db source to use the vSQLString variable zero rows are passed in the data flow.

If I use a stand alone variable that has its value explicitly set to my SQL query and use this in the data flow it works as expected. It appears as though it doesn't like a variable that uses an expression to set its value.

i hope that makes sense.

Phil Parkin SSC Guru Points: 246955 More actions · Answer 3

Try setting a breakpoint after the ExecSQL task so that you can inpect the runtime contents of your vSQLString variable ... perhaps that will throw some light on what is happening.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chris-475469 SSCrazy Points: 2986 More actions · Answer 4

I set a breakpoint at the postExec event of the exec sql task and the variable seemed to be set correctly.

I'll triple check that though.

Phil Parkin SSC Guru Points: 246955 More actions · Answer 5

Please check that both of the variables are being set correctly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Chris-475469 SSCrazy Points: 2986 More actions · Answer 6

Thanks, Phil.

I again looked at the value of the variables and I noticed that the value for vDateThreshold was set incorrectly due to some ordering of the tasks. I just completely missed that when looking into the issue and everything is now working as expected.