Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.
 image
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
 

image

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.

image

 
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 MikeDavisSQL.com. 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.
image 

Here is the output from the Merge join.

image

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.

image
 
The final Data Flow will look like the following image.
 

image

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


Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.