Creating my first SSIS package to transform an Excel file and doing ok with most things (Have already written an Excel VBA to do this, but attempting to recreate in SSIS instead)
So far I have got the format I want through data flows and transformations, but I now need to iterate through the dataset and add a row with certain data, every time the date or employee id changes. Data is sorted fine.
I have attached a small sample data (already transformed) with privacy information removed. output3.csv
When column A or column H changes I need to add a record. (Column A is employeeid, Column H is shift date)
Example row 9 I need to add a record with the date time from the first record in column K and the previous record in column L
ie - a shift for that agent from 9:30 to 16:45
And again whenever the date changes - or the employeeid changes
Any suggestions on the best way much appreciated