question

sajeshk-2707 avatar image
0 Votes"
sajeshk-2707 asked ZoeHui-MSFT commented

Flat file potential data loss issue using SQL server import wizard

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-generalsql-server-integration-services
sourcedata.png (7.0 KiB)
ecftest.txt (162 B)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @sajeshk-2707,

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



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Zoe,

Thank you for your kind response. I was tried method 1 before posting this issue, that also not works. I am trying to use the sql import wizard tool and i couldn't find an option to create a derived column in the import wizard. is there is any option available in the import wizard to create a derived column? This is one time activity hence not need to create package using SSIS.

Thanks.
Regards,
Sajesh

0 Votes 0 ·

Hi @sajeshk-2707,
If you are using sql import wizard tool, the above two methods are not suitable for you. You may try with Tom's resolution to load the data into a stage table and change the datatype with tsql. Also you may directly design the package with SSDT and then FastParse and derived column will also work.

0 Votes 0 ·

Hi @sajeshk-2707,
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered TomPhillips-1744 commented

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Tom,

Thank you for your reply, yes currently i am loading the data into a stage table and using tsql statement to merge the data.

Regards,
Sajesh.

0 Votes 0 ·

You need to change all your data types in SSIS and the stage table to strings.

0 Votes 0 ·