Convert date stored as DT_STR to DT_DBDATETIMEOFFSET

Question

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.

In Text file

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

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?

Hadi · Accepted Answer · 2019-05-20T08:23:33.730

2

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

edited May 20 '19 at 08:23

answered May 19 '19 at 15:54

Hadi

36,233
13
65
124

I have used the DT_DBDATETIMEOFFSET named outdate in Derived column using as output in script component – mohan111 May 20 '19 at 05:56
How I need to get the outdate column into the script component – mohan111 May 20 '19 at 06:32
@mohan111 concerning ScanDateTime, forget about derived column just add a Script component, double click on it, in the `Inputs and Outputs`, expand the `Output 0` and click the `Output Columns`. Press `Add Column` to add an output column – Hadi May 20 '19 at 07:45
@mohan111 check the following link forca step by step tutorial https://www.sqlshack.com/ssis-script-component-vs-derived-column/ – Hadi May 20 '19 at 07:46
when i'm ruuning this i'm getting this Run time error : The UTC Offset of the local dateTime parameter does not match the offset argument. Parameter name: offset – mohan111 May 20 '19 at 07:59
@mohan111 this is weird since i tested it on my machine and it works. I will search for that and get back to you – Hadi May 20 '19 at 08:19
I will check n get back to you @hadi – mohan111 May 20 '19 at 08:45
1

It worked thanks for you support and guiding me on this – mohan111 May 20 '19 at 10:16

Convert date stored as DT_STR to DT_DBDATETIMEOFFSET

1 Answers1

Predicted delivery date Null handling

ScanDateTime offset handling

References