An Un-CATCHable Error?

I’ve been using the scripting tools in SSIS for some time, but I came across something today that I can’t quite explain.  I normally don’t posts unresolved problems on my blog, but I’m trying out a strategy suggested by my friend Lee Everest by sharing unfinished work in the hopes that my research and troubleshooting can help someone else.

So here’s the scenario: I’m building an ETL framework using SSIS, which is now three levels deep (envision grandparent, child, and grandchild packages), and I’ve found that I need to be able to pass values from ancestor to descendant packages and vice versa.  While the former is easily done using SSIS configurations, the latter requires a little scripting to accomplish.  Additionally, this scenario requires that the leaf-level packages must be able to be occasionally run atomically as well as within the ETL framework, It was during the testing of those piecemeal executions that I discovered the issue at hand.

Sidebar: If you’re interested in a how-to on passing values from child packages back up to the parent, have a look at this blog post by Steve Fibich.

The Code

So here’s the code I’m using: 

i1

In a nutshell, this code will attempt to write to an SSIS variable named LeafLevelConfigSetting.  That variable does not exist in the current package, so the variable by that name must be inherited from an ancestor package.  What I intended to happen is that, if the package is executed by itself, the LeafLevelConfigSetting variable will not exist in the current scope so the LockOneForWrite() method will throw an exception that should be caught by the try/catch block, allowing the package execution to proceed without error.  In theory, because I’m trapping any exception thrown by the LockOneForWrite() method, my script task should succeed even when there is no LeafLevelConfigSetting variable.

The Problem

The package runs fine when executed as part of the larger ETL framework; the LeafLevelConfigSetting variable is supplied by one of the ancestor packages, and the script task succeeds in updating that value.  However, if I execute the package on its own, it fails every time:

i2

The Execution Log reads as follows:

Error: Failed to lock variable "LeafLevelConfigSetting" for read/write access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Huh?  That function call was within the try/catch block and should have been trapped within the script task without causing a failure of that task.  Now I’ve never claimed to be a hard core programmer, so this revelation led me to reexamine what I thought I knew about try/catch blocks within the SSIS script task. So I created a small script that I knew would fail: the following snippet causes a divide by zero error, which should be handled in the try/catch block:

i3

Unlike the previous script task, this one succeeded:

i4

As shown in this trivial example the try/catch block does behave as expected; exceptions within the try{} block are properly caught and do not fail the task.  So the unexpected behavior described here appears to limited to the LockOneForWrite() method (as well as its close relative LockOneForRead(), as I discovered during further testing).

 

The Solution (sort of….)

Now for my specific application, I was able to overcome this problem by accessing the Dts.VariableDispenser.Contains() to verify the existence of the variable I want to modify, as shown below:

i5

 

… but even though I can now move on from this problem, I’m still left with no explanation as to why I’m unable to catch this exception.  I’m curious if anyone else has encountered this problem, and if so, if there’s an explanation to why the exception thrown by this particular function cannot be caught by a try/catch block.

PASS BI Virtual Chapter

Did you know that PASS has virtual chapters, allowing members to participate in virtual meetings that are geographically agnostic?  One such group is the PASS BI Virtual Chapter which serves those interested in SQL Server business intelligence related topics.  Meetings are held via LiveMeeting, typically on a weekday around midday to allow a virtual lunch-and-learn experience.  I’ve attended several of these sessions and have presented at one of them, and found the experience as both attendee and presenter to be positive and useful.

Starting this week, I will be taking on a larger role with this group.  The chapter leader, Amy Lewis, is taking a few months off for maternity leave, so during her absence I’ll be helping to find speakers, facilitate meetings, and publicize the group.  I’ve also been asked to see if there are others who are interested in volunteering their time to help with this virtual chapter.

So if you’re looking for online opportunities to learn more about SQL Server business intelligence, or if you’re interested in speaking or volunteering with this group, let me know.  You can find more information about this chapter as well as videos, past presentation materials, and a schedule of upcoming events on the virtual chapter website at http://bi.sqlpass.org.

SQL Saturday is a’comin!

Just a quick reminder that registration is still open for SQL Saturday #35 in Dallas.  This event will be held at the Region 10 Education Service Center in Richardson on May 22, just over two months from now.

We’ve had a lot of excellent sessions submitted from scores of industry experts and Microsoft employees, and we’ll be finalizing the schedule and expect to publish it later this week.  Admission to the event is free, though we are offering lunch on the premises for $10 in advance.

This event is for anyone interested in SQL Server regardless of skill level – we have sessions that for everyone from beginner to seasoned professional.  So sign up today and join us in Dallas on May 22!  Ping me if you have any questions.

North Texas SQL Server User Group – March 2010

Join us this Thursday, March 18th for our monthly NTSSUG meeting.  SQL Server MVP Sean McCown will be continuing his series on Ground Zero SSIS at 6:00pm.

Our guest speaker will be Drew Minkin, who will be presenting a session on SQL Server data mining:

Data Mining for SQL Developers: This session will introduce SQL Developers to how SQL Server Analysis Services (SSAS)’s Data Mining algorithms can enhance your applications.  Topics covered will include basics of data mining theories and practices, data life cycle managment, building a data mining structure and model, and choosing an algorithm.

The Presenter: Drew Minkin has been working with Microsoft SQL Server since 1997.  He worked at Microsoft in the SQL Product’s CSS group and as a SQL Advisory Support Consultant from 2000-2006.  He has been a Senior SQL Consultant for Fujitsu, a Technical Lead for the startup Zilliant and a Business Intelligence Architect for a leading retail banking software company.

We meet at the Microsoft Campus in Las Colinas, in Bldg LC1 (the southernmost building); map it here.  As always, food and drinks are provided.

Hope to see you there!

Alpha Split in SSIS, Redux

So I’ve discovered another benefit of being a technical blogger.  Not only do you get some kudos when you write something that helps someone else, but if you offer up a less-than-optimal solution, you’ll get some suggestions on how it can be done better.  I’ve had my share of the former, but earlier this week I experienced the latter.

Last month I blogged about using the SSIS CODEPOINT() function to make it easier to split data streams based on a character value.  Although the method I suggested gets the job done, of my blog readers wisely pointed out to me that you can do range comparisons on character data using the SSIS expression language.  So, rather than doing a greater/less than comparison on the ASCII values of each of the significant comparison characters (the first letter, or perhaps the first two letters if you’re splitting deeper than just the first letter), you can simply run an alphabetical comparison, such as the following:

split_cs

As you can see, the syntax and complexity of this solution is much simpler than what I proposed using CODEPOINT().  Thanks to Hrvoje Piasevoli for pointing out a better way to do this.