asked on

SSIS 2008 Derived Column Date Conversion

I have an SSIS conversion and am trying to convert dates from an Oracle datasource that are formated YYYYMMDD HH:MM:SS.00 I worked through the Unicode to DT_STR conversion but now can't seem to handle NULLs in the data.

My first derived column replaces the DetailActivityStartDate column which is string (DT_STR) using the following expression:

(DetailActivityStartDate == "") || (ISNULL(DetailActivityStartDate)) ? "" : SUBSTRING(DetailActivityStartDate,1,4) + "-" + SUBSTRING(DetailActivityStartDate,5,2) + "-" + SUBSTRING(DetailActivityStartDate,7,2) + " " + SUBSTRING(DetailActivityStartDate,10,2) + ":" + SUBSTRING(DetailActivityStartDate,12,2) + ":" + SUBSTRING(DetailActivityStartDate,14,2)

Then I have added another Derived Column task because I was getting errors from null values when trying to just convert to database timestamp (the destination is a datetime column in SQL 2008.) Here is the expression (that is failing) of the Derived Column task that also converts the string from the task above, to database timestamp:

(DT_DetailActivityStartDate == "") || (ISNULL(DT_DetailActivityStartDate)) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)DT_DetailActivityStartDate

The error I am getting are:

[Derived Column - Date Nulls [1513]] Error: The conditional operation failed.

[Derived Column - Date Nulls [1513]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column - Date Nulls" (1513)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "DetAct Derived Column 3" (2348)" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column - Date Nulls" (1513) failed with error code 0xC0209029 while processing input "Derived Column Input" (1514). 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.

Alpesh Patel

Take first Derived columns to convert Null to blank.

and Put condition to check date is blank only.

(DetailActivityStartDate == "") ? "" : SUBSTRING(DetailActivityStartDate,1,4) + "-" + SUBSTRING(DetailActivityStartDate,6,2) + "-" + SUBSTRING(DetailActivityStartDate,9,2) + " " + SUBSTRING(DetailActivityStartDate,10,2) + ":" + SUBSTRING(DetailActivityStartDate,12,2) + ":" + SUBSTRING(DetailActivityStartDate,14,2)

Pleace proper starting point in above example

I am doing same

richxyz

ASKER

I tried your suggestion, and had to add a Data Conversion task afterwards to convert from string to database timestamp (DT_DBTIMESTAMP) - but the date column that contains blanks cause the error below. I added a viewer and the dates are properly being transformed from "20110301 080600" to "2011-03-01 08:06:00" but the next step when I try to do data conversion to datetimestamp, I get:

[Data Conversion [5273]] Error: Data conversion failed while converting column "DischargeDate" (167) to column "Copy of DischargeDate" (5287). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

ASKER CERTIFIED SOLUTION

richxyz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

richxyz

ASKER

I figured it out