Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transforming Date types in SSIS from an Excel Source Expand / Collapse
Author
Message
Posted Wednesday, March 20, 2013 3:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:53 AM
Points: 21, Visits: 135
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
Posted Wednesday, March 20, 2013 3:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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
Posted Wednesday, March 20, 2013 6:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:39 PM
Points: 18, Visits: 78
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
Posted Thursday, March 21, 2013 3:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:53 AM
Points: 21, Visits: 135
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
Posted Thursday, March 21, 2013 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:53 AM
Points: 21, Visits: 135
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
Posted Thursday, March 21, 2013 4:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:42 AM
Points: 13,295, Visits: 11,086
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
Posted Thursday, March 21, 2013 4:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:53 AM
Points: 21, Visits: 135

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
Posted Thursday, March 21, 2013 10:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 2:53 AM
Points: 21, Visits: 135
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
Posted Thursday, March 21, 2013 7:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:39 PM
Points: 18, Visits: 78
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 »

Add to briefcase

Permissions Expand / Collapse