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:
BusinessID : [DT_TEXT]
NationalID : [DT_WSTR]
BirthDate : [DT_DBDATE]
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 "T*he 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.
