CODEWITHSUNDEEP
ssis
Asynchronous
Asynchronous

Reputation: 3977

SSIS Derived Column: How to check if valid Timestamp and insert null?

I have the following time stamp in a text file:

3/3/2008 15:21:33

Using SSIS Derived Column, I am trying to check if the TimeStamp is in correct format. Then convert it to this:

2008-03-03 15:21:33.000

I am getting the following error:

 Data conversion failed. The data conversion for column "TIME_STAMP" 
 returned status value 2 and status text "The value could not be 
 converted because of a potential loss of data.

I want to insert Null if the conversion failed or if the time stamp is invalid.

I tried this but it is not working.

(LEN(TRIM(TIME_STAMP)) < 23) || (TRIM(TIME_STAMP) == "") 
|| (ISNULL(TIME_STAMP)) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)TIME_STAMP

How do I insert null if the timestamp fail conversion and convert only when it is in valid format?

Update: Additional Error Message, LEN and TRIM are not supported for the data type (DT_DBTIMESTAMP)

Thanks

Upvotes: 0

Views: 4359

Answers (1)

gannaway
gannaway

Reputation: 1882

SSIS Expressions don't support type checking or exception handling, so you are limited in those regards. The expression below will take your string and put it in the format you want, however, your package will fail if your TIME_STAMP column can't be cast to a DT_DATE. For example, if the value in text file were 3/3/2008T15:21:33, it would fail.

(DT_WSTR, 23) 
( 
    (LEN(TRIM(TIME_STAMP )) < 17) || (TRIM(TIME_STAMP ) == "") 
    || (ISNULL(TIME_STAMP )) ? NULL(DT_DBTIMESTAMP2, 3) : (DT_DBTIMESTAMP2, 3)           
    (DT_DATE)TIME_STAMP 
)

You could always write a custom Script Component Transformation in C# to do this type of validation/conversion. The main bit of code may look something like this:

/// <summary>
/// This method is called once for every row that passes through the component from Input0.
///
/// Example of reading a value from a column in the the row:
///  string zipCode = Row.ZipCode
///
/// Example of writing a value to a column in the row:
///  Row.ZipCode = zipCode
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (!Row.DerivedColumn1_IsNull)
    {
        DateTime dt;

        if (DateTime.TryParse(Row.DerivedColumn1, out dt))
        {
            Row.DerivedColumn1 = dt.ToString("yyyy-MM-dd HH:mm:ss.fff");
        }
        else
        {
            Row.DerivedColumn1_IsNull = true;
        }
    }
}

Upvotes: 1

Related Questions