Paramaterising SQL in SSIS

Question

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!

Answer 1

I would create a script task and concatenate the strings into one variable and use that as your source: Dts.Variables["SQLQuery"].Value = Dts.Variables["BaseSQL"].Value.ToString() + Dts.Variables["MyVariable"].Value.ToString(); Then, in the advanced properties under the component properties tab, you need to change the ValidateExternalMetadad property to False so SSIS will not try to prevalidate your sql query in your variable which would give you a run time error. Just created a simple package and that seemed to work. Hope this helps.

-Ryan

Answer 2

You should set the Expression property of SQLQuery, instead of the Value property. If you do it correctly, the Variable Value window should show you the result of the expression:

UPDATE mytable set something = 'bar' where something_else = 'foo'