Data Conversion Error on Excel Destination

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Did you set the error output of the OLE Destination to "Redirect Rows"?

You might also have metadata problems and could maybe stand to recreate the Excel destination.|||

I have set the error ouput to redirect rows

Actually I have a Data flow task with Oledbsource -->Oledbdestination (Redirect Error Rows ) -- >Data Conversion -- >Excel Destination.

When I do this its gives me the following error.

Data conversion failed while converting column "A" (53) to column "A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABL

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

But for this data flow task Oledbsource -- > Data Conversion - > Excel Destination. Its works fine.

I am doing the same data conversion in both data flow tasks.

Please let me know what I am doing wrong.

|||Well, I suppose check and double check your column mappings going into and out of the OLE DB destination.|||

prg wrote:

I am inserting rows using OLEDBDestination and want to redirect all error rows to EXCEL Destination.

I have used Data Conversion Transformation to Convert all strings to Unicode string fields before sending it to Excel Destination.

But its gives the following error.

[Data Conversion [16]] Error: Data conversion failed while converting column 'A' (53) to column "Copy of A" (95). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

[Data Conversion [16]] Error: The "output column "Copy of A" (95)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Copy of A" (95)" specifies failure on error. An error occurred on the specified object of the specified component.

Can someone please tell me what should I do to make it work?

Thanks,

Actually I would check the Data conversion transform. Specifically the column A; since is there where the error is being generated. Try placing a data view to inspected the values that go into the data conversion. What happens if you delete column A from the data conversion? Does it fail in a different column?

|||

I have checked the Data conversion transform again. I have removed column A and used another column. Still it fails.

This is kind of weird. I cant figure out where the problem is .

Is it that EXCEL Destination cannot be used to redirect rows that have errors? Because its seems to work perfectly when I do OLEDBSource -> Data Conversion ->Excel Destination.

|||Well, if the errors are created (redirected) because of conversion errors, then it is natural that the redirected error rows don't match your destination data types. They can't because that's why they are in error.

Make sure that the data types of the Excel destination match the data types of your SOURCE data, not the data types of the OLE DB Destination table. Not sure if this will work because the metadata probably won't match...|||

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. 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.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

|||

Ayzan wrote:

Hi all,

I think I have exactly the same problem using a sql destination for error output.

Error: 0xC02020C5 at GS_ORDREC, Data Conversion [1827]: Data conversion failed while converting column "OL_DONORD" (102) to column "Test" (1880). The conversion returned status value 8 and status text "DBSTATUS_UNAVAILABLE".

Error: 0xC0209029 at GS_ORDREC, Data Conversion [1827]: The "output column "Test" (1880)" failed because error code 0xC020908E occurred, and the error row disposition on "output column "Test" (1880)" specifies failure on error. An error occurred on the specified object of the specified component.

Error: 0xC0047022 at GS_ORDREC, DTS.Pipeline: The ProcessInput method on component "Data Conversion" (1827) failed with error code 0xC0209029. 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.

SSIS package "GS_ODSREC.dtsx" finished: Failure.

-

I check and recheck the package, everything is ok. Strange thing, when I try to convert the "ErrorColumn" field, the package works fine.

Regards

Ayzan

Okay, you *don't* have the same problem as the OP. If you are using a SQL Server destiantion, SSIS will not automatically cast/convert datatypes for you. The data flow datatypes (metadata) will need to match EXACTLY with the SQL Server destination table.|||

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

|||

Ayzan wrote:

Thank you Phil Brammer,

Sorry this IS the same problem, cause I try with a Sql server destination, a data reader destination, a flat file destination, for redirecting the failed rows, and I have still the same message ... in spite of casting/converting datatypes

Regards

Ayzan

Ah, see, you left out valuable information! So I'll ask this as I have done before. Before we continue, please provide the metadata information as it stands before going into the destination. Then, please provide the table column information for the destination as well.

One other thing to note is that there could be one bad row that is causing this error... Have you redirected errors and inspected them?|||

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

|||

Ayzan wrote:

Ok,

There is a simple Data Flow, so as to reproduce the error:

#Data Source, OLEDB, AdventureWorksDW

#OLE DB Source, SQL Command :

SELECT TOP (10) TimeKey, OrganizationKey, DepartmentGroupKey, ScenarioKey, AccountKey+100 as AccountKey, Amount
FROM FactFinance

#OLE DB Destination

AdventureWorks.FactFinance

#Error Output to Data Conersion

TimeKey -> DT_STR

#DataReader Destination

Enjoy !

I don't have a AdventureWorks.FactFinance table. I have the data warehouse table... Are you creating your own FactFinance table in the AdventureWorks table? The mere prefix of "Fact" indicates that it should be in AdventureWorksDW.|||

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

|||

Ayzan wrote:

Sorry,

#OLE DB Destination

AdventureWorksDW.FactFinance

Well in fact, whatever the source/destination, you just have to generate an error while trying to insert new records.

Regards

Ayzan

I'm utterly confused now. So your source and destination tables are the same? What are you doing exactly, and what are you expecting to see? Why would I select the top 10 records from FactFinance and then turn around and insert them again? Regardless, nowhere should a DT_STR datatype be picked up on the Timekey field because it is an integer field.

Nevermind. I understand now. Hang on.