Transforming Date types in SSIS from an Excel Source

Question

Post reply

Transforming Date types in SSIS from an Excel Source

roryoflynn1

Ten Centuries

Points: 1281
More actions
March 20, 2013 at 3:00 pm

#271898

Hi,
I am using SSIS to import data from multiple excel files into a SQL Server database staging table using a ForEachLoop container control flow task.
The staging table has a column Date of data type NVARCHAR.
The imported date values are not consistently formatted.
Is there some way in SSIS that I can ensure that all date formats are uniform so that they will be consistent in the Staging Table?
Ideally I would like them all to be in the format
30-Sep-2009
any guidance appreciated, thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

I would do this in a script component by using the .NET parse method.

Doing this in a derived column would be too complicated.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

devilsid SSC Eights! Points: 830 More actions · Answer 2

is there a particular reason you want the date to appear as 30-sep-2009 in the database???

if not suggest you should be able to use a normal data conversion to convert it to a date, and if you are running a report and would like to present the date in a particular format you can add formatting on the date to appear as you please.

hope this helps.

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 3

Hi devilsid,

thanks for your comment.

There is no particular reason I want that particular formatting, as long as they all values are consistently formatted I caan live with that.

When you say use a normal data conversion to convert it to a date,

do you mean within the ssis process prior to the data landing in the database table or after the data is in the table?

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 4

I have observed one further issue,

for example I examined one excel source file which has a date displayed in the cell as 30-Jan-2013, the cell is formatted as Date category with Locale English (U.S.)

This data ends up in the database table as 41304.

When I transfer this number 41304 from the database table back into an excel file and format the field as Date category it transforms to 30-Jan-2013.

I then looked at a different excel file source and even though the formatting was exactly the same , the data ended up in the database table in the "correct " format, that is, it is also 30-Jan-2013 in the database table.

My point is, some of these numbers (eg 41304) do contain correct information and should be preserved.

Also why would the import process randomly transform some source dates to numbers? any ideas?

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 5

If the ACE OLE DB driver thinks the column is a date column, it will try converting the numbers to a date.

Internally, dates in Excel are represented by a number (the number of (milli)seconds after a certain date).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 6

Hi Koen Verbeeck,

thanks for your input... I believe it is the number of days since 1-1-1900, however there seems to be some issues regarding leap years ....

roryoflynn1 Ten Centuries Points: 1281 More actions · Answer 7

Hi,

I eventually used the following code,

The excel serial data in my scenario always begins with '4'

The code below is fired at the loaded staging table in the database..

thanks for your contributions they are greatly appreciated.

update test_table set Dates = dateadd(d,cast (Dates as int),'1899-12-30') where Dates like '4%'

update test_table set Dates = CASE WHEN ISDATE(Dates) = 1 THEN CONVERT(nvarchar(100),CONVERT(Date,Dates),113)END

devilsid SSC Eights! Points: 830 More actions · Answer 8

Spot on Veerbeek..

i have come across date issues myself when using excel as a data source ... apparently is not best practice using excel (but thats a different story)

as per your comment .. try an explicit data conversion to date before it goes to the landing table.. has worked fo me in the past.