SSIS Select record with last date from flat file column input and place only last column into database

I have a flat file .csv I am importing into a database table using SSIS.  Each file will have multiple "Platform numbers" with a timestamp.  I would like to select only the last record for each "Platform number" with the last time stamp, and put only that row into database.  

sample data:

247311.75,Platform 311128 data,2015-09-08T23:19:01.227Z
247318.03,Platform 311128 data,2015-09-08T23:24:01.292Z
247322.75,Platform 311128 data,2015-09-08T23:29:02.06Z
247322.88,Platform 311128 data,2015-09-08T23:34:02.178Z
247323.31,Platform 311128 data,2015-09-08T23:39:02.301Z
223094.9,Platform 311129 data,2015-09-08T02:06:11.554Z
223095.06,Platform 311129 data,2015-09-08T02:11:12Z
223095.06,Platform 311129 data,2015-09-08T02:16:12.529Z
223097,Platform 311129 data,2015-09-08T02:21:13Z
223104.47,Platform 311129 data,2015-09-08T02:26:13.169Z
223109.77,Platform 311129 data,2015-09-08T02:31:14Z
223110.55,Platform 311129 data,2015-09-08T02:36:14.136Z
223110.62,Platform 311129 data,2015-09-08T02:41:14.221Z

Desired Result:

247323.31,Platform 311128 data,2015-09-08T23:39:02.301Z
223110.62,Platform 311129 data,2015-09-08T02:41:14.221Z

Does anyone know a way to strip out only the last date time?
LVL 5
rtayIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Although I'm sure there is a way to do this in SSIS I would recommend just dumping the data into a staging table and then run a SQL query against it to do the actual insert.  The query is fairly trivial at that point.

WITH ctePlatform AS
(
   SELECT columnA, Platform, DateColumn,
      ROW_NUMBER() OVER (PARTITION BY Platform ORDER BY DateColumn DESC) AS RowNumber
   FROM StagingTable
)
INSERT INTO DestinationTable (...)
SELECT ...
FROM ctePlatform
WHERE RowNumber = 1
Vikas GargAssociate Principal EngineerCommented:
Hi,

You can remove duplicates using Sort Transformation as shown in the below image.

You need to select the column having Platform  data
and that's it.

Remove Duplicate in SSIS

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.