SSIS derived column error

Question

I got this error while debugging in SSIS:

Error: 0xC0049064 at Data Flow Task, Derived Column [70]: An error occurred while attempting to perform a type cast. Error: 0xC0209029 at Data Flow Task, Derived Column [70]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (70)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "EVENT_DT" (118)" 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. Error: 0xC0047022 at Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Derived Column" (70) 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. Error: 0xC0047021 at Data Flow Task: 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. Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "DataReaderDest" (143)" wrote 0 rows. Task failed: Data Flow Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure.

My expression is:

(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + 
 SUBSTRING(EVENT_D,4,2) + "-" + 
 SUBSTRING(EVENT_D,1,2) + EVENT_T)

My original data are in this sequence:

EVENT_D: DD/MM/YYYY
EVENT_T: HH:MM:SS

Any help are appreciated. I have try changing my expression numerous time but still fails.

Answer 1

I faced the same problem while converting a string containing date to datetimestamp field. It is all due to spaces in the field. So we need to check for space and then convert it to datetimestamp. Below is the expression for the same.

LEN(TRIM([Field1])) == 1 ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING([Field1],1,4) + "-" + SUBSTRING([Field1],5,2) + "-" + SUBSTRING([Field1],7,2) + " " + SUBSTRING([Field1],10,2) + ":" + SUBSTRING([Field1],12,2))

Answer 2

If you wanted to do this without a script, you could have just changed your expression from

(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + SUBSTRING(EVENT_D,4,2) + "-" + SUBSTRING(EVENT_D,1,2) + EVENT_T)

to

(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + SUBSTRING(EVENT_D,4,2) + "-" + SUBSTRING(EVENT_D,1,2) + " " + EVENT_T)

Not having the space between the date and the time portions is what was most likely causing your original error. You were getting

2012-04-3012:25:37

instead of

2012-04-30 12:25:37

Answer 3

I suspect that there are some date time values which are not in the correct format .So SSIS throws error while parsing them .

In order to find to incorrect date time value from your source table try to redirect the error rows from Derived Transformation and check the incorrect data using a data viewer

The problem with substring values are if the string data are not in the correct format then the Derived Transformation will throw error .

1. In your OLEDB source component try to write the sql and get only the correct datetime values

Ex:

 Select col1,
        case when isDate(EVENT_D) = 1 THEN EVENT_D ELSE NULL 
        END as [EVENT_D],
        Col2,EVENT_T,other columns
        from yourTable

in your derived transformations use your code to convert into DT_DBTIMESTAMP type .

1. Else Try to use a script component and parse the EVENT_D and EVENT_T values and convert to datetime values. No need to use Derived column with all those substring values

   create a New Output column Valid_D with the datatype as DT_DBTIMESTAMP.Select the 2 input columns EVENT_D and EVENT_T in the available input Columns in Script Transformation Editor

VB.NET code

    Dim result As DateTime

If DateTime.TryParseExact(Row.EVENT_D, "dd/MM/yyyy", 
                          Nothing, Globalization.DateTimeStyles.None, result) Then
     Row.ValidD = result.Add (TimeSpan .Parse (Row.EventT ) );
End If

C# code

DateTime result;
    if (DateTime.TryParseExact(Row.EventD, "dd/MM/yyyy",
        CultureInfo.InvariantCulture, DateTimeStyles.None,out result))
    {
         Row.ValidD = result.Add (TimeSpan .Parse (Row.EventT ) );
    }

Now you can use Valid_D column which is of DateTime type in your subsequent transformations

Update : The problem with your syntax is you cannot add date+ time in the string format . You need to parse individually both EVENT_D and EVENT_T .

(DT_DBTIMESTAMP)(SUBSTRING(EVENT_D,7,4) + "-" + 
 SUBSTRING(EVENT_D,4,2) + "-" + 
 SUBSTRING(EVENT_D,1,2) + EVENT_T)

So your syntax is not valid.

The isDate function shows NULL for 30/04/2012 because as per MSDN

   The return value of ISDATE depends on the settings set by
   SET DATEFORMAT, SET LANGUAGE and Configure the default language
   Server Configuration Option. 

but it returns 1 for the value 01/05/2012 and 02/05/2012 because it takes as MM/dd/YYYY so the 1st date is Jan 5th 2012 instead of May 1st 2012

So the soultion is use script component and parse these values into valid date and time and then use it in your subsequent transformations .

Please check my script transformation code above

Update 2:

I think your using SSIS 2005 .The code should be

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
  Dim result As DateTime
  If DateTime.TryParseExact(Row.EVENTD, "dd/MM/yyyy", Nothing,    Globalization.DateTimeStyles.None, result) Then
  Row.ValidD = result.Add(TimeSpan.Parse(Row.EVENTT))
  End If
  End Sub

after Script transformation ,you don't need to use Derived component .The result obtained in the column Valid_D contains the valid value which is of datetime format