Importing Dates using derived column transformation

Hi All

I've got a flatfile which contains a column SALEDATE with this data

20070802

''

20070804

''

20070805

20070901

I've got a table with a column SALEDATE datetime.

I use a derived column with this expression

SALEDATE <add as new column> (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2)) database date [DT_DBDATE]

This expression fails whenever it comes across a blank field.

How can evaluate my expression to insert a null if it comes across a blank field?

Thank You

SaleDate == "" || ISNULL(SaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(SaleDate,1,4) + "-" + SUBSTRING(SaleDate,5,2) + "-" + SUBSTRING(SaleDate,7,2))|||

Phil thank you for replying.

There are some records that don't even have anything for SALEDATE

20070801

''

20070802

20070901

20070902

The package fails at the row 627 when the empty field comes across.

How should i modify the expression?

|||The package doesn't fail because of the expression -- check your logs -- it likely fails because of an invalid row (missing delimiter?). SSIS expects the metadata to be the same across the whole file.|||You're right

627 is the last row in the flatfile. it fails on that row.

I checked the file the last record contains data 20070831

We get flatfiles in a RaggedRight format.

There are no text qualifiers and Header row delimiter is set to {CR}{LF}

Here is the error log:

[Transform Columns [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Transform Columns" (1)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SALEDATE" (4334)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transform Columns" (1) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

[DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.

I'll try different options and see what i can come up with.

Thanks for you help.

|||Sounds like a short row and it is missing its row terminator|||

I can't seem to get any rows into a table.

Still getting the same error.

I tried changing the (DT_DBDATE) to (DT_STR) but it turns red.

OnSaleDate == "" || ISNULL(OnSaleDate) ? NULL(DT_DBDATE) : (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "/" + SUBSTRING(OnSaleDate,5,2) + "/" + SUBSTRING(OnSaleDate,7,2))

Did anyone come across this problem?

|||Please share all of your errors.

There's nothing wrong with that expression, syntactically.|||

I got the same problem and I fixed cleaning garbage data in the column that caused the error. Ex I expected a number and I got a "-" character.

Good luck

|||

Here is how i solved it.

LEN(TRIM(OnSaleDate)) > 1 ? (DT_DBDATE)(SUBSTRING(OnSaleDate,1,4) + "-" + SUBSTRING(OnSaleDate,5,2) + "-" + SUBSTRING(OnSaleDate,7,2)) : NULL(DT_DATE)