Exception.Data[key]=value

Hi.

Is it possible to transmit custom data from SQL 2005 to the client via Exception.Data[key]=value?

The idea
I have a number of SPs that accept userId parameter which must be verified. I have written a simple CLR SP (check_userid_valid) which performs the verification of the userId parameter's value. If the parameter's value is considered invalid an exception is thrown. When the exception (System.Exception) is instantiated additional data is added via the Data property of the exception:

System.Exception ex = new System.Exception();
ex.Data["Source"] = "check_userid_valid"

So, any SP that calls the check_userid_valid with invalid userId is expected to "crash" and the exception with all the additional data is expected to be propagated back to the client which, in turn, could read the data.
Unfortunately, it seems that the ex.Data contains only entries put by the MS SQL 2005 server itself, eliminating the rest.

The question is: how can I supply additional exception data with the exception I do throw from my CLR code on the server-side, so that is can be consumed at the client-side.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomException()
{
try
{
//try code goes here
System.Exception firstEx = new System.Exception("first exception");
throw firstEx;
}
catch (System.Exception ex)
{

}
finally {
System.Exception secondEx = new System.Exception("check_userid_valid");
throw secondEx;
}
}
};

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