asked on

How to use TSQL in the Conditional Split Data Transfer in SSIS

I have a SSIS Project where I want to "pull" data from a file that only keeps the row with the Max Date. So if Salesman 111 has 3 orders dated 01/01/2011, 02/02/2013 and 03/03/2013. I want to keep the row with Salesman 111 and date 03/03/2013. The other rows 111 01/01/2011 and 111 02/02/2012 will not be used.

The SQL Code for this being:
SELECT Salesman, MAX([Date]) AS "Max Date"
FROM tblorders
GROUP BY Salesman

So in my SSIS package I was hoping to use the Conditional Split and that is where I stumble. I see all columns and functions that can be used, but not where I can script my code.

Please advise.

Vikas Garg

Hello,

The shortest solution for this would be extract all the data to the sql table and then write the below query to remove the unwanted records.

You can use execute sql task after the DFT and write the below query

;WITH CTE AS
(
SELECT Salesman, [Date] , ROW_NUMBER()over(partition by Salesman order by Salesman, [Date] desc) RN
FROM tblorders  
)

DELETE FROM CTE WHERE RN > 1

Open in new window

SeTech

ASKER

Looking to do this in the SSIS application, I understand your SQL and the SQL I supplied will work as well. How in the Conditional transformation task can this be done???

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial