Using System Variables as Parameters in an OLE DB Source Query

Wed Aug 20, 2008 by James Nix in ssis-madness

You can expect:

This is a low intensity post (you should be able to do this in your sleep).

I Expect (that you have the following experience):

Simple SSIS Dataflow work

OLE DB Source Queries with Parameters

The OLE DB Source allows you to enter a Select statement to retrieve data from a source. As I stated several times before, this is the preferred method. If you want to use parameters, this is the ONLY method you can use. It is fairly easy to use parameters. You just use a ? for each parameter you wish to map. Then you click the Parameters button and map your parameters to variables.

This seems easy enough, but if you select a System variable your package will not run. You will receive an error message similar to this one: [DTS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0010001. The reason for this is because when SSIS goes to map the parameters, it needs to lock the variables for reading. We chose a System variable and the OLE DB Source fails to lock the variable. I believe this is a bug.

The Solution

I searched high and low for a workaround and the only solution I found was to stuff the whole select into a variable and then use a script to take the System variable and stuff it in the Where clause. Ok, here comes a rant...

This is a CLUDGY FIX! Don't do it! You bypass the benefit of having a Parameterized query and you make your solution MUCH HARDER TO MAINTAIN. I can't stress this enough. You are making a huge mess for yourself and others. I have people complain to me all the time that SSIS is horrible and then I look at how they set things up and I want to smack them upside the head! /rant

Most people who use SSIS that I've worked with don't know that you can create Variables that are evaluated as expressions. Variables can just store data or they can execute an expression every time they are accessed. This feature allows you to do some really nice things and in this case, is the solution to the problem. I set up a variable named PackageName so that I could retrieve the System::PackageName variable. In the Package Explorer tab, I navigated to my variable, set its EvaluateAsExpression property to True and set its Expression to @[System::PackageName]. I went back to the OLE DB Source and changed the mapping to my variable. It now worked how I needed it to.

You can also get to the variable settings by just clicking on the variable in your Variables pane, but I wanted you to look at the Package Explorer! While you are in the Package Explorer, look around a bit and get familiar with it. It is a nice way to manage settings across your entire package quickly.

Legacy Comments


Talleyrand 2008-08-20	re: Using System Variables as Parameters in an OLE DB Source Query The locking only seems to be an issue within a dataflow task. An execute sql statement didn't exhibit the same behaviour for me. The issue I was running into yesterday was assigning a datetime value into a parameter and the quick and dirty way I got around it was to build the sql statement as an expression. More leisurely research lead me to using parameters as intended and set the data type as Date---not db_date, db_time, or db_timestamp. I liked the first poster's suggestion of prefacing statements built via expressions with a note to direct future maintainers to look under expressions. Second was what I found first when I was having parameter issue and the msdn post has the pros and cons of using expression vs parameterization. Thought they might be useful if others are here for parameter pointers. http://blog.boxedbits.com/archives/34 http://rafael-salas.blogspot.com/2007/11/ssis-mapping-parameter-inside-of.html http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2415979&SiteID=1

James Nix 2008-08-21	re: Using System Variables as Parameters in an OLE DB Source Query The locking appears to be an OLE DB Source bug. It works correctly in the Script Transforms too. - Madman -

Chad 2008-08-26	re: Using System Variables as Parameters in an OLE DB Source Query Madman, I have a SSIS dataflow that uses a script component to call a webservice for import. The import runs for about 1200 rows and then completes... There are still 7000 rows remaining. No errors, just completes. I kick off the package again and it runs for another 1200 rows and stops. I am using an OLE connection and using the sqlcommand, not the table/view option. Just curious if you have any thoughts of how I could identify why the package stops. If there was an error or something to go off of, (which there may be, but I haven't seen it from sql or the webservice) or some possible ideas of what I could look at. Thanks in advance, Chad

James Nix 2008-08-26	re: Using System Variables as Parameters in an OLE DB Source Query Are you using a Try..Catch inside your script? Make sure you aren't swallowing an error anywhere. Is the Script Component set up to be a transform or a destination? Can you post your script code (minus any sensitive data)? Are you sure your source data is clean? Are you using any variables in the script (ReadOnly / ReadWrite)? - Madman -

Todd McDermid 2008-11-10	re: Using System Variables as Parameters in an OLE DB Source Query I'd suggest creating another (User) variable, using property expressions to assign it the value of your System variable. Then use your User variable in the parameters. This would work with most System variables - since they don't change from the time the package executes.

Jaap Wagenvoort 2009-02-02	re: Using System Variables as Parameters in an OLE DB Source Query Great. Thanks a lot! :)

Terry Ritzman 2010-10-08	re: Using System Variables as Parameters in an OLE DB Source Query Unfortunately, Microsoft forces us to use cludgy code because they don't support parameters in subqueries for OLE DB connection! From Microsoft: When you are use an OLE DB connection manager, you cannot use parameterized subqueries, because the OLE DB source cannot derive parameter information through the OLE DB provider msdn.microsoft.com/en-us/library/ms141696.aspx

farouk chi 2011-03-08	re: Using System Variables as Parameters in an OLE DB Source Query The OLE DB Source allows you to enter a Select statement to retrieve data from a source. As I stated several times before, this is the preferred method. If you want to use parameters, this is the ONLY method you can use. It is fairly easy to use parameters. You just use a ? for each parameter you wish to map.

JonG 2011-09-22	re: Using System Variables as Parameters in an OLE DB Source Query Thanks for this neat elegant solution.

Michael 2012-07-25	re: Using System Variables as Parameters in an OLE DB Source Query Thanks! I've been banging my head on the keyboard for couple of hours before I finally arrived at this post