Flat file potential data loss issue using SQL server import wizard

sajesh k 1 Reputation point
2021-10-19T05:41:09.903+00:00

Dear,

I am getting an unweird issue with flat file import in SQL server. I have a csv with 6 columns ,one of the field name is "impactdate" in YYYYMMDD format in csv and in the target table the column mapped for this field is configured as date. But i am getting the potential data loss error and not sure why i am getting this error message even the source data is in correct.

Attached table structure, sample file and impact date source level configuration

Messages
Information 0x402090de: Data Flow Task 1: The total number of data rows processed for file "\hq-ssis-ua-a1\statpro-shared\DATA\MFS\ecftest.ecf" is 3.
(SQL Server Import and Export Wizard)

Error 0xc02020c5: Data Flow Task 1: Data conversion failed while converting column "Impact Date" (18) to column "Impact Date" (66). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Impact Date]" failed because error code 0xC020907F141554-tablestructure.txt141616-sourcedata.png occurred, and the error row disposition on "Data Conversion 0 - 0.Outputs[Data Conversion Output].Columns[Impact Date]" 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.
(SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion 0 - 0" (58) failed with error code 0xC0209029 while processing input "Data Conversion Input" (59). 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.
(SQL Server Import and Export Wizard)

Thanks.
Regards,
Sajesh.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-19T19:58:06.097+00:00

    You will be MUCH happier if you always import into a "staging" table with all fields into varchar fields. Then merge the data into the target using TSQL commands.

    You have very limited control and error messaging on data type conversion issues in SSIS.

    2 people found this answer helpful.
  2. ZoeHui-MSFT 32,426 Reputation points
    2021-10-19T09:40:24.05+00:00

    Hi @sajesh k ,

    The issue is related with the datetype of Impact Date.

    You may try with below methods.

    Method1: Flat File Source-->Right click, Show Advanced Editor-->Go to Input and Output Properties
    --> Output and click on the Impact Date column-->Choose the datatype with [DT_DBDATE]-->Set FastParse to True in the Custom Properties-->Map to the OLEDB Destination

    141946-screenshot-2021-10-20-094351.jpg

    Method2: Add Derived Column-->Replace the Impact Date or add a new column with expression 

    (DT_DBDATE)(SUBSTRING([Impact Date],1,4) + "-" + SUBSTRING([Impact Date],5,2) + "-" + SUBSTRING([Impact Date],7,2))

    -->Map to the OLEDB Destination

    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.
    Hot issues October