2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
bn25
  • 47
  • 7

3 Answers3

2

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.

The right way to do that is by using SQL Command with parameters:

EXEC ProcName ?

And select @[User::vDateThreshold] as parameter.

If it is not working then check your procedure code and make sure it generate a specific result set. If the result set is dynamic and columns are not fixed then you have to define it in the query using WITH RESULTSETS keyword.

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

From the name of @[User::vDateThreshold it seems like an SSIS datetime variable. Try setting this to a variable with an explicit cast and then executing the stored procedure with the variable. Make sure there that are single quotes (') within the CAST function as you would use if this was done in SSMS. When concatenating a datetime variable within a string variable in SSIS, the datetime variable must be converted to text, which is done with (DT_STR, length, codepage) in the sample expression below. I'm not sure what version you're using, but testing this out on SSDT for Visual 2017 worked fine for me. This will cover if you still want to hold the SQL in a variable, however the solution that @Hadi posted is a good option if you'd prefer to go that route.

"DECLARE @pDate DATETIME
SET @pDate =  CAST('" + (DT_STR, 50, 1252)@[User::vDateThreshold]  + "' AS DATETIME)
EXEC ProcName @pDate"
userfl89
  • 4,610
  • 1
  • 9
  • 17
0

Thank you for the responses to my question.

I actually found the issue was with the ordering of my tasks in the package. When I looked closer at the values assigned to the relevant variables by using a break point on my exec SQL task I could see the wrong date was being passed to my proc. When I set the value of vDateThreshold at an earlier point the correct date value was assigned.

I think this was a case of looking at something for long enough that I was missing the obvious.

bn25
  • 47
  • 7