• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2326
  • Last Modified:

how to import NULL data into datetime fields

I have a database field
birth_date (datetime, null)

My source file has birth_date column which is having 'NULL' as data in all the rows. I used a derived column transformation to have datatime column and used the expression as :
TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)birth_date

But when I run the package, I get an error that the column could not be converted.
Please help how to handle nulls for numeric and datetime fields

Thanks.
0
dorsun
Asked:
dorsun
  • 6
  • 4
1 Solution
 
PedroCGDCommented:
If you already has your column as NULL why you trying to put into NULL again?!
OR is coming as a STRING from source and you want to convert to date and NULL?
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 
dorsunAuthor Commented:
In the source file, birth_date column is having 'NULL' as data in all the rows. And it is coming as string with value 'NULL'.
So that is the reason, I am not able to insert into database field which is of type datatime.

Please suggest.

Thanks
0
 
PedroCGDCommented:
ah, ok!
Check the package I made for a similiar question in EE:
http://www.experts-exchange.com/Microsoft/Development/Q_24133458.html

Helped?
regards,
Pedro
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dorsunAuthor Commented:
Pedro,
I have verified the solution of have provided in the example.
But its working only when I have atleast one valid date in the date columns.
But in my case, all the rows have NULL.
I am attaching my source file and the text file having the schema for the destination table.
Can you please look at this and give ur suggestion.

Your help is much appreaciated.

Thanks.
Profile-Sample.xls
Dest-Table-schema.txt
0
 
dorsunAuthor Commented:
Pedro,

Going according to your solution, the error occured during the data conversion stage.
Error:
[Data Conversion [898]] Error: Data conversion failed while converting column "birth_date" (839) to column "birth_date_db" (7939).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Please help.

Thanks.
0
 
PedroCGDCommented:
ok...
could you send me the file?
regards,
Pedro
0
 
PedroCGDCommented:
sorry... You already attached... :-)
I will see....
just a moment
0
 
PedroCGDCommented:
OK...
Check attached package I made for you.
Add it to a exsitent SSIS Project.
Helped?
regards,
Pedro
www.pedrocgd.blogspot.com
Package-EE92-dtsx.txt
SSIS-Interface.JPG
0
 
dorsunAuthor Commented:
Thanks Pedro. It works!
0
 
PedroCGDCommented:
I made a example project for you and give a B?!
ok..
Pedro
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now