SSIS中ODBC SQL命令表达式构建器传递变量失败问题

问题回顾 #

你在SSIS的ODBC SQL命令表达式构建器中使用用户变量@[User::Days]时执行报错，但将变量直接替换为数字（比如2）后SQL脚本能正常运行，说明SQL逻辑本身没问题，问题出在变量的传递或表达式格式上。该变量是通过Execute SQL Task赋值的。

可能的原因 #

1. 变量数据类型不匹配：@[User::Days]的类型可能不是整数，导致拼接SQL时出现语法错误
2. SQL表达式中变量引用方式错误：直接在字符串中写入@[User::Days]，SSIS无法正确解析变量
3. Execute SQL Task的变量赋值存在问题：比如结果集映射错误，导致变量没有被正确赋值

解决步骤 #

1. 检查变量的数据类型

确保@[User::Days]设置为整数类型（Int32）：

• 在SSIS的变量窗口中找到该变量，查看“数据类型”列
• 如果是字符串类型，修改为Int32，因为SQL的Subdate函数需要整数参数，字符串拼接后会导致SQL语法错误

2. 修正SQL表达式的变量拼接方式

在SSIS表达式中，不能直接在SQL字符串里写@[User::Days]，需要将变量转换为字符串后和SQL语句拼接。正确的表达式如下：

"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(), " + (DT_WSTR, 2)@[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(), " + (DT_WSTR, 2)@[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';"

这里用(DT_WSTR, 2)把整数变量转换成字符串，确保能和SQL字符串正常拼接。你可以点击表达式构建器的Evaluate Expression按钮，查看拼接后的完整SQL语句是否正确。

3. 验证Execute SQL Task的变量赋值

• 确认Execute SQL Task的Result Set设置为Single Row（因为你要返回单个值给变量）
• 检查Result Set映射：在Execute SQL Task的“Result Set”选项卡中，确保查询返回的列正确映射到@[User::Days]，且列的数据类型和变量类型一致（比如查询返回整数，变量也是Int32）
• 单独运行Execute SQL Task，通过设置断点查看@[User::Days]是否被正确赋值（在调试模式下，右键变量窗口的该变量，选择“Watch”查看值）

4. 推荐使用参数化查询（更安全可靠）

如果你的ODBC数据源支持参数化，建议不要用字符串拼接，而是使用参数映射，避免SQL注入和类型问题：

1. 在ODBC源的SQL命令中用占位符?代替变量：

   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(), ?)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(), ?) 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';
   

2. 进入ODBC源的Parameter Mapping选项卡，添加@[User::Days]变量：
   • 参数名称填0（ODBC参数按顺序索引，第一个占位符是0，第二个是1）
   • 数据类型选择对应类型（比如Integer）
   • 长度按需设置

内容的提问来源于stack exchange，提问作者Richard Nguyen