Parameterized OLEDB source query

Question

I am creating an ETL in SSIS in which I which I want my data source to be a restricted query, like select * from table_name where id='Variable'. This variable is what I defined as User created variable.

I do not understand how I can have my source query interact with the SSIS scoped Variable.

The only present options are

• Table
• Table from variable
• SQL Command
• SQL command from a variable

What I want is to have a SQL statement having a variable as parameter

Solution 1

Simple. Choose SQL command as the Data Access Mode. Enter your query with a question mark as a parameter placeholder. Then click the Parameters button and map your variable to Parameter0 in the Set Query Parameters dialog:

More information is available on MSDN.

Solution 2

An inferior alternative to @Edmund's approach is to use an Expression on another Variable to build your string. Assuming you have @[User::FirstName] already defined, you would then create another variable, @[User::SourceQuery].

In the properties for this variable, set EvaluateAsExpression to True and then set an Expression like "SELECT FirstName, LastName, FROM Person.Person WHERE FirstName = '" + @[User::FirstName] +"'" The double quotes are required because we are building an SSIS String.

There are two big reasons this approach should not be implored.

Caching

This approach is going to bloat your plan cache in SQL Server with N copies of essentially the same query. The first time it runs and the value is "Edmund" SQL Server will create an execution plan and save it (because it can be expensive to build them). You then run the package and the value is "Bill". SQL Server checks to see if it has a plan for this. It doesn't, it only has one for Edmund and so it creates another copy of the plan, this time hard coded to Bill. Lather-rinse-repeat and watch your available memory dwindle until it unloads some plans.

By using the parameter approach, when the plan is submitted to SQL Server, it should be creating a parameterized version of the plan internally and assumes that all parameters supplied will result in equal costing executions. Generally speaking, this is the desired behaviour.

If your database is optimized for ad-hoc workload (it's a setting turned off by default), that should be mitigated as every plan is going to get parameterized.

SQL Injection

The other big nasty you will run into with building your own string is that you open yourself up to SQL Injection attacks or at the least, you can get runtime errors. It's as simple as having a value of "d'Artagnan." That single quote will cause your query to fail resulting in package failure. Changing the value to "';DROP TABLE Person.Person;--" will result in great pain.

You might think it's trivial to safe quote everything but the effort of implementing it consistently everywhere you query is beyond what your employer is paying you. All the more so since there is native functionality provided to do the same thing.

Solution 3

When using OLEDB Connection manager (with SQL Server Native Client 11.0 provider in my case) you can catch an error like this:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

So you need to explicitly specify database name in OLEDB Connection manager properties. Otherwise SQL Server Native Client can use different database name then you mean (e.g. master in MSSQL Server). For some cases you can explicitly specify database name for each database object used in query, e.g.:

select Name
from MyDatabase.MySchema.MyTable
where id = ?