SQL Server Integration Services Derived Columns

Question

I have SSIS Package Derived columns where I extracted the date from the source csv file.

Sample source file:

AddFi_1003AddlInfo_Incremental_202106300100.csv

In my derived column called "FileNameYYYYMMDD", I have (DT_I8)LEFT(RIGHT(@[User::FilePath],16),8) - 1

I extracted the date part from the source csv file.

I go back 16 characters from right side of the source csv file and I get 20210630. I then subtracted 1 from 20210630 and I got 20210629.

I have another derived column "Derived Column Filename YYYY-MM-DD" to turn value 20210629 into actual date format "2021-06-29 00:00:00.000".

It is working as expected.

But, when I processed the month-end source file

Month-end sample source file:

AddFi_1003AddlInfo_Incremental_202107010100.csv

If I go back 16 characters from right side of the source file and I get 20210701. I then subtracted 1 from 20210710

The expectation when processing the month-end source file is to go back one day. The expectation for this sample month-end file is 20210630.

Unfornately I received the following error message:

[Derived Column Filename YYYY-MM-DD [76]] Error: An error occurred while attempting to perform a type cast.
[Derived Column Filename YYYY-MM-DD [76]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Derived Column Filename YYYY-MM-DD" failed because error code 0xC0049064 occurred, and the error row disposition on "Derived Column Filename YYYY-MM-DD.Outputs[Derived Column Output].Columns[FilenameYYYY-MM-DD]" 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 Filename YYYY-MM-DD" (76) failed with error code 0xC0209029 while processing input "Derived Column Input" (77). 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.

Answer 1

Hi @Villa ,

You shouldn't be subtracting -1 from the DT_I8 data type.

The proper way is to do the following:

1. Convert DT_I8 into DT_DBTIMESTAMP data type.
2. Call DATEADD() built-in function to subtract -1.

Answer 2

Thank you @Yitzhak Khabinsky