posts - 10, comments - 49, trackbacks - 0

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.

Print | posted on Wednesday, August 20, 2008 9:31 AM | Filed Under [ SSIS Madness ]

Feedback

Gravatar

# 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
8/20/2008 2:28 PM | Talleyrand
Gravatar

# 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 -
8/21/2008 9:17 AM | James Nix
Gravatar

# 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
8/26/2008 12:01 AM | Chad
Gravatar

# 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 -
8/26/2008 8:17 AM | James Nix
Gravatar

# 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.
11/10/2008 2:49 PM | Todd McDermid
Gravatar

# re: Using System Variables as Parameters in an OLE DB Source Query

Great. Thanks a lot! :)
2/2/2009 2:54 AM | Jaap Wagenvoort
Gravatar

# 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

10/8/2010 10:11 AM | Terry Ritzman
Gravatar

# 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.
3/8/2011 6:12 AM | farouk chi
Gravatar

# re: Using System Variables as Parameters in an OLE DB Source Query

Thanks for this neat elegant solution.
9/22/2011 11:39 PM | JonG
Gravatar

# 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
7/25/2012 10:28 AM | Michael
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET