Which SSIS System Variable holds error text

Question

I am running a SSIS package using SQL Server 2008 Job. The package crash at some point while running. I have created my own mechanism to grab the error and record it in a table. So I can see that there is an error with an specific task, but could not find what the error is.

When I run the same package from BIDS, it works perfect. no error.

What I want to do is, I need to write that error string to my own table which shown in the "Execution Result" tab.

So the question is which system variable holds the error string in SSIS.

Answer 1

The error is stored in the ErrorDescription system variable. See Handling Errors in the Data Flow for an example of how to get the error description.

Also, if you want to capture error information into a table, SSIS supports logging to a table using the SQL Server Log Provider. You can also customize the logging.

Answer 2

Too easy.

1. Left-Click (highlight) on the object you want to capture the error event (Script, or Data Flow, etc.)
2. Click on 'Event Handlers' - screen should open with Executable = object you clicked and Event Handler = OnError
3. Click URL (click here to create....)
4. Drag Execute SQL object from SSIS Toolbox
5. Configure to the database/table you want to house the error message
6. Write INSERT INTO DB.Schema.Table(DBName, SchemaName, TableName,ErrorMessage,DateAdded)
7. Write VALUES (?,?,?,'I am smart',getdate())
8. Click Parameters and select the USER::Variables for the ?'s + my comment.

Since this is ran at the database server it will pass in the ?'s. My SAC is already at the database as a value but you will have selected System::ErrorDescription as parameter 3. Remember, this array is 0 based. DO NOT TRY TO NAME THE PARAMETERS. Instead, number them 0 to ~? The datatypes are based on what you have going in; mine are all VARCHAR so... :)

This is a much better solution than just logging whatever the server allows you to. I can also add a counter variable and adjust it wherever I like; then pass it to the event OnError. This will allow me to pinpoint exactly where the last successful object completed; works best in scripting objects but also available in other areas.

I'm using this so I can process thousands of cycles without actually failing the package. If a table doesn't exist or a column doesn't exist I simply log it for further review later. Oh yeah, I'm cycling through hundreds of databases capturing their architecture and maximum column size used; not to be confused with maximum column size. Example: TelephoneNumber comes from a source column of char(500) (definitely bad programming but...you can't change everything so..). I capture the max len of that column and adjust the destination column to accommodate that size +/- a certain percentage.

If a table doesn't exist or a column doesn't exist anymore I log the error and keep churning. At the end, I can evaluate those entries and see if I can actually remove them from my warehouse. This happens more in the TEST and STAGE environments than in PROD. However, when a change goes through to PROD I most definitely will identify it as it's coming in to the warehouse.

Everything is configured, this includes dynamic MERGE/JOINs, INSERT, SELECT, ELEMENTS, SIZES, USAGESIZE, IDENTITY, SOURCEORDER, etc. with conversions of data to destination datatypes.

ALL that because the systemic version of logging will not provide you with the granularity you might need for this type of operation. This OnError Event Handler can if setup properly.

Answer 3

Check this out! He has explained with a Step by step process on how to configure SSIS logging which has the error message parameter.