Convert date stored as DT_STR to DT_DBDATETIMEOFFSET

3
votes

I have Delimited text file where I'm loading into Database table How can I load DT_STR col to DT_DBDATE ,DT_STR to DT_DATETIME.

enter image description here

In Text file

COL1 : Predicted delivery date : DT_STR
COL2  : ScanDateTime : DT_STR

enter image description here

In Destination Table :

COL1 : Predicted delivery date : DATE  (DataType)
COL2  : ScanDateTime : DATETIME

and I need to load data in this below format

Switchoffset (Substring(ScanDateTime , 1, 22)+':'+Substring(ScanDateTime , 23, 24),'-05:00')

I have trying to load this data into destination table using Derived column :

I gave this expression for

COL 1:

(DT_DBDATE)LEFT([Predicted Delivery Date],10)

COL 2:

(DT_DATE)(SUBSTRING([ScanDateTime ],1,4) + "-" + SUBSTRING([ScanDateTime ],5,2) + "-" + SUBSTRING([ScanDateTime ],7,2),'-05:00')

But both are giving error :

[Derived Column 2] Error: An error occurred while attempting to perform a type cast. [Derived Column 2] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column.Outputs[Derived Column Output].Columns[Predicted]" 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.

For Col1, how I need to handle Null values and Load the data into Destination table which is Date Format?

For Col2, how I can write an expression for Switchoffset (exp) - 5hrs for that date time column and load?

1
sqlsql-serverdatetimessisetl

1 Answers

2
votes

Predicted delivery date Null handling

You can add a derived column with the following expression:

(ISNULL([Predicted delivery date]) || [Predicted delivery date] == "") ? 
NULL(DT_DATE) : 
(DT_DATE)[Predicted delivery date]

ScanDateTime offset handling

You can solve the problem by adding a Script Component trasnformation, select ScanDateTime as input column and add a new column of type DT_DBDATETIMEOFFSET named outDate, inside the script use the following code:

public override void Input0_ProcessInputRow(Input0Buffer Row) 
{ 
    if (!Row.ScanDateTime_IsNull && !String.IsNullOrEmpty(Row.ScanDateTime)){

        DateTime dtDate = DateTime.Parse(Row.ScanDateTime);
        dtDate = DateTime.SpecifyKind(dtDate, DateTimeKind.Unspecified);
        DateTimeOffset offDate = new DateTimeOffset(dtDate,
                               TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time").GetUtcOffset(dtDate));

        Row.outDate = offDate;

    }else{

        Row.outDate_IsNull = True;

    }
}

References