To insert varchar records in datetime field

Question

To insert varchar records in datetime field

sqlstud

SSCrazy

Points: 2527
More actions
October 10, 2012 at 12:37 am

#263725

Hi,
I have table "Trial1"
One of the columns is
DATERECEIVED DATETIME NULL
Loading data from Excel to staging table using ssis 2005.
Datareceived column contains data as below
17/12/2010
18/12/2011
DECLINED
NULL
I need to load the data as it is. But thing is that, datatype is datetime..
We can modify the column as varchar...
But i need to know whether is it possible to load the data whatever present in the excel?
Regards
SqlStud

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sean Lange SSC Guru Points: 286573 More actions · Answer 1

sqlstud (10/10/2012)
Hi,
I have table "Trial1"
One of the columns is
DATERECEIVED DATETIME NULL
Loading data from Excel to staging table using ssis 2005.
Datareceived column contains data as below
17/12/2010
18/12/2011
DECLINED
NULL
I need to load the data as it is. But thing is that, datatype is datetime..
We can modify the column as varchar...
But i need to know whether is it possible to load the data whatever present in the excel?
Regards
SqlStud

Well it sort of depends on what you want. You currently have a datetime column but obviously "DECLINED" isn't going to work there. If you want to keep your datetime datatype (which I strongly recommend) then you probably need to make that NULL. If you want to maintain the data exactly as it is excel you will have to change to a varchar column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sqlstud SSCrazy Points: 2527 More actions · Answer 2

Well it sort of depends on what you want. You currently have a datetime column but obviously "DECLINED" isn't going to work there. If you want to keep your datetime datatype (which I strongly recommend) then you probably need to make that NULL. If you want to maintain the data exactly as it is excel you will have to change to a varchar column.

Thanks Sean..

I have changed the datatype to Varchar....

Please find the attachement.

While make a preview in OLEDB Source the data in the date column is displayed as NULL. But in excel sheet, it is "DECLINED".

Could you please give me the solution?

Regards

SqlStud

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

sqlstud (10/10/2012)
Well it sort of depends on what you want. You currently have a datetime column but obviously "DECLINED" isn't going to work there. If you want to keep your datetime datatype (which I strongly recommend) then you probably need to make that NULL. If you want to maintain the data exactly as it is excel you will have to change to a varchar column.
Thanks Sean..
I have changed the datatype to Varchar....
Please find the attachement.
While make a preview in OLEDB Source the data in the date column is displayed as NULL. But in excel sheet, it is "DECLINED".
Could you please give me the solution?
Regards
SqlStud

I am with Sean - it's 100 times better to keep your dates in a datetime column and do something else with the DECLINED info - perhaps create another column to hold 'status', or whatever, and use that.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 4

Your Excel column is holding mixed datatypes, so you'll need to bring in the column as string and add IMEX=1 to the Excel connection string (Google it for details).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sqlstud SSCrazy Points: 2527 More actions · Answer 5

Phil Parkin (10/11/2012)
Your Excel column is holding mixed datatypes, so you'll need to bring in the column as string and add IMEX=1 to the Excel connection string (Google it for details).

Thanks Phil...

I added IMEX=1 to the connection string..

But it displayed the error in OLEDB Source as

Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Src_Product" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Regards

Sqlstud

sqlstud SSCrazy Points: 2527 More actions · Answer 6

It is solved..

i made change in the connection string

Data Source=<path\xlsx filename>;Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties="Excel 12.0;HDR=YES;IMEX=1";

Thanks to all

Regards

Sqlstud