SSIS - Fixing data with an incorrect datatype in the source data

Question

I'm an SSIS newbie and I've hit a snag trying to convert what should be a "Duration" column into seconds in my package.

Essentially the Excel source data column has been incorrectly typed as a TIME datatype in excel with the h:mm AM/PM format (ie. An 11 minute episode is being displayed as 11:00 AM)

Ideally I would like to convert this to seconds in an int datatype in my DB. What would be the best way to go about this?

Answer 1

1. Create the DFT something like this:

2. Use the following inside derived column : SUBSTRING(REPLACE((DT_STR,100,1252)Duration,":",""),1,4)

   Duration is my column name in excel, which is DT_Time type(set via advanced editor)

What I am essentially doing is, converting Time to String, removing :, taking the first four character (hhmm).

Hence output is like :

input      output

12:00:00AM  1200
12:30:00AM  1230