data conversion error with date column when selected database-date-datatype for source and destination

Question

Hello There,

I configured a data-flow-task within foreach loop container to loop-through flat-files in a folder; the data types contained in the source-flat-file are of type string and date and within the flat-file connection manager (to import flat-file source data), I set the data-type as follow:

1. BusinessID : [DT_TEXT]
2. NationalID : [DT_WSTR]
3. BirthDate : [DT_DBDATE]
4. MarriageStatus : [DT_STR]

over for flat-file source, I made the following configuration -- with advanced editor>Input and output properties > flat file source Output:
External Columns > Birth date (column): database date [DT_DBDATE]
Output Columns > Birth date (column): database date [DT_DBDATE]

-- to match the data types as configured using flat-file-connection manager (to import source data), especially for BirthDate (column) at destination assistant, the data type is : [DT_DBDATE].

Destination table metadata:

after all the configuration (and with some changes here with date-data type), error with date-datatype persist as follows:

Error: 0xC02020A1 at DFT_Employee, Employee_Flat File Source 2: Data conversion failed.
The data conversion for column "BirthDate" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Error: 0xC0209029 at DFT_Employee, Employee_Flat File Source 2: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Employee_Flat File Source.Outputs[Flat File Source Output].Columns[BirthDate]" failed because error code 0xC0209084 occurred, and the error row disposition on "Employee_Flat File Source.Outputs[Flat File Source Output].Columns[BirthDate]" 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.

Error: 0xC0202092 at DFT_Employee, Employee_Flat File Source 2: An error occurred while processing file "E:\Dell-Desktop\PowerBI\SSIS-Work\SSIS_FlatFiles-RawFile\Load-MultipleFiles-To-MultipeTables\MultipleFiles_DifferentNames**\EmployeeOneHundred.txt" on data row 1.**

Below is Sample data screenshot of source file:

Would anyone please help me understand where I am going wrong? Thank you for giving your valuable time.

Answer 1

Hi @ArunRaaman ,

I did some test locally that I could not reproduce the issue.

However, when I mistype the BirthDate with value "2011--02-20", I got the same issue as yours.

Suppose that the BirthDate column also has the incorrect format value.

You may redirect the error rows and enable dataviewer to check the value.

using-ssis-error-output

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.