Getting Previous Row in SSIS Data Flow

There is no native function built in to SSIS to get the previous row in the data flow. But with a little work you can do this and make it perform much better than a SQL cursor and you don’t have to use the dreadfully slow OLEDB Command transform.
In this example have some data that shows the day a patient was admitted to the hospital and you want to figure how many days it has been since the person was in the hospital last. So you want to calculate the days since the last admit date. Here is the table.
You can see you are going to need the previous row to calculate the number of days since the patient’s last admit date. To accomplish this you are going to use the merge join transform and a little trick with row numbers.
In the data flow you will have two OLEDB sources from the same table. The query in the first source will be.

SELECT        PatientID, Name, AdmitDate, DaysSinceLastAdmit, ROW_NUMBER() Over(Order by PatientID, AdmitDate) as RowNum
FROM            dbo.Patient
Order by  PatientID, AdmitDate

The Right query is :

SELECT        PatientID, Name, AdmitDate, DaysSinceLastAdmit, (ROW_NUMBER() Over(Order by PatientID, AdmitDate)) + 1 as RowNum
FROM            dbo.PreviousRow
Order by PatientID, AdmitDate


Notice you are ordering by the patientID and then the admit date but the row number will be the top ordered by column. Here is the output of the above query.


You will need to set up the OLEDB source as sorted in the advanced editor. The trick to this is to set the row number as sort key 1 and the Patient ID as sort key 2. If you need instructions on how to do this, check out my previous blog “Using Merges with Duplicate Rows” on Don’t forget to set IsSorted to true.

The Next transform will be a Merge join. The image below shows how the Merge Join is configured. It is using an Inner Join to eliminate null rows. It is getting the admit date from the right side which would be the previous row to the one on the left.

Here is the output from the Merge join.


Now you have the admit date and the previous admit date on the same row. It is just a simple derived column using the DateDiff function to get the days since last visit. Here is the code for that.
DATEDIFF(“d”,[AdmitDate Prev],AdmitDate)

The output after the Derived Column will look like the following image. Now you have the days.

The final Data Flow will look like the following image.


Now you can write this to a staging table and then update the Patient table using an Execute SQL Task.

This entry was posted in SSIS, Syndication. Bookmark the permalink.

One Response to Getting Previous Row in SSIS Data Flow

  1. JV says:

    Excellent article. Thanks… J

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s