We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

how to import NULL data into datetime fields

dorsun
dorsun asked
on
Medium Priority
2,520 Views
Last Modified: 2013-11-10
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.
Comment
Watch Question

Commented:
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

Author

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

Commented:
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

Author

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

Author

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.

Commented:
ok...
could you send me the file?
regards,
Pedro

Commented:
sorry... You already attached... :-)
I will see....
just a moment
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks Pedro. It works!

Commented:
I made a example project for you and give a B?!
ok..
Pedro
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.