SSIS: passing sql command as variable to ole db source

Question

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 '+'.".

Ben English · Accepted Answer · 2013-06-12 17:55:25Z

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 Jun 12, 2013 at 17:55

Ben English

3,9382 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kumar Vaibhav Over a year ago

How to "You need to have your SQL Query variable set to EvaluateAsExpression" ?

Ben English Over a year ago

@KumarVaibhav it's in the variable properties

meekash55 Over a year ago

can't the variable be initialized by value at runtime. In my case i am generating the SQL query at run time upon checking conditions and wanted to store them in variable and later that variable to use it in Data flow task... is it possible?

Elmar · Accepted Answer · 2016-10-17 07:26:06Z

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 Oct 17, 2016 at 7:26

Elmar

3321 gold badge3 silver badges11 bronze badges

Comments

chetan sharma · Accepted Answer · 2018-07-18 06:16:25Z

0

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

answered Jul 18, 2018 at 6:16

chetan sharma

111 bronze badge

Collectives™ on Stack Overflow

SSIS: passing sql command as variable to ole db source

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related