SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Try Catch within an SSIS Script Tasks and Components

 Using a Try/Catch/Finally within your Script Components and Script Tasks is just good coding practice.  The thing you want to be able to do is push any errors you trap to a log file.  The syntax is somewhat simple, but it took me some time to research it and it differs depending upon whether it's aComponent or Task.  I'll go over each.

Within the Component there is no access to the Dts object, so you need to work through theComponentMetaData object as shown below.  Please note the pbCancel is defined as a bool at the top of my Component.

catch (SqlException sqle)
   ComponentMetaData.FireError(1, "parse and insert into T011_Tree", sqle.Message, "", 0, out pbCancel);

The tricky part is using the ComponentMetaData to access the log.  The Component will fail if any of theTry block fails, regardless of the pbCancel value.  This is primarily to interact with the log, like a good programmer :)
Within the Task you access the log from the same object you access just about anything, Dts.  To access the log you simply use Dts.Log and you can fail the Task by simply setting the Dts.TaskResult=Dts.Results.Failure.


This blog is syndicated from SSIS - SQL Server Tidbits(http://www.josefrichberg.com/)


No comments.

Leave a Comment

Please register or log in to leave a comment.