asked on

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?

Brian Crowe

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

ASKER CERTIFIED SOLUTION

Vikas Garg

membership

This solution is only available to members.

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

Start Free Trial