How to formulate conditional expression in SSIS Data Flow tab?

Question

I am developing an SSIS 2008 package and I am trying to create a Derived Column transformation. But when I go to the Expression editor and try this expression it gives me alot of errors. I have tried various differentiations of this but all have resulted in errors. I need one of you SQL experts to point out a better expression!

ISNULL(WITHDRAWAL_DATE)||TRIM(WITHDRAWAL_DATE)==""?NULL:CAST(WITHDRAWAL_DATE AS DATETIME)

So I want this WITHDRAWAL_DATE input String datatype to be compared to an empty string--if it is empty I want it to become Null, otherwise to be cast as a date.

Thanks guys for your helps. I am so confused! WITHDRAWAL_DATE is a DATE data type input in the source XML file and now I have it as a STRING data type in my XSD file. Ultimately the problem is that some of the Withdrawal_Date fields in my XML source data are empty. So I want to insert Null values into my database for these records.

What data type do I need to specify in my XSD, XLST, and SQL output table? And it doesn't matter to me if I use Data Conversion task or Derived Column Xform but since I am new to these, could you send me expression syntax?

@BobS: when I ran with your updated solution, I received error: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

So when I googled datetime2 datatype it looks like this is a new data type that supports larger time/date fields. So I modified my SQL table to use DATETIME2 instead for this field and modified your cast expression below to use DATETIME2 but then output of transform didn't change accordingly.
I also tried changing WITHDRAWAL_DATE to datetime for all files and then changing SQL Table to say NOT NULL for this field. But this also gave me errors.

Answer 1

I see a couple of issues that you should address with this Derived Column transformation.

First, you can't change the data type of a column, which is what your expression is trying to do. I'm not sure if you're actually trying to do this. But, if your output column is the same as the input column, then you will have to change it. To do this, in the Derived Column editor, the Derived Column Name should be a new column name and the Derived Column should be <add as new column>

The expression needs two changes. To assign a NULL value you use a null function with syntax NULL(data-type). And, the CAST function syntax is (DT_datatype)columnname. So, here's how your expression should look

ISNULL(WITHDRAWAL_DATE) || TRIM(WITHDRAWAL_DATE) == "" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)WITHDRAWAL_DATE

UPDATE: You should be able to use the expression above; but, I did change it to reference the DT_DBTIMESTAMP data type. The SSIS DT_DBTIMESTAMP data type matches the DATETIME SQL Server data type.

To learn what data type you should use for the source component, you can right-click on the source component and select Show Advanced Editor... Select the inputs and outputs tab. Navigate the tree view to find your column and view the associated data type. The Advanced Editor is available most (maybe all) data flow components.

UPDATE 2: IF your output data type for the Derived Column component is DT_DBTIMESTAMP2 instead of DT_DBTIMESTAMP, make sure you change both DT_DBTIMESTAMP references in your expresson. Before closing the Derived Column component, look at the Data Type column for your expression. You can't change it, but it will show the data type that the expression output will be. If it's not what you want, then there's still a problem with your expression.

For, the source files, you can't change the data type of the external columns. At least, I haven't been able to do it. In SSIS, you have to work with what is interpreted by the Source component. If you can alter the files, to change data type, then great. Then, use the Derived Column component to convert what is giving to what you need.

Answer 2

What errors are you getting? I suspect one of them is a cast error.