SSIS:将 sql 命令作为变量传递给 ole db 源 [英] SSIS: passing sql command as variable to ole db source

问题描述

我在 SSIS 中创建了 2 个变量，即 ProductValue 和 SQLQuery.SQLQuery 包含如下 sql 语句:

I have created 2 variables in SSIS, namely ProductValue and SQLQuery. The SQLQuery contain the sql statement as below:

"SELECT * FROM Products Where ProductID > " + @[user::ProductValue]

当我通过 data access mode = sql command from variable 将 SQLQuery 变量传递给 Ole db 源时，系统返回如下错误消息:

When I pass the SQLQuery variable to Ole db source via data access mode = sql command from variable, system return the error messages as below:

Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E14  Description: "Incorrect syntax near '+'.".

推荐答案

您需要将 SQL 查询变量设置为 EvaluateAsExpression.

You need to have your SQL Query variable set to EvaluateAsExpression.

如果您检查变量网格中 SQL 查询变量的值，它应该是这样的:

If you check the value of the SQL Query Variable in the Variables Grid it should say something like:

SELECT * FROM Products Where ProductID > 1

如果不是，则说明您没有正确定义变量.SQL 查询变量的值不应包含任何引号或 +，在这种情况下只应包含表达式.

If it doesn't then you have not defined your variable properly. The Value of your SQL Query variable should not contain any quotes or +, only the expression should in this case.

这篇关于SSIS:将 sql 命令作为变量传递给 ole db 源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！