Home > SSIS Best Practices > SSIS #107 – Parameterized Query in OLE DB Data Source: parameter information cannot be derived from SQL statements with sub-select queries

SSIS #107 – Parameterized Query in OLE DB Data Source: parameter information cannot be derived from SQL statements with sub-select queries

I have a few posts blogging about how to pass variables into parameterized queries into Execute SQL Task and OLE DB Data Source in a data flow.

As you gain more confidence in SSIS, you start to design your ETL process with higher efficiency and higher portability. That means using more variables, and more package configuration files. This is when you will start to hit the limitations. One of the limitations that is relevant to this topic comes from this unfortunate message:

Parameter information cannot be derived from SQL statements with sub-select queries

This message is generated from an OLE DB Data Source in a data flow. It has a simple SQL command, but with a parameter inside a sub-query.

image 

This is what you will see when you try to Preview or click the Parameters… button.

  image

This is a very interesting message actually. It is telling us why SSIS refuses to accept the query because a parameter is inside a sub-query. It also gives us a very interesting recommendation, that is to use the “SQL command from variable” mode.

Another recommendation Microsoft might have given us here is to create a stored procedure with parameters. It didn’t, because creating stored procedures is essentially taking us away from Integration Services, and back to the SQL Server land. I guess the Integration Services designers wouldn’t want that to happen.

Integration Services’ inability to parse SQL statements with parameters inside sub-select queries comes from the OLE DB provider. In my case, we are using the native OLE DB native provider. You would think that this provider will be “smarter” than other providers, such as OLE DB provider for Oracle. Unfortunately, no privilege here.

image

Use “SQL command from variable” for OLE DB Data Source

I don’t want to go back to the SQL Server land, so following the above recommendation is my choice. The screenshots and steps I am sharing with you here are really backwards in order. If you want to implement this recommendation in your own package, you will need to follow the steps below in backward order. 

To follow the recommendation from Microsoft, you will need to change the Data access mode from SQL command to SQL command from variable.

image

Select the user variable you have created that stores the query as an expression. You should see that your parameter(s) are actually showing the values you have assigned to the variable(s).

image

Create a variable to store the parameterized query

This is the package-scope variable I’ve created to store the parameterized query.

image

To make this work, there are a few things you will need to pay attention to:

1) The data type of this variable should be String. A word of caution here. My query has sub-query, but it’s really a very simple query. If you have a query that is very complicated, and possibly exceeds the max length of a String data type, then the recommendation from Microsoft is not really a good choice for you. Going back to the SQL Server land to create a stored procedure will be a more manageable solution.

2) In the Properties window, set EvaluateAsExpression to True. This will give you a chance to use the Expression Builder.

3) Open the Expression Builder by clicking on the eclipse button in Expression

image

Use the Expression Builder to build the parameterized query

Expression Builder is a invaluable tool in SSIS, but it’s not a tool I look forward to in my daily job. The reason is that everything needs to be wrapped around the double quote. When our variable itself is a String data type, it’s actually very easy to construct the query.

String is one of the most used data types, but it’s not the only data type. In my case, my variable varDAYS_HRS is set to 60, and it’s an integer. 

image

In the above screenshot, the two red boxes should be 60, if I hard-code it. Since all needs to be string, I must use some data type casting function to cast it as string. (DT_WSTR, <<length>>) is a way to do just that.

A more generic syntax for explicitly converting an expression from one data type to a different data type is here:

(type_spec) expression

See Cast (SSIS Expression): Convert SSIS Data Types from MSDN for details.

Always use the Evaluate Expression button on the Expression Builder screen to make sure that your expression is acceptable by Integration Services.

Create another variable to hold the dynamic parameter value

Variable varDAYS_HRS is created to hold the dynamic parameter we need to pass to the query. This is a simple user variable:

  image

“SQL command from variable” for OLE DB Data Source is a valuable recommendation

Next time, when Integration Services cannot parse your parameterized queries, give this recommendation a try if your query is reasonably simple and short.

Categories: SSIS Best Practices
  1. Vlad
    May 4, 2012 at 2:11 am

    Thank you very much Sherry.

    Like

  2. Lumumba
    June 18, 2012 at 4:19 pm

    Very good !! Thank you very much indeed !!

    Like

  3. Jay
    June 20, 2012 at 4:15 pm

    Your link was very helpful to resolve my issue, i was getting error about casting & got resolved using “(DT_WSTR,4)”.

    Thank you lot.

    Like

  4. anjali
    August 7, 2012 at 11:41 am

    Awesome… i was searching for this…

    Like

  5. Benjamin
    February 13, 2014 at 3:11 pm

    It’s amazing to me that Microsoft still hasn’t resolved this issue in SQL 2012??? Sigh… :0/

    Like

  6. Debasis
    September 25, 2014 at 7:12 am

    There is a limitation Using the Expression Builder to build the parameterized query. It can not be more than 4000 characters. Is there any solution??

    Like

  7. Namita
    January 16, 2015 at 8:32 pm

    Thanks a lot, Sherry!! You resolved my biggest issue…

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: