Paramaterising SQL in SSIS
- by Anonymouslemming
Hi all,
I'm trying to paramaterize some queries in SSIS. After some reading, it sounds like my best option is to create one variable that contains my base sql, another that contains my criteria and a final variable that is evaluated as an expression that includes both of these. I want to end up with an SQL query that is effectively
UPDATE mytable set something='bar' where something_else='foo'
So my first two variables have the scope of my package and are as follows:
Name: BaseSQL
Data Type: String
Value: UPDATE mytable set something = 'bar' where something_else =
Name: MyVariable
Data Type: String
Value: foo
My third variable has a scope of the data flow task where I want to use this SQL and is as follows:
Name: SQLQuery
Data Type: String
Value: @[User::BaseSQL] + "'" + @[User::MyVariable] + "'"
EvaluateAsExpression: True
In the OLE DB Source, I then choose my connection and 'SQL command from variable' and select User::SQLQuery from the dropdown box. The Variable Value window then displays the following:
@[User::BaseSQL] + "'" + @[User::MyVariable] + "'"
This is as desired, and would provide the output I want from my DB.
The variable name dropdown also contains User::BaseSQL and User::MyVariable so I believe that my namespaces are correct.
However, when I then click preview, I get the following error when configuring an OLE DB Source (using SQL command from variable):
TITLE: Microsoft Visual Studio
Error at Set runtime in DB [Set runtime in myDb DB [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: "Must declare the scalar variable "@".".
(Microsoft Visual Studio)
Can anyone advise what I'm missing or how I can resolve this please ?
Thanks in advance!
