Excel Data Conversion of a General Data format to a Date

Question

So I have this column in an Excel spreadsheet that is a date but in Excel is a General format.

The Date appears as so...

8222022

And when we get to October the date expands to a 2-digit month...Ex...10182021

The current data conversion step has the following expression...

(DT_DBDATE)(SUBSTRING([Column 34],5,4) + "-" + SUBSTRING([Column 34],1,2) + "-" + SUBSTRING([Column 34],3,2))

Which does not seem to take into consideration the extra month digit.

This expression seems to have worked fine up to this point in time.

But today the processing of this file failed with the following error...

Started: 11:00:00 PM
Error: 2022-09-06 23:00:04.84 Code: 0xC0049064
Source: Data Flow Task Conversions [2] Description: An error occurred while attempting to perform a type cast.
End Error
Error: 2022-09-06 23:00:04.84 Code: 0xC0209029
Source: Data Flow Task Conversions [2]
Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "Conversions" failed because error code 0xC0049064 occurred, and the error row disposition on
"Conversions.Outputs[Derived Column Output].Columns[PickupDate]" 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.
End Error
Error: 2022-09-06 23:00:04.84 Code: 0xC0047022
Source: Data Flow Task SSIS.Pipeline
Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.
The ProcessInput method on component "Conversions" (2) failed with error code 0xC0209029 while processing input "Derived Column Input" (3).
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.
End Error
Error: 2022-09-06 23:00:04.85 Code: 0xC02020C4
Source: Data Flow Task Flat File Source [138]
Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
End Error
Error: 2022-09-06 23:00:04.85 Code: 0xC0047038
Source: Data Flow Task SSIS.Pipeline
Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC02020C4.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.
End Error
DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:00 PM Finished: 11:00:04 PM Elapsed: 4.063 seconds. The package execution failed. The step failed.

Can anyone help shed any light as to why all of a sudden this Data Conversion Expression seems to be failing when it has processed fine up to this point in time even with the one-digit month?

Any help would be GREATLY appreciated.

And Thanks in advance for your help.

Answer 1

Hi @Bobby P ,

The error is related with the length of your column.

Please check that the length of column34 in the excel is 7 or 8 only.

Then we can first use Derived Column to make the date column contains 8digits only. You could replace the column34 or rename the column lets say 'newcolumn' with below expression.

LEN([Column 34]) == 8 ? [Column 34] : ("0" + [Column 34])  

After that we could use the expression to convert data of the 'newcolumn'.

(DT_DBDATE)(SUBSTRING([new column],5,4) + "-" + SUBSTRING([new column],1,2) + "-" + SUBSTRING([new column],3,2))  

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.