NUll value column Peoblem (AS400) to sql

Question

NUll value column Peoblem (AS400) to sql

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

quillis131 Hall of Fame Points: 3706 More actions · Answer 1

kramaswamy (10/19/2011)
your source column is SMALLDATETIME, so you can't write the string "NULL" to it.
This expression:
ISNULL(column) ? "NULL" : column
has two data types it is attempting to evaluate to - a string and a dt_dbtimestamp.
What you should have is this:
ISNULL(column) ? NULL(DT_DBTIMESTAMP) : column
OR
ISNULL(column) ? "NULL" : LEFT((DT_WSTR, 30) column, 11)

ISNULL(column) ? NULL(DT_DBTIMESTAMP) : column

---- This is not working, its sytatically error. Are you sure of these ?

ISNULL(column) ? "NULL" : LEFT((DT_WSTR, 30) column, 11)

--- And are you sure there is String Function called LEFT, as I could find it. Tried to Ltrim, but its not working. It shows me same error "potential loss of data.

kramaswamy SSCoach Points: 18181 More actions · Answer 2

My bad. The LEFT function doesn't exist in SSIS. Use SUBSTRING instead:

ISNULL(column) ? "NULL" : SUBSTRING((DT_WSTR, 30) column, 1, 11)

As for the first one, it should work. I tried it on my end with a column that was DT_DBTIMESTAMP and it worked.

quillis131 Hall of Fame Points: 3706 More actions · Answer 3

quillis131 (10/18/2011)
kramaswamy (10/17/2011)
If you're using ISNULL in SSIS, then the way you need to use it for your purposes is something like:
ISNULL(column) ? (VALUE_IF_TRUE) : (VALUE_IF_FALSE)
So, for example,
ISNULL(column) ? "1950-01-01" : column
Since the value for both true and false need to evaluate to the same data type.
Hi kramaswamy... Its working finally Thank you, Probably I was missing double quotes.
I have another question with this .....
I tried ISNULL(column) ? "NULL" : column... this goes fine at Source but my mapped destination column says SMALLDATETIME data type, I made sure placed a DATA CONVERSION task b/w Derived column Transformation and Destination and convert Data type to smalldata time
But it Fails again with error message " The value could not be converted because of a potential loss of data."... I know this is something to data type coversion... can you help me in this please

I got the problem (partially) ... When I said above its working, I meant :

LEN(DATECOLUMN) == 0 ? " NULL" : [DATECOLUMN] working. Sorry again it lead to lot of confusion...any way thats the way we learn most of the stuff.

It doesn't make any sense in using ISNULL Function, because as the column value is empty in my case and not NULL.

When I was trying with ISNULL(column) ? "NULL" : column, my date column data after Derived Transformation is not shown as NULL, it just empty(blank fiels like my Source value.

I am interpreting it as this way, when there is any NULL word mentioned in the column value coming from source then in that case, we can make use of this ISNULL function to replace "NULL" with any date structure or anything else, in derived transformation.

Now this expression works like champ LEN(DATECOLUMN) == 0 ? " NULL" : [DATECOLUMN] . As it works good after derived transformation, but errors out just before the Destination as my destination column data type is smalldate time.

Now I need a conversion Transformation

As the expression is evaluated to string ( data column coming from source to derived transformation is STRING Data type), gave a thought to convert it to Dt_timestamp but its failing, tried even converting to Dt_date.

Plz give me any thoughts... and let me know if this doesn't make any sense.

But I have

quillis131 Hall of Fame Points: 3706 More actions · Answer 4

Finally got working ... thank you all

This sholud be the one i am trying from 2 days :

LEN(DATEcolumn) == 0 ? NULL(DT_DBDATE) : (DT_DBDATE)DATEcolumn

As my destination is smalldatetime the whole expression should be DT_DBDATE.