SSIS iterate through dataset transformation

Posted on 2011-05-10
Last Modified: 2012-05-11
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
Question by:Makrini
    LVL 21

    Accepted Solution

    see image below. image
    LVL 10

    Author Comment

    Apologies for the delay.  This pointed me in the right direction - thanks
    LVL 10

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now