Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

|||SWEET!! Thanks!

Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

Derived Column / Change date format

Hi,

Is there a way using a derived column transformation to change the format of the date eg:

The input flat file's date is in yyyy/mm/dd and i need to convert / change this to yyyy/dd/mm

Is this possible?

Thanks in advance,

Slash.

Substring and concatenation, perhaps?|||.. would work because it is not really a date is it? It is just a string. Worth bearing in mind since display of dates is really a client issue not a SSIS/Server issue, unless you are taking a string and converting to a date type. Then SSIS will use the "client" as in user account settings that the process runs under when interpreting the string.|||

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

|||

slash85 wrote:

The weird thing is that the original flat file date is all yyyy/mm/dd but when it comes to import into the database as datetime. It inconsistently changes the format of the date some dates appear correct and some appear as yyyy/dd/mm. Is there anyway to over come this / have you heard of anything like this before?

Thanks for the replies,

Slash.

The database isn't returning multiple formats once inserted... That is, when doing a select from a database table, you aren't seeing multiple formats in a datetime field. It's impossible. If the destination field is a character field, and you're converting to datetime before inserting, then perhaps you have some invalid dates that cannot convert.

To my knowledge, without converting to a string, and using the substring function, you will not be able to get the format you desire.