2

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
sql2015
  • 591
  • 3
  • 13
  • 34
  • 1
    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.Mc Jan 05 '18 at 09:51
  • ye if I manually change the excel column format to time is changes correctly. I just need t achieve this without manually changing – sql2015 Jan 05 '18 at 10:27
  • Here's a sample of some code (that can be run in a SSIS task) that formats coumns: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/57e84b3f-1f92-4d8c-8603-91899b2a3419/formatting-excel-text-to-currency-using-ssis-script-task?forum=sqlintegrationservices 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.Mc Jan 05 '18 at 10:58
  • 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 Jan 05 '18 at 11:13
  • 3
    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.Mc Jan 05 '18 at 11:16
  • @Nick.McDermaid, I agree! You can also export the SSRS as Excel – MiguelH Jan 05 '18 at 14:39
  • @Nick.McDermaid you should compile all these comments into an answer so that the question can be closed. – Tab Alleman Jan 05 '18 at 14:42
  • Why not convert the string to `TIME` instead of `VARCHAR(8)` before exporting into Excel? – digital.aaron Jan 05 '18 at 19:09

1 Answers1

0

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124