Custom SSIS Logging Example

 

SSIS Logging Example : Logging Detailed Information and Errors

20140610

Created By : JRD

References

Example based on:

http://www.sqlsafety.com/?p=272

Misc References:

Multiple Errors (advanced)

http://agilebi.com/jwelch/2007/05/05/handling-multiple-errors-in-ssis/

SSIS Utilities on CodePlex for ssis lots of cool tools:

https://ssisctc.codeplex.com/

SSIS Logging Example Project

Pre-Requisites: Step 1-3

AdventureWorks2012, and Adventureworks2012 DB
Create the table and sproc in the zip qry_LogEvent and ServiceEventLog, word doc in zip has screen shots

See [local], has these db’s

Login as : Sql Authentication

logginguser

pass@word1

            package password: Jas0n

1. The Logging Stored Procedure: (DONE) adventureworks2012.dbo.qry_LogEvent

-- log an INFO message

execute adventureworks2012.dbo.qry_LogEvent @ServiceID=10, @Message='Start from GetSAPMasterData()', @EventID=100

-- log an ERROR message

execute adventureworks2012.dbo.qry_LogEvent @ServiceID=10, @Message='Start from GetSAPMasterData()', @EventID=100

execute adventureworks2012.dbo.qry_LogEvent 'error happened!', 9, 103, 'ERROR', 'SqlException'

1.  
1. The logging Table (DONE) adventureworks2012..[ServiceEventLog]

This is where messages are logged:

a. select * from adventureworks2012..[ServiceEventLog] where Serviceid = 10

2. Your SSIS pck’s uses Service ID = 10 this is the ID used in Logging below:

SSIS: Step 3-6

Open the project , test the connection, all the below steps are already done in the example project but are documented for assistance.

3. *** Configure SSIS Package for Logging! Lets do the first task
1. Add Pre/Post Event Handler if u so desire

2. Add OnError Event Handler for the Task:

                                          i.    Pass the ErrorDescription into the SQL task as follows,

1.    Open the “Error Custom Logging” SQL task here to see:

                                        ii.     

4. Execute
6. Check Results, see how your Machine Name is listed, schema of table is modeled after the “Windows Event Log” schema, If your machine name is not listed then an error occurred logging to table.

select * from adventureworks2012..[ServiceEventLog] where Serviceid = 10

8. Test error Logging!
1. Create an ERROR to test:

SELECT FirstName,

             MiddleName,

cast(1/0 as nvarchar)             LastName

FROM Person.Person

2. Execute package
3. Check errors, take note we got the Full Error message!!!

4. Set sql back to way it was

SELECT FirstName,

             MiddleName,

LastName

FROM Person.Person

5.  

9. If you don’t want error to interrupt flow, : (same as try catch{} with no throw), this would allow ur package to continue execution.
1.  

Source Project https://drive.google.com/file/d/0B_g_1Tnrt6oFc2JaaTBUeFVHSzA/edit?usp=sharing