posts - 10, comments - 49, trackbacks - 0

Wednesday, August 20, 2008

Using System Variables as Parameters in an OLE DB Source Query

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.

posted @ Wednesday, August 20, 2008 9:31 AM | Feedback (10) | Filed Under [ SSIS Madness ]

Powered by:
Powered By Subtext Powered By ASP.NET