Looping scripts

I have an application that manages the booking of rooms in several different buildings, and am trying to build an SSIS package to build a table containing records showing the total number of hours per day that each building is being used. Because two bookings in the same building can take place at the same time or overlap with each other, it's not a straightforward sum of the hours per booking.

I've written a VBA script that loops through booking records comparing their start and end times, building a total value for each day, and then writing the building id, date and total hours to a table. My problem is that I'm not sure how to do this

in SSIS. The script component operates on row by row basis so doesn't seem to allow looping through records, and the aggregate component doesn't allow scripts.

I'm still pretty new to SSIS so may be missing something obvious, but if anyone has help to offer it would be greatly appreciated.

Thanks,

Tim

You will need an Execute SQL Task, a few variables, and a ForEach loop.

You need to begin with the Exceute SQL Task to read your table for the appropriate records and write them to a ADO RecordSet. The task will need a variable to write the recordset to and so you will need to define a variable of data type "Object" to hold the record set. In the Execute SQL Task set the ResultSet property to "Full Result Set" as you will be retrieving multiple rows. In the ResultSet option you will keep the ResultName to 0 and in the variable you need to choose the name of the variable you would have created.

In the ForEach Loop you need to use the ForEach ADO Enumerator and set the SourceVariable to the variable that holds the recordset. If you are planning to use the values of the columns retrieved from the Execute SQL Task within the ForEach Loop then you would need to define variables to pass them into, and then define the variables in the Variables option for the ForEach Loop.

I hope this helps.

|||

A few altenative approaches:

One, you can use a script component in the data flow to accomplish this. You'd have to capture the rows in an internal data store (a DataTable or array would work), and then process them once you have all the data for a building.

Two, you could use an Execute SQL task as desibull suggests, to get a recordset. However, rather than processing it in a For Each (which is going to introduce the same single record at a time issue), process it in a Script Task.

Three, it seems (at least theoretically) possible to build a SELECT statement which would do this for you. In particular, CTEs in SQL 2005 could help with this type of problem. More detail on the table structure could help determine if that is possible.

Considering you already have the VBA code for processing the data, I'd probably go with option 2.