Script Components in SSIS
I recently wanted to solve a problem. Basically I had employee data with me in one table and their attendance information in another table. The task was to list out employees that were absent between a given range along with information about the dates on which they were absent. I had to solve this using SSIS as I was reading from an Access store and wanted to also do some transformations.
I was thinking about how to do the same, since it was easy just to list the employees who were absent on a given date range just by using an outer join, but I also wanted the date on which they were absent which required me to loop over the date range and then for each date find all employees who were absent. I was thinking about what to do when I ran into two more capabilities of SSIS that left me feeling very excited.
The first one was the concept of a script component which is a VB.NET code block in which you can write regular code, access package variables etc. This is a very powerful feature.
The second one was the ability to load data from a table into a recordset destination and then convert that recordset into an ADO.NET DataTable. When I was finding out how to do this, I ran into this post: http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx (thanks to all the people who contributed to the code sample here). It worked just great!!
Using these two features, I loaded the data from both the tables into two recordset destinations, converted them into data tables and then looped over them to get the data that I want and then spool the result to a DataReader destination and viola, my SRS report can then read off this destination.