Exceptions in Data Flow Scripts

What is the "correct" way of dealing with exceptions in a data flow script component. i.e. am I supposed to catch all exceptions and then set some failure flag? The reason I ask is I've got a script in a dataflow which is occasionally throwing exceptions when trying to convert an empty string to a decimal. Problem was the package locked up and had to be terminated when the exception was thrown (and not caught in the script)?

The only thing which differentiates this package from others I've created is the data flow has a conditional split which creates 2 seperate paths loading 2 seperate SQL tables - the exception is being thrown in a script on one branch which seems to hand the entire flow

PS I have fixed the script so this doesn't happen, i.e. test if the input string is String.Empty and if so set the _IsNull property.

If you are going to go down the road of custom building functions inside a script versus using the derived column or conditional split transformations to look at your data (test your data for correctness), then you'll also have to build in your own error handling as the script component does not support error outputs as part of the data flow.

Phil|||

Hi Phil,

Thanks for the reply, I'm not sure you're answering my question though The problem is that if an exception is thrown in a script and not caught, the package simply locks up - the unhandled exception seems to be blocking - no event handler is called etc. So what I'm asking is how do you flag a failure in a data flow script (if it were a control flow script I would propably create a global catch handler with Dts.TaskResult = Dts.Results.Failure

Dave

|||I think the package should lock up though, as the script component doesn't support the error output stream. So if an error is encountered in a script task, how should the package continue? That is, the error is fatal to the script component.

Some of the other guys will undoubtedly have some more advice when they get in tomorrow morning, especially those that are well versed in script programming.

Sorry, but I won't be of no more help tonight, especially when I'm not sitting in front of SSIS.

Phil|||Does it really lock up, or does the package simply fail?|||

It locks up, i.e. if run from BIDS the scipt component goes yellow when it starts executing, and nothing more occurs.

In other data flows, exceptions do cause the script component (and ultimatly the package) to fail. The different seemingly being that the package has a conditional split and each path of the split appears to be running concurrently when the exception is thrown in the script.

It's interesting that there's no error output from a script - not sure what to make of that - of all my dataflow components, the ones which fail the most are my scripts , esp since I cannot single step through them. Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

|||

Dave Waterworth wrote:

Maybe I need to add and error number colume, handle the exception and set the error number in the script and then pass the subsequent flow through a conditional split?

Yep, a very valid solution.

Exception from HRESULT: 0x80131904

Hi,

I write a custom component (destination component) that handle the error of my dataflow.

The custom component works fine on design time and runtime by using BIDS.

When I'm using the same package that use my custom component with DTEXEC,

I got the following error :

System.Exception: AcquireConnections : Exception from HRESULT: 0x80131904

at SSISGenerator.SSISErrorHandler.ErrorHandlerDestination.AcquireConnections(
Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)

The error message point out that the problem is in acquireconnections method.

This is the code i'm using in my custom component for the acquireconnections method.

public override void AcquireConnections(object transaction)

{

try

{

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET,cm.Name));

this.sqlConnection = cmado.AcquireConnection(null) as SqlConnection;

if (this.sqlConnection == null)

throw new Exception(String.Format(MSG_ACQUIRECONNECTIONS_ADONET, cm.Name));

if (sqlConnection.State != ConnectionState.Open)

this.sqlConnection.Open();

}

}

catch (Exception e)

{

throw new Exception(MSG_ACQUIRECONNECTIONS + e.Message);

}

}

Does someone got an idea ?

Mathieu

Could you try to debug your method while called from DTExec?

Thanks.

|||

Hi,

I did some tests and I will explain you the result.

Debug the method with DTExec

I debugged with DTEXEC a solution that contain two project

01 - class library project for the custom component

02 - integration service project for a test package

I did the following tasks for the debug proces

01 - On debug tab, I put the information needed for debugging (start an external program to debug (DTEXEC) and the file for the parameter)

02 - I put the debug dll in GAC

03 - I make sure that I have a breakpoint set on PreExecute

04 - I hit F5 and the error didn't show up.

05 - I took the custom component (dll) and I copy it on the dev server.

06 - I put the debug dll in GAC.

07 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

08 - I remove the debug information and I've compiled the custom component in release mode.

09 - I put the release dll in GAC

10 - I executed the test package on my machine with DTEXEC and the error didn't show up.

11 - I took the custom component (dll) and I copy it on the dev server.

12 - I put the release dll in GAC

13 - I executed the test package on the dev server with DTEXEC and the error didn't show up.

After all these tests, I was suprise since the error message disappears. So, I will do other tests and post the result.

For now, I have one idea for this error message. When I did some tests, I was using the same command line over and over. I'm using a environment variable that indicate the location of my dtsconfig file for the connection string of my connection. I'm not sure if I create the environment variable before openning the commandline. I think it can be one of the reason.

We will see.

Mathieu