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: 286590 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: 246986 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246986 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).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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