Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Tim Mitchell

Tim Mitchell is a business intelligence consultant, author, trainer, and SQL Server MVP with over a decade of experience. Tim is the principal of Tyleris Data Solutions and is a Linchpin People teammate. Tim has spoken at international, regional, and local venues including the SQL PASS Summit, SQLBits, SQL Connections, SQL Saturday events, and various user groups and webcasts. He is a board member at the North Texas SQL Server User Group in the Dallas area. Tim is coauthor of the book SSIS Design Patterns, and is a contributing author on MVP Deep Dives 2. You can visit his website and blog at TimMitchell.net or follow him on Twitter at twitter.com/Tim_Mitchell.

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.

Comments

Posted by toddmcd on 23 March 2010

The problem may be that SSIS isn't actually only throwing an exception.  There seem to be (to me) two error-handling mechanisms in SSIS - exceptions, and error messages.  I haven't had the time to test this out myself, but I believe that you can cause the same end result by dividing by zero (w/o try/catch) as well as by calling FireError.

In your case, the LockFor<whatever> internals may be posting an error message to the event handler before or instead of throwing a .Net exception.

If that's the case (and remember that the SSIS internals are COM-based) then the Error Event may be informing the task to fail, even though you're catching the .Net exception.  There may be nothing you can do (aside from avoiding the problem as you've done).  I'm positive I've run into the same thing.

Posted by Carolyn Richardson on 29 March 2010

Could you write into a table? I've always used a build table that I populate along the progress of the package so that I can restart easily from the point of failure by looking at whats been populated in the build table. Usually adding a no of records field so that I can see that the expected no of records are being moved through each part of the process.

Posted by Richard Harper on 29 March 2010

Personally,  I think the system is working as designed.  By calling the package as a standalone package you are trying to access a variable that is meant to be there.  If it's not there who'se mistake is it ?  (And is it a mistake ?) In your case,  no it's not,  because you are doing it deliberately by SSIS cannot tell that so it takes the safest route.

Posted by rolandalexander on 29 March 2010

There's something to be said here also about using exception handling to test for the existence of an object. As you discovered in your "workaround", there is a specific method that can be used to determine whether or not the object exists. One should IMO always use available language features to check for existence, rather than trying to force an exception to deal with its absence.

I do not know, but suspect that what you are encountering here is similar to a precompile error in T-SQL.  In other words, there is some process which goes over the code before it executes, and throws an exception if it finds a reference to an object that doesn't exist. This can occur in a SQL Server stored procedure that refers to a non-existent table, for example. You can create the procedure without incident, but when you try and execute it, it results in an error.  As in your script example, local error handling doesn't respond, because the error did not occur during code execution: it occured *before* the code executed.

Posted by Tom Garth on 29 March 2010

I have to agree with rolandalexander that testing for existence is a best practice and should be instinctual whether you're using exception handling or not.

That said, I agree your Try... Catch is failing for some reason and if you ever figure it out please tell us. I'm sure there is validity to what is happening, but it isn't at all apparent.

Nice puzzler. Thanks.

Posted by smarttec on 29 March 2010

I was reading MS Visual Studio documentation and found something that may explain the behavior you observed:

- SSIS uses COM methods.

- COM methods report errors returning values in HRESULT, while .NET methods report errors returning exceptions.

So, when you look for a variable not yet created in your collection, the exception section doesn´t run at all. The article I read explains how to create a new exception and assign it to some HRESULT.

Please read the following:

ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.es/dv_fxinterop/html/610b364b-2761-429d-9c4a-afbc3e66f1b9.htm

Hope this may help.

Regards,

Fernando

Posted by Allen Nugent on 29 March 2010

This might be related to the issue behind a bug report I came across (which I now can't find) while trying to solve a problem with my 1st package. Basically, if a variable is defined in a child scope but referred to in the parent scope, the syntax checker will not report the problem but the package will fail. Maybe SSIS just doesn't bother to fully map all variables the way a compiler does.

Posted by smarttec on 29 March 2010

Sorry, the link was incorrect. Please check the following: "How to: Map HResults and Exceptions" (msdn.microsoft.com/.../9ztbc5s1.aspx)

Leave a Comment

Please register or log in to leave a comment.