-2

I am trying to transfer 67,714,854 rows from MySQL to SQL Server using SSIS. The package times out after transferring 14,282,990 rows. I changed the time out property to 0 also, but that didn't help.

How do I resolve this issue?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
user779882
  • 1
  • 1
  • 1

3 Answers3

2

I found a hacky solution to it. And that is having a limit at the end of your query. I was facing the same problem with ADO .NET connection to connect to MySQL. Although it doesn't solve the problem. It atleast get the work done.

SSIS: 2208 R2. MySQL: 5.0

Debpriya Seal
  • 73
  • 1
  • 10
  • I had a similar issue where my query would return 0 rows (should have been around 39,000). Adding a `LIMIT 10000000` made it work. Wish I knew why. – Marcus Oct 28 '15 at 16:06
1

On your OLE DB Destination connection, what "Data access mode" have you selected. If you have selected "Table or view - fast load" (this is the default), then there will be a "Maximum insert commit size" specified. You can try one of two things: 1) change the commit size to a larger number; or 2) try the other data access mode "Table or vew". Since you're getting a timeout error, I suspect that option 1 may not help (since you're already getting a timeout with a smaller value), so try option 2. Although that will likely result in slower performance, it may actually complete successfully. (You could then try @Siva's approach and split the output across multiple destinations to improve performance).

(Note: I'm referring to what's available in SQL Server 2008 R2, if you're using previous versions, it may be slightly different)

If none of the above work, you could also try to create a new SSIS package from scratch by running the SQL Server Import Wizard (right-click on your database in SQL Server Management Studio and select Tasks/Import Data. Follow the wizard screens and near the end make sure you check the box to Save the SSIS package, and choose a file location to save it to. Typically, the resulting SSIS package will be a functional package (and then you can also make whatever further modifications you like to it).

Joe L.
  • 1,888
  • 12
  • 14
0

Does MySQL give you the error or are you using PHP (or another language) to transfer the data and does that timeout? In the case of the latter, in PHP you can set the script timeout to infinite using this:

set_time_limit(0);

Either way, based on the information given, I'm not sure what type of database it is, but typically I would set up a cron script to transfer the data bit by bit in order to keep the load at an acceptable level. Please give more information...

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Battle_707
  • 708
  • 5
  • 15
  • my source is mysql and my destination is sql server.i have used selct all colums from table in oledb souce transformation.CAn you breif me out how and what is cron script thanks for your responce – user779882 Jun 01 '11 at 19:37
  • Well, if you're staying with SQL, you can't write a cron script. A cron script is basically a script that you let the system automatically run at set intervals. This will allow you to do move data over bit by bit. However, this requires a separate language, like php, as it needs to track the last row that was moved. the cron script would run the same code with an additional WHERE clause indicating id (/ row) range. You can obviously do it manually, but that can be tedious. Either add __WHERE id >= n1 AND id < n2__ or use the limit function: __LIMIT 100000, 100000__. – Battle_707 Jun 02 '11 at 00:47