A93

SSIS Obtain error Msg

1.In the SSIS Data Flow Task get ErrorCode from Component

Many component provide error output, you can route to another component for get the error raw data in the data flow, error output contains the following metadata : ErrorCode, ErrorColumn, Flat File Source Error Output Column

http://salvoz.com/blog/2011/12/27/ssis-using-the-script-component-to-obtain-an-error-description/

2.In the SSIS Control Flow Obtain Script Task Exception

https://philcurnow.wordpress.com/2013/11/02/catching-and-storing-exceptions-in-ssis-script-tasks/

https://social.msdn.microsoft.com/forums/sqlserver/en-US/709db169-8da6-4cee-bc6d-80081b5d2bc7/how-to-get-detail-error-description-of-a-script-task

1.Get a Package level variable: UserErrorDescription As String(5000)

2. Inside Script task write below code in the catch block. Note: Do not include variable UserErrorDescription in the ReadonlyVariableList.

Import using Microsoft.SqlServer.Dts.Runtime;  in the script task.

using Microsoft.SqlServer.Dts.Runtime;
try{}
catch (Exception ex)
{
//local variable to update error description
                Variables LockedVariable = null ; 
                Dts.VariableDispenser.LockOneForWrite("User::UserErrorDescription", ref LockedVariable);
                LockedVariable["User::UserErrorDescription"].Value = "Task:ScriptTask,Error Description: " + ex.Message.ToString();   
                LockedVariable.Unlock();
                //Raise Error event
                Dts.Events.FireError(0, "Script Task", "Error", string.Empty, 0);  
}

3. Create a task in package level onError event .

4. Now you can access the variavle to report your error. Note: Here include the variable UserErrorDescription in the ReadonlyVariableList.

MessageBox.Show(Dts.Variables["User::UserErrorDescription"].Value.ToString());  

3. In the SSIS get Runtime Package Error Message and mail to System Manager

It’s really simple, creat a send mail task in package level onError event in error handler, click send mail task on expressions menu, edit expression properties and add MessageSource = @[System::ErrorDescription], the System variable will record error description