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 Handling

Hello,

I am trying to catch an error, but not able to. I am storing the value of @.@.Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT?). The Error that it throws it this:

Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.

Can you please let me know whats going wrong here? Thanks a lot in advance.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0
Begin
PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'
GOTO ROLLITBACK
End

Hi Mannubhai,

Which version of SQL server you are using.. if you are using SQL05, try using Try..catch block.

If you are using SQL2k.. then in above script there is no variable declared and there is no rollitback lable..

And YOU SHOULD GET THE ERROR SAYING THAT THE TABLE DOES NOT EXISTS.. AND THIS ERROR HAS THE SEVERITY LEVEL OF 16.. which says that the error can be fixed by the user and terminates the batch.. so you are not able to catch it.

Have a look on the severity levels for more details.

Regards,

|||

There are errors that cannot be caught.

Erland has written up some good articles on the subject. Please take a look.

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

|||

Hey Thanks Sajid. I am using SQL 2000. The Code that I have given is just a snippet. It is not the complete one. The Complete SP has the variables declared and yes, the ROLLITBACK label is also present. The error as I said is, INVALID OBJECT #TEMPO_. But can this error NOT be caught in the way I have attempted? I think it does not even execute this statement --> SET @.ErrNo = @.@.Error. Why does this happen? Please let me know. Appreciate the response.

Mannu.

|||

You can refer to Erland's documents on Error handline.. spcially the first link provided by OJ. .that will surely help you out to understand the Error handling in details..

Regards,

|||

Hi All,

I seem to have solved this. The below insert which I was using, I put that into a variable and executed it using sp_executesql. A Dynamic SQL.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

I put this like this:

SET @.SQL = '

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_'

EXEC sp_Executesql @.SQL

SET @.ErrNo = @.@.Error.

IF ErrNo <> 0

....

....

This gave the proper results. Let me know if more information is required.

Mannu.

|||

I partially agree with your solution.

But there is few risks you might face if you given the access permissions on SP level (readonly at Table)..

The alternate possible solution might be,

Option 1#:

Code Snippet

If Exists (Select * from tempdb..sysobjects where id=object_id('tempdb..#tempo_'))

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Else

Goto RollITBack

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Option 2#:

Wrapp the insert statement in new proc & call it from your main proc.

Code Snippet

Create Proc InsertFromTemp

as

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Go

Exec InsertFromTemp

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End