it seems your field "DEA_EXP_DT_OUT" is defined as a string in the ports tab. You are trying to assign a date to that string. You need to change the port datatype from string to date/datetime.
change it from a variable port to an output port. Then you can port downstream
Nonetheless this won't help against issues if the target table doesn't allow NULL values.
So you need to know what is expected to be written to the target if the input value is a NULL value. Only when you know this, you will be able to extend the TO_DATE() function such that NULL values won't be written to the target table.
BTW what is the target system? SQL Server? Just a guess.
You are right- My next question to Scott was going to be the same. The target table is still not loading.
My expectation is:
When the date column is null for a record in source file , the target table date column should also show it as null for that record. If the date column is not null, then it should populate the target table will date from source but format should be mm/dd/yyyy.
Yes, the target table is on SQL Server.
Hold on, you're mixing up things if I understand correctly. That's not healthy. So please let me explain a few things.
One thing to keep in mind is the data type. The data type of an attribute in a database table tells what values can be stored. And the "internal" format of how these data values are stored is known only to the DBMS vendor. For example, a Date/Time in PowerCenter - as far as I know - is stored in a 64-bit integer; probably today would be a number somewhere in the 1.7 bn .
A completely different thing is how these data types are "represented", meaning how a character string looks like which can be interpreted by a human being. For example, on Unix/Linux systems the date/time value Jan 1st, 1970, at midnight is treated as the number 0; but for us human beings this number 0 is printed as "01/01/1970 00:00:00" (assuming a 24-hour format).
Again, the internal storage of this date is a 64-bit integer with a value of 0. Some software is responsible for "translating" this number into something that we can read (like "01/01/1970 00:00:00" or "Jan 1st, 1970, midnight") or to perform this conversion the other way round (namely to "translate" the date Jan 2nd, 1971, at 3:45 AM, into a number like 31,635,900).
Now SQL Server (and Sybase) can interpret a character string in a "suitable" format as a date/time value. However, this "suitable" format is derived from some system settings. For example, you probably work with a Windows computer set to work somewhere in the USA. And this so-called "localisation" (let's assume New York, meaning USA East Coast) has a couple of implications. For example, the default format for a date/time string is MM/DD/YY HH12:MI:SS AM (this is the format as it would be defined in PowerCenter for the TO_DATE() or TO_CHAR() function). So SQL Server expects that a date/time value be given in this format.
On the other hand, when printing a Date/Time value (e.g. when executing a SELECT statement in the SQL Server Management Studio), SQL Server uses this default format to print out a date/time value on the screen.
As an example, on a German PC SQL Server would print the sample date given above like this:
2. Jan. 1971, 03:45
Whereas on a PC located in the USA the DBMS would print this date:
Jan 2, 1971, 3:45 AM
And now comes the not-so-funny part.
It's a little difficult to "force" SQL Server to use a different date/time format than the system-defined default. It is possible (as far as I understand it, I've never tried it myself), but it probably needs some programming in Transact SQL.
What does that mean for you?
You should simply forward the date/time value coming from the source file to SQL Server as is. Don't try to "format" it, that doesn't make sense. PowerCenter hands over date/time ports to SQL Server in a format which gives the DATETIME (or DATETIME2 or whatever) attribute the correct value. You should - under normal conditions - NEVER try to hand over a date/time value to SQL Server as a string; if your date/time values are already available as Date/Time ports, then hand over these Date/Time ports from PowerCenter to SQL Server "as is".
Of course that also means (and that has nothing to do with PowerCenter, it's just how SQL Server works) that you cannot (easily) influence how a date/time value stored in SQL Server is presented to you on the screen, in a file, or whatever. So please don't try to influence that (except after having googled for some sample code to set up your own printable date/time format), that simply can't work.
I know this is long and not too easy to understand.
Short version: don't ever try to "format" a date/time into a string when the target is a DATE, DATETIME, or DATETIME2 in SQL Server. Hand over Date/Time ports as they are.