SSIS error in data flow task import from excel to SQL server 2012.

Question

got many error in errorlog when executing data task to import data into SQL server database from excel file source. anyone know how to resolve?

---

error 1.
SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Excel Source returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

error 2.
SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Excel Source.Outputs[Excel Source Output].Columns[Exchange Rate]" failed because error code 0xC0209072 occurred, and the error row disposition on "Excel Source.Outputs[Excel Source Output].Columns[Exchange Rate]" 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 3.
There was an error with Excel Source.Outputs[Excel Source Output].Columns[Exchange Rate] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".

Answer 1

The value could not be converted because of a potential loss of data.".

You got a nearly clear error message.
You are trying to import large data then allowed by the data taregt, e.g. the target is of type varchar(50) and the source data is for 100 chars = loss of data.

Answer 2

Hi @nononame2021 ,

There was an error with Excel Source.Outputs[Excel Source Output].Columns[Exchange Rate] on Excel Source.Outputs[Excel Source Output]. The column status returned was: "The value could not be converted because of a potential loss of data.".

Usually the issue will be related with a mismatch of data types in the export/import columns.

First thing to do is check the column by right click on source or destination, selecting Advanced Editor. Go to Input and Output Properties and check the data type is match with the source data.

Also refer to below link:
sql-server-fix-error-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

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.

Answer 3

when i click the "Excel source" in data flow task
and then click the "Preview" button, it show message as below:

[Excel Source [232]]: Opening a rowset for "Worksheet$" failed. Check that the object exists in database.

any ideas?

Answer 4

i also got this error when click "Excel source" object in data flow task, what is wrong and how to resolve it?

Answer 5

Hi @nononame2021 ,

That message usually means validation has failed;

Have any of the underlying tables changed? Data types, lengths, maybe even column ordering, etc?

Right-click on the Data Flow Task and select Properties. Then set DelayValidation =TRUE.

If not, please try creating a new data flow task for the same.

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.