Transforming Date types in SSIS from an Excel Source

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Business Intelligence » Transforming Date types in SSIS from an Excel...

Transforming Date types in SSIS from an Excel Source

Rate Topic

Display Mode

Topic Options

Author

Message

roryoflynn1

Posted Wednesday, March 20, 2013 3:00 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:00 PM
Points: 7, Visits: 71

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

Post Attachments

date-sample (2).jpg (3 views, 39.86 KB)

Post #1433502

Koen Verbeeck

Posted Wednesday, March 20, 2013 3:27 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376, Visits: 6,472

I would do this in a script component by using the .NET parse method.
Doing this in a derived column would be too complicated.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1433507

devilsid

Posted Wednesday, March 20, 2013 6:35 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:26 PM
Points: 16, Visits: 61

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.

Post #1433560

roryoflynn1

Posted Thursday, March 21, 2013 3:16 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:00 PM
Points: 7, Visits: 71

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?

Post #1433649

roryoflynn1

Posted Thursday, March 21, 2013 4:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:00 PM
Points: 7, Visits: 71

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?

Post #1433705

Koen Verbeeck

Posted Thursday, March 21, 2013 4:35 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 6:39 AM
Points: 9,376, Visits: 6,472

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).

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1433707

roryoflynn1

Posted Thursday, March 21, 2013 4:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:00 PM
Points: 7, Visits: 71

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 ....

Post #1433711

roryoflynn1

Posted Thursday, March 21, 2013 10:10 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, April 01, 2013 1:00 PM
Points: 7, Visits: 71

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

Post #1433889

devilsid

Posted Thursday, March 21, 2013 7:44 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, April 17, 2013 10:26 PM
Points: 16, Visits: 61

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.

Post #1434111

« Prev Topic | Next Topic »

Permissions