ssis ETL dates into Excel

Question

I'm using SSIS ETL (I'm a newbie) and trying to extract dates from a SQL server table in format "08/03/2013 00:00:00" to an Excel 2013 file. I need the dates in Excel to be in format "dd/mm/yyyy", they must remain as dates so that the hierarchy remains. ie when you do an autofilter on the column you can see the date groups. There are also some null entries in the column of data.

Things I have tried in ETL script:- 1)Direct conversion from SQL table to Excel file - Result "2013-03-08 00:00:00",yyyy/mm/dd ss:ss:ss no hierarchy. I have tried changing the Excel column format but no matter what I select the date stays the same. 2)I have then added a Date Conversion to the ETL, to change the SQL date to DT_DATE - Result "3/8/2013", m/d/yyyy, no hierarchy. 3)I have then tried a Date Conversion, to change the SQL date to DTW_STR - Result "2013/03/08 00:00:00", yyyy/mm/dd ss:ss:ss no hierarchy. 4)I have then tried various ETL conversions to strings but none are recognised by Excel as dates so no hierarchy.

I'm not in a position to "re-educate" users to a new date format of "yyyy/mm/dd" I'm not a strong coder so would prefer to do this within ETL if possible.

So to summarise what I'm after I need the dates in Excel to be actual dates that can be grouped as such in format dd/mm/yyyy.

Not an ideal solution, but you could try you adding another column to the outputted Excel, in which you could use the DATEVALUE function to convert the text to an Excel Data and then format appropriately? — maxhob17
Just a thought, but are you tied in to using SSIS? Would a data connected excel table you can reference be a suitable alternative? support.office.com/en-us/article/… — iamdave
Thanks for the suggestions, I was just hoping that SSIS ETL was compatible with Excel, ETL is already being used to process the file, I guess it has been designed with extraction in mind rather than trying to populate back into Excel. — Andy P

Jonathan Porter Jonathan Porter · Accepted Answer · 2017-01-12T14:24:57

I believe what should do is try using the derived column transformation.

Derived column name: newDate

Derived column: Replace 'originalDate'

Expression: RIGHT("0" + (DT_WSTR, 2)DATEPART("dd", [originalDate]), 2) + "/" + RIGHT("0" + (DT_WSTR, 2) DATEPART("mm", [originalDate]), 2) + "/" + (DT_WSTR, 4)DATEPART("yyyy", [originalDate])

This will take each part and convert it into a string format so that we can concatenate them back together into the format we want. You may need to enclose everything in parenthesis and convert it back to date format if excel doesn't recognize it. (DT_DATE)(...)

ssis ETL dates into Excel

1 Answers