Changing Column value using derived col transformation

I am trying to load a csv file into a sql server table. The source file has 4 date columns.I have used derived col transformation to change data from string to datetime value before i insert them to SQL server DB. Now,one of the column in Source has values '0000-00-00'. I am new to SSIS and don know how to handle this. Because of this weird value , the load operation fails saying " Data conversion error". Whn i configured the error rows to be redirected into a table , then the insert operation was successful but only with half of the record which it has in the source file, Kindly help me in this.

more ▼

asked Apr 25, 2010 at 12:39 PM in Default

avatar image

1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You could use something like this to insert nulls for those known invalid "date" values.

[column1]=="0000-00-00" ? null : [column1]

If you need more complex features like the ability to validate incoming data as a certain data type, I recommend using a script component instead.

more ▼

answered Apr 25, 2010 at 11:32 PM

avatar image

Tom Staab ♦
14.5k 7 15 21

Perhaps ISDATE (http://msdn.microsoft.com/en-us/library/ms187347.aspx) would be a better choice validation wise?

Apr 26, 2010 at 03:21 AM Matt Whitfield ♦♦

Unfortunately, the ISDATE T-SQL function is not available in an SSIS expression.

Apr 26, 2010 at 10:04 AM Tom Staab ♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 25, 2010 at 12:39 PM

Seen: 1571 times

Last Updated: Apr 26, 2010 at 05:39 AM

Copyright 2018 Redgate Software. Privacy Policy