2

I have an SSIS package set up to export data from a SQL Server 2008 R2 table to a MySQL version of that table. The package executes however, I am getting about 1% of the rows failing to be exported.

My source connection uses the SQL statement

SELECT * FROM Table1

all of the columns are integers. An example of a row which is exported successfully is

2169,2680, 3532,NULL, 2169

compared to a row which fails

2168,2679,3532,NULL, 2168

virtually nothing different that I can ascertain.

Notably, if I change the source query to only attempt the transfer of a single failing row - ie.

SELECT * FROM Table1 WHERE ID = 2168

then the record is exported fine - it is only when part of a select which returns multiple rows that it fails. The same rows fail the export each time. I have redirected error rows to a text file which displays a -1071610801 error for the failing rows. This would apparently translate to:-

DTS_E_ADODESTERRORUPDATEROW: "An error has occurred while sending this row to destination data source."

which doesn't really add a great deal to my understanding of the issue!

I am wondering if there is a locking issue or something preventing given rows from being fetched or inserted correctly but if anyone has any ideas or suggestions on what might be causing this or even better how to go about resolving it they would be greatly appreciated. I am currently at a total loss...

user1025443
  • 21
  • 2
  • Is there mybe unique key at the destination table and there exists duplicated value in the source? – Matej Jan 04 '12 at 22:39
  • Another idea is to do batching with `For Each` container, counter variable and source sql as expression? Ask for help if you have issues with that. – Matej Jan 04 '12 at 22:43
  • No unique keys - the same row will export multiple times if exported on its own just not when pushed out with other rows in the select. Will give batches a go. – user1025443 Jan 05 '12 at 11:19

2 Answers2

0

Try to setup longer timeout (1 day) ot the mysql (ADO.NET) destination.

Matej
  • 7,517
  • 2
  • 36
  • 45
  • No joy I'm afraid - tried setting both a high and then an infinite command timeout. In fact the row fails when I try exporting it with a single other but is fine when exported alone. Bizarre. – user1025443 Jan 04 '12 at 16:25
0

Well after much head scratching and attempting every work around that I could come up with I have finally found a solution for this.

In the end I switched out the MySQL connector for a different driver produced by devArt -dotConnect for MySql and, with a few minor exceptions (which I think I can resolve) all of my data is now exporting without error.

The driver is a paid for product unfortunately but in the end I'd have taken out a new mortgage to see all those tasks go green!

user1025443
  • 21
  • 2