Ole db source issue in Data flow

  • Chris-475469

    SSCrazy

    Points: 2986

    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

  • Phil Parkin

    SSC Guru

    Points: 243474

    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\?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Chris-475469

    SSCrazy

    Points: 2986

    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: 243474

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Chris-475469

    SSCrazy

    Points: 2986

    <!--more-->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: 243474

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Chris-475469

    SSCrazy

    Points: 2986

    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.

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

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