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

Answer: 
> 
> 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.

* [Parameterized OLEDB source query](https://stackoverflow.com/questions/18288183/parameterized-oledb-source-query)

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.

* <https://www.mssqltips.com/sqlservertip/2356/overview-of-with-result-sets-feature-of-sql-server-2012/>
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"

```