Strange problem with Date type data in excel versus SSIS

Question

Strange problem with Date type data in excel versus SSIS

Ganeshkumar005

Ten Centuries

Points: 1140
More actions
January 19, 2012 at 4:13 am

#146800

Would appreciate a quick response.
Importing data from excel file having a column of Date type data DD/MM/YYYY. In DB its get stored and refelect as YYYY-MM-DD 00:00:00:000. While for many of them it is working fine, for few fields the date & Month getting switched. It should be uniform. Pl see the example below for quick reference. I have tried all options between text & date type in SSIS & excel and mix of both and so on and tired. Data conversion is already there.
Date Of Birth
20/07/1970
12/07/1965
06/05/1963
1970-07-20 00:00:00.000 -- Correct
1965-12-07 00:00:00.000 -- Wrong
1963-06-05 00:00:00.000 -- Wrong
Regards,
GaneshK

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

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

JasonYousef SSCommitted Points: 1769 More actions · Answer 1

I'm not sure what could be the advise as you said tried everything, and even the Data conversion, I would suggest as a last resort to roll your own expression in a derived column..

something like

(DT_DBTIMESTAMP)(Right([COLUMN],4) + "-" + substring( [COLUMN],FINDSTRING( [COLUMN], "/", 1) +1 , FINDSTRING( [COLUMN], "/", 1)-1 ) + "-" + substring( [COLUMN],1,FINDSTRING( [COLUMN], "/", 1) -1 ) )

Mahesh Lakkala SSC Veteran Points: 222 More actions · Answer 2

Can you please post the data type that you have used in your source and destination tasks ?

Also, ensure that "IMEX=1" is set in the extended properties of excel connection string.

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 3

Jason - I m bit uncomfortable with ur suggestion and have not tried yet.

Mahesh - I dont see a scope for changing this option. Using VS2008.

Thanks to both of you.

Btw, on a side note, what I see by the help of data viewer is that Date is flowing fine to destination, but when I do select on table, I get the day & month switched. Hope this helps you you to help me.

Regards,

Ganesh.

Mike-1076638 Old Hand Points: 381 More actions · Answer 4

You are wasting your time

SSIS does not work correctly with EXCEL.

http://www.dbsoftlab.com/imex1.html

Save everything as text file and carry on or continue trying until you find the problem you cannot fix

Mike

dbSL

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 5

I agree with Mike.

I banged my head with SSIS and decided not to use SSIS at all together with Excel.

(of course you can save as a textfile etc but then you need to automate that procedure).

It is worth learing VBA and do the interaction with SQL-server with for example remote OLE DB

and VBA.

//Gosta

Mike-1076638 Old Hand Points: 381 More actions · Answer 6

Funny is not it.

Large corporation cannot fix this problem

But we are a small company can work with excel correctly

Mike

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Mike-1076638 (2/29/2012)
You are wasting your time
SSIS does not work correctly with EXCEL.

No, the JET OLE DB provider cannot read Excel files decently.

Big difference.

Whatever the problem is, it can be solved in SSIS (see the expression that Jason posted).

But most of the time it is easier to read from .csv files.

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

Yadava Shettigar SSC-Addicted Points: 400 More actions · Answer 8

You need to use Right and Substring function in derived column transformation as suggested by

JasonYousef