Arbitrary parameters in SSIS for OLE DB

Ryszard Dżegan

5.00/5 (3 votes)

1 Jul 2014CPOL4 min read

26.3K

How to get around restrictions for parameters in OLE DB connection type while working with SSIS and BIDS.

Introduction

While working in Business Intelligence Development Studio (BIDS) and using OleDb connection I encountered a problem that I can use explicitly parameters in SQL query only in very limited places like in WHERE clause:

SQL

SELECT Name
FROM Source1
WHERE Name = ?

Trying to use them in other places caused an error. Here are couple of trivial examples:

SQL

SELECT ?

SELECT Name, UPPER(Name + ?) AS 'UpperName'
FROM Source1

SELECT s1.Name, s2.Name
FROM Source1 s1
FULL OUTER JOIN Source2 s2 ON s1.Name + ? = s2.Name

Each of which results in following error:

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.

Suggested solution is to use "SQL command from variable" option instead of "SQL command". By creating string variable and moving there our query and then by changing Data access mode to mentioned option, we can solve majority of such issues, BUT... String variable can contain up to 4000 characters. If our query is longer and we can't shorten it or turned it into stored procedure and must use it as it is, then there is a kind of trick that I will describe here.

Preparation

Let's start with database and tables that I will use here:

SQL

CREATE DATABASE TestDatabase
GO

USE TestDatabase

CREATE TABLE Source1(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
CREATE TABLE Source2(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
CREATE TABLE Destination(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
GO

INSERT INTO Source1 VALUES(N'Source1')
INSERT INTO Source2 VALUES(N'Source2')
GO

We should get such a simple database:

Now go to the BIDS and prepare a package:

Visible data flow is contained in following Control Flow component:

Let's make an use case test. I want to extract names from source table, but I don't know which source I will use. Therefore I use an integer variable called TableId that will determine my source. Please edit OLE DB Source component and write the following SQL Query as SQL Command:

Now click the Parameters button and notice that an error was raised:

Script Task

Suggested solution for an error is to use "SQL command from variable" option instead of "SQL command" and we could do this if our query didn't exceed 4000 characters. But this is not the case here. For the sake of simplicity, we need to imagine that our query is bigger than available limit and we can't put it into string variable. Therefore we need to find a way to get around this limitation.

The solution that I will describe here is to user Control Flow component called Script Task (with C# language). Please add a Script Task to Control Flow as follows:

The next step is to prepare variables for SQL query and source table id that we want to load. Of course we can't just put our original query into string variable as it is, since it is too large! (we need to imagine this). But we also can't leave it empty because BIDS assumes that the variable contains real SQL query that can be parsed, validated and used out of the box at design time. To appease BIDS, we need to put there something that will define all columns and their types. I use select statement with NULL values casted to specific types for all columns or empty strings and zeros for simpler types. Here, we will use empty string as a placeholder for Name column:

In addition, we need to indicate that our variable contains an expression. Use variable properties to set EvaluateAsExpression option to true:

Since we have SQL variable, we need to alter it at runtime using Script Task. In order to achieve that, we need to declare TableId variable in ReadOnlyVariables section and SqlQuery variable in ReadWriteVariables section:

After that we are ready to edit the script:

After clicking "Edit script..." button (and a while) Visual Studio appears with some boilerplate. We need to navigate to Main method that will contain "TODO: Add your code here" comment:

Replace TODO comment with following code:

var tableId = (int)Dts.Variables["User::TableId"].Value;

var sqlQuery = Dts.Variables["User::SqlQuery"];
sqlQuery.EvaluateAsExpression = false; // Without this line, variable won't be updated.
sqlQuery.Value = string.Format("SELECT Name FROM Source{0}", tableId); // Update query.

The code is easy and self-descriptive. We are fetching declared variables and preparing new SQL query in its final form. Here is the screenshot of filled Main method:

After clicking save button and closing Visual Studio, our Scipt Task is ready to go. However, we didn't finish the Data Flow yet. Edit OLE DB Source component and set Data access mode to "SQL command from variable". Then select User::SqlQuery as Variable name. Notice, that we see only static value with defined placeholders:

Connect OLE DB Source with OLE DB Destination:

Time to edit OLE DB Destination. We can do this, because BIDS knows what columns are transfered from OLE DB Source due to our placeholders in SqlQuery variable:

There is one thing left. Since we used empty unicode string instead of NULL casting to NVARCHAR as column placeholder (empty string is shorter), we need to explicitly enlarge output string length of that column. Open Advanced Editor of OLE DB Source. Navigate to Input And Output Properties tab page. Select the Name column from Output Columns and set its Length property to sufficient size: