SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transforming Date types in SSIS from an Excel Source


Transforming Date types in SSIS from an Excel Source

Author
Message
roryoflynn1
roryoflynn1
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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
Attachments
date-sample (2).jpg (20 views, 39.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34283 Visits: 13270
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
devilsid
devilsid
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 90
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
roryoflynn1
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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?
roryoflynn1
roryoflynn1
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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?
Koen Verbeeck
Koen Verbeeck
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34283 Visits: 13270
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?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
roryoflynn1
roryoflynn1
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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 ....
roryoflynn1
roryoflynn1
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 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
devilsid
devilsid
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 90
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search