Parameterized OLEDB Source Request

Question

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

I do not understand how I can associate my original request with a variable SSIS scope.

The only existing options are

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

I want to have an SQL statement that has a variable as a parameter

Answer 1

Simple Select SQL command as the data access mode. Enter a 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 box:

Additional information is available on MSDN .

Answer 2

A lower alternative to @Edmund's approach is to use an expression for another variable to create your string. Assuming you already defined @ [User :: FirstName], then you would 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] +"'" . Double quotes are necessary because we are creating an SSIS string.

There are two great reasons why this approach should not be begged.

Caching

This approach will inflate your plan cache in SQL Server with N copies of essentially the same query. The first time it starts, and the value is "Edmund", SQL Server will create an execution plan and save it (because it can be expensive to create them). Then you run the package, and the value is "Bill." SQL Server checks to see if it has a plan for this. This is not so, he has only one for Edmund, and therefore he creates another copy of the plan, this time hard-coded to Bill. Lather-rinse-repeat and watch out for a reduction in available memory until it unloads some plans.

Using a parameter approach, when a plan is sent to SQL Server, it must create an internal configuration version of the plan and assume that all the parameters provided will result in equal costs. Generally speaking, this is the desired behavior.

If your database is optimized for the ad-hoc workload (it is disabled by default), this should be mitigated, as each plan will be parameterized.

SQL injection

Another big nuisance you'll encounter when creating your own string is that you open up SQL Injection attacks, or at least get runtime errors. It is as simple as the value of d'Artagnan. This single quote will crash your request, resulting in a packet failure. Changing the value to "'; DROP TABLE Person.Person; -" will cause great pain. "

You might think that it is trivial to quote everything safely, but the efforts to implement it are always wherever you ask, beyond what your employer pays you. Moreover, there is a built-in functionality that ensures the implementation of the same.