SSIS – Flat File Package Logger – Part 1

Is it possible to develop something “re-usable in SSIS” ?

Yeeaaaahh !! We can develop re-usable control flow parts. Its available as part of SQL Server 2016.  

I believe MSBI professionals aware of this new feature.

For more details on this: https://msdn.microsoft.com/en-in/library/mt620034.aspx

Requirement:

1. Generic requirement in package development is that we need to log the package details like package name, start time, end time, row count details, error details. So it helps a lot for the deployment/developer team to review the package status and investigate the issues if required.
2. Each and every package will have the logger. Ideally we will develop some control flow or data flow to achieve it and copying the same to all the packages.

Issues:

Most of the time if there is a change need to implement in the logger, we need to touch all the packages for modifying it. It will be a headache for the developers.

Resolution:

After the release of above new feature in SQL server 2016, It helps a lot for the developers to maintain their code change at one place i.e., control flow parts only. It will refresh the reference of the control flow parts wherever it is used in the packages.

Logging Type:

1. Flat file
2. Tables

Let’s discuss the ReUsable flat file logger

Flat File Logging:

Generic requirements in logger is something like below and some parameters should be configurable

1. Flat file should be created in the mentioned path
2. Folder should be created in the mentioned path with the name of package execution date
3. Text file should be created dynamically in the name of the package and execution time
4. Summary of the package name, package start time, package end time, row count details
5. if error records available, that should be written in the text file

Using above requirement, i have developed the control flow part using “script task“. I have defined few variables which will be used across the package for capturing the logs.

PackageLogger.dtsxp – control flow part which i have developed. See below diagram for variables which are defined for package logger

Once the control flow part is developed and  will be available for using across the packages in the Package Parts [available as part of SSIS tool box]

how to identify that the control flow which is part of the control flow part or usual control flow ? Note the right top corner of the control part, it has marked as ‘P‘. So this component is ReUsed 😉

Variables can be configured differently for each and every package

This how the log file will looks like

Here is the link to download the Reusable Flat File logger

Try it out the attached package logger and let me know your thoughts.

If you like this post, please share with your professional network.