SSIS – Add logging to SQL Server database table sysssislog

Possibly save 1 hour of your time: This shows how you can add logging for your SSIS package to SQL Server database table sysssislog. The table is automatically created and records will be added automatically.

Open your package in Visual Studio or SSDT. On the top menu, select SSIS >> Logging. Check the checkbox at the top so that you want logging at the package level. Then on the right pane, select provider type of SSIS log provider for SQL Server. Then click Add to add the provider. Check the checkbox for the provider and select your database configuration.

configure SSIS Logs part 1

On the Details tab, you can check the checkbox for logging OnError and OnTaskFailed. This will ensure that you will get log in your table when there is a package error or when a task failed.

configure SSIS Logs part 2

After you write to log to your table, you can also setup Send Mail Task on these log data. On the tab Event Handlers in Visual Studio or SSDT, you can set up OnTaskFailed with Data Flow task to get data from this database table sysssislog. Then you can generate the log flat file somewhere. Then use Send Mail Task to email this log.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s