SSIS data going into excel destination incorrectly

Question

in an SSIS package I import data from a flat file into a db, do a sql query and export results into a excel destination. my issue is I have a convert,cast statement which is fine in sql it returns a value as HH:MM:SS. the field is a string. my issue is when it goes into excel it is displaying the column data as MM:SS:0 i need it to stay as HH:MM:SS I have tried to use a data conversion and derived column. but I cannot seem to achieve this. in my sql db I have 2 fields that are datatype 'nvarchar' I want to add them together so have the following

CONVERT(varchar(8), CAST(time as datetime) + CAST(length as datetime), 108) as 'endtime',

this returns values like 14:22:01 when it exports into excel it is appearing as 22:01:0

any advice would be much appreciated

Excel does whatever it likes unfortunately. I don't know of a way to force excels formatting except to use automation to format the column afterwards. Not if you manually format the column the data will come through OK — Nick.McDermaid
ye if I manually change the excel column format to time is changes correctly. I just need t achieve this without manually changing — sql2015
Here's a sample of some code (that can be run in a SSIS task) that formats coumns: social.msdn.microsoft.com/Forums/sqlserver/en-US/… Excel will need to be installed in your SSIS server in order for this to work. Are you exporting data to excel to upload it into a different system, or is this some kind of report? — Nick.McDermaid
no the excel spreadsheet gets generated on server then emailed out as a report. I was just looking into scripts now, I'm new to SSIS so just trying to get my head round it — sql2015
So it's a report so formatting is critical. You know that SSRS is generally better for formatting reports? SSIS is not really a report builder — Nick.McDermaid

Hadi Hadi · Accepted Answer · 2018-01-05T22:15:57

You can format Excel column using a Script Task.

You have to use an Excel manipulation library like Micsoroft.Interop.Excel to change the format of the column to HH:mm:ss.

You can get a working example from the following question:

Format excel destination column in ssis script task

SSIS data going into excel destination incorrectly

1 Answers