0

I am trying to cast a String (DT_WSTR) as a Date Format (DT_DBDATE) in SSIS.

My source is a Excel-File and the input field looks as follows: OCT 04 2020

My Result should have the format 2020-10-04

I use a Derived Column Editor and created this expression:

(DT_DBDATE)(SUBSTRING([BILLING CYCLE DATE],8,4) + "-" + SUBSTRING([BILLING CYCLE DATE],1,3) + "-" + SUBSTRING([BILLING CYCLE DATE],5,2))

The error I get is:

[Derived Column [312]] Error: An error occurred while attempting to perform a type cast.

[Derived Column [312]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "Derived Column" (312)" failed because error code 0xC0049064 occurred, and the error row disposition on "input column "BILLING CYCLE DATE" (388)" 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" (312) failed with error code 0xC0209029 while processing input "Derived Column Input" (313). 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.

I know that there are several questions that a similar, I also tried a lot of them but nothing helped me so far.

I have no Idea why it is not working as it used to work one month ago. And in other SSIS Packages similar expressions are working fine.

Any answer is appreciated!

ste92
  • 434
  • 9
  • 23

2 Answers2

0

I found the solution:

I had to transform the monthname OCT into a number 10. So tried this solution: Convert month name to month number in SSIS

(SUBSTRING([BILLING CYCLE DATE],1,3) == "OCT" ? "10" : "") + SUBSTRING([BILLING CYCLE DATE],4,3) + SUBSTRING([BILLING CYCLE DATE],7,5)

After that I did the above expression in a seperate Derived Column and it worked very fine!

(DT_DBDATE)(SUBSTRING([BILLING CYCLE DATE],7,4) + "-" + SUBSTRING([BILLING CYCLE DATE],1,2) + "-" + SUBSTRING([BILLING CYCLE DATE],4,2))

ste92
  • 434
  • 9
  • 23
0

You can use a script component transform:

DateTime.ParseExact(Row.stringDate, "MMM dd yyyy", CultureInfo.InvariantCulture);

The inputs are string to convert, format, and culture.

KeithL
  • 5,348
  • 3
  • 19
  • 25