Logging is an essential part of any application. It gives user an insight of application operation. And it proves valuable in event of any issue. Same stands true for SSIS packages as well. Since SSIS package will get executed usually by SQL Server Agent or some Windows Scheduled Task and in both case it is usually unattended, having logger gets really helpful when all of a sudden you start receiving alerts that some files are not being processed.
For this post, I have create a very simple demo to show how logging works in SSIS. For this demo, I am using file system task to transfer file from one folder to another folder. And I have created a log file to record each step of this process.
First I have added a file system task. And then when you right click on control flow tab, it gives few options and first option is logging. By selecting this option, it will open up another window which is basically logging configuration wizard. Select objects of containers for which you want to create logging. In this case I wanted to log everything for whole package so I selected folder. If you click on “Provider Type” you should see different logging options available to log events in SSIS. And good thing is we can select multiple logging options, for example file based logging for simple operational steps and windows event logging for critical errors. For sake of simplicity (and I am feeling lazy to go into too deep) I have selected “SSIS Log provider for text files”. There is another tab next to “Providers and Logs”, “Details” which lets user select which events user want to log into logger. If you click on “Advanced” it will expand current window and will give more fine grain choices for logging.
 |
 |
 |
 |
Once I selected logger type, then click on ADD. It will add logger into table below and next steps are to configure connection for that text log file. For that I just need to create new file connection and create a file to some desired location. And this is the end of logger configuration for me. Now when I run SSIS package, it will log each and every events because I selected that I wanted to log all events.
 |
 |
 |
As it can be seen here, it is really easy to create logger for SSIS package and it can be really handy when we face some error and we wanted to find more details about that error.
That’s it for now. I have uploaded this demo file over server for download.
It’s Just a Thought … 
