SSIS passing paramater in SQL command with ODBC

Question

I am trying to run a variable in an expression builder SQL command for OBDC.

The expression looks like this below

"SELECT Date(Curdate())                   AS 'Load Date', 
       Sum(CASE 
             WHEN pomstatus = 'TBR' 
                  AND Date(portdate) < Curdate() THEN 1 
             ELSE 0 
           END)                          AS 'Total Removes', 
            Min(CASE 
                         WHEN pomstatus = 'TBR' THEN portdate 
                         ELSE 'N/A' 
                       END) AS 'Oldest Removes', 
       Sum(CASE 
             WHEN pomstatus = 'TBR' 
                  AND Date(portdate) < Curdate() 
                  AND Date(portdate) > Subdate(Curdate(),  @[User::Days] )THEN 1 
             ELSE 0 
           END)                          AS 'Total New Removes', 
       Sum(CASE 
             WHEN pomstatus = 'TBR' 
                  AND acctype = 'b' 
                  AND Date(portdate) < Curdate() THEN 1 
             ELSE 0 
           END)                          AS 'SOHO Removes', 
       Sum(CASE 
             WHEN pomstatus = 'C-U' 
                  AND Date(dd) < Curdate() THEN 1 
             ELSE 0 
           END)                          AS 'Total Confirmed U', 
            Min(CASE 
                         WHEN pomstatus = 'C-U' THEN dd 
                         ELSE 'N/A' 
                       END) AS 'Oldest Confirmed U', 
       Sum(CASE 
             WHEN pomstatus = 'C-U' 
                  AND discoservice LIKE '%s%' 
                  AND Date(dd) < Curdate()THEN 1 
             ELSE 0 
           END)                          AS 'Total Confirmed Satellite', 
            Min(CASE 
                         WHEN pomstatus = 'C-U' 
                              AND discoservice LIKE '%s%' THEN dd 
                         ELSE 'N/A' 
                       END) AS 'Oldest Confirmed Satellite', 
       Sum(CASE 
             WHEN pomstatus = 'C-U' 
                  AND Date(dd) < Curdate() 
                  AND Date(dd) > Subdate(Curdate(),   @[User::Days] ) THEN 1 
             ELSE 0 
           END)                          AS 'Total New Confirmed U', 
       Sum(CASE 
             WHEN pomstatus = 'pwo' THEN 1 
             ELSE 0 
           END)                          AS 'Total Pending WO', 
       Sum(CASE 
             WHEN pomstatus = 'etbr' THEN 1 
             ELSE 0 
           END)                          AS 'Total Escalated TBR', 
       Sum(CASE 
             WHEN pomstatus = 'wtnr' THEN 1 
             ELSE 0 
           END)                          AS 'Total Waiting TNR' 
FROM   POM.mainorders 
WHERE  acctype <> 'c';"

I get the below error:

Error popup

I tested the same script but replaced the 2 variables with the number 2 and it was successful, so I know the script itself is working. It looks to me like the variable isn't passing properly.

As for how I am populating the variable, the variable gets populated form an execute SQL Task imaged below:

Execute SQL task

Execute SQL Task

Query Builder Result

See this answer: https://stackoverflow.com/a/46739168/1507566 — Tab Alleman, May 30 '18 at 20:21
Possible duplicate of [How to pass variable as a parameter in Execute SQL Task SSIS?](https://stackoverflow.com/questions/7610491/how-to-pass-variable-as-a-parameter-in-execute-sql-task-ssis) — Tab Alleman, May 30 '18 at 20:22

score 2 · Accepted Answer · answered May 30 '18 at 19:14

It doesn't recognize the variable inside the string

try concatenating the string with this:

Subdate(Curdate(), "+ @[User::Days] + " )

Personally, I would create a stored procedure in the database and pass the two parameters, but not everyone has access to do that.

score 0 · Answer 2 · answered May 31 '18 at 13:59

That's not how parameters work with the Execute SQL Task.

Either you use string concatenation as @KeithL suggests or properly map variables (@[User::Days]) to parameters (depends on your provider - either an ordinal based ? marker or a named parameter @myvalue)

Since your provider indicates OLE DB, you will need to use a ? as the placeholder and it then becomes a 0 based ordinal system. Click on Parameter Mapping and the name of the parameter is 0 and the Variable is @[User::Days]

See also Books OnLine - Map Query Parameters to Variables in an Execute SQL Task

Thanks for the comment, I did try this but I couldnt get it working/couldnt understand it properly. KeithL suggestion worked but I will keep this in mind for future problems. — Richard Nguyen, May 31 '18 at 15:08

SSIS passing paramater in SQL command with ODBC

2 Answers2