June 26, 2006 at 8:03 am
This is my first post, so please understand if my explanation is a bit obtuse.
I have finally figured out how to use the Import/Export Wizard to build SSIS packages to migrate an entire Sybase 12.5 database to SQL 2005. The database contains well over 600 tables, most of them quite small. If I define a package containing all the tables, when I run the package SQL very quickly saturates all user connections (on either the Sybase side or the SQL side, depending on which is set lower) and crashes the package. Since we have several almost identical databases to migrate, I'd like to know:
Is there any way to control the number of tables SQL 2005 will try to simultaneously convert? Is there a thottle on workload or number of connections that I could apply?
I have tried changing the DataReader parameter for simultaneous executables, but that did not seem to have any effect. I can break the migration into collections of c.50 tables, but that is time consuming (and would have to be done for each database since they are not quite identical) and could be error-prone for the non-DBAs that will be doing most of the migrations.
Thanks for your thoughts, Lee A.
June 26, 2006 at 6:09 pm
I run the risk of being wrong here since I don't know the internals of your package, but it sounds like you've set up a package to run all of the conversions in parallel, is that right? If that's the case, there are several ways to handle this:
1) The package itself has a "MaxConcurrentExecutables" property, try setting this. (you can reach this properties page by right clicking on the white space of the package and selecting properties, which will show up on the right)
2) Adjust your package flow. You can have SSIS tasks execute sequentially. I'm guessing the wizard-designed package will try to fire off everything at once. Try opening your package and setting up your tasks to happen one after another. When you click on a task, there's a unconnected green arrow that shows up below the task... click on the arrow and connect it to another task by clicking on the task.
Good luck and I hope I was of some help.
June 27, 2006 at 7:34 am
Thanks for your thoughts, but:
Re. 1. As I mentioned in my original post, I tried changing the MaxConcurrentExecutables but to no observable effect. It default value is -1 (which I interpretted as meaning unrestricted). I changed it to 2 and 5, but SSIS still scheduled more than 40 simultaneous table processes and crashed by exhausting the number of user connections. What do I need to do to cause the MaxConcurrentExecutables parameter to take effect?
Re. 2. The Wizard generates only one Data Flow task. Within the task on the Data Flow tab, it generates a two box step for each table. The only unconnected arrows on the two box steps are the red error processing ones. There is, of course, the one unconnected green arrow eminating from the Data Flow task, but I'm at a loss as to what task to connect it to that will limit the number of parallel table processes it tries to initiate. I'm certainly open to suggestions on how to prevent it from exhausting all user connections.
So, I'm still looking for ideas on how to limit the number of simultaneous process SSIS starts.
NOTE: I realize I can define multiple Data Flow tasks, each specifying c.50 tables to be processed, but the extra work (and potential errors) of defining 10 to 15 tasks for the 600+ table database is highly undersirable when I should be able to move the database with only one Data Flow (and, in fact, could under SQL 2000).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply