SSIS: passing sql command as variable to ole db source

▲
1
▼

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]

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 '+'.".
ssis
asked on Stack Overflow Jun 12, 2013 by user1902849 • edited Feb 7, 2020 by Gryu

3 Answers

▲
4
▼

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

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

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.

answered on Stack Overflow Jun 12, 2013 by Ben English
▲
0
▼

In case anyone runs into this thread, you can change the setting when selecting "Show advanced editor" (=right click on the Oledb source) and select "Component Properties" tab and switch "AccessMode" to "SQL Command From Variable".

answered on Stack Overflow Oct 17, 2016 by Elmar
▲
0
▼

Put the command like this: "SELECT * FROM Products Where ProductID > " + (DT_STR, 10, 1252) @[user::ProductValue] + ""

answered on Stack Overflow Jul 18, 2018 by chetan sharma

User contributions licensed under CC BY-SA 3.0