Share via

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

ArunRaaman 996 Reputation points
2022-03-23T20:45:13.167+00:00

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:
186078-image.png

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:
186206-image.png

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

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,525 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 35,556 Reputation points
    2022-03-24T02:23:00.63+00:00

    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.

    186285-image.png

    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.

    0 comments