SSIS database connections

[Lee Adrian]

Using the Import/Export Wizard, it is easy to set up SSIS jobs to import multiple tables in one task. However, SSIS appears to grab a connection to the source database and the destination database for each table indicated in the task sometime prior to doing the actual data transfer. Even though the transfer seems to be done a few tables at a time, all the connections are held throughout the execution of the task.

This has two effects:

1. The task may fail if the number of connections exceeds the limit of user connections set for the database

2. There is a severe impact on the other users of the source database (in my case, a production system)

Is there any way to control the number of simultaneous connections that SSIS data transfer packages initiate?

I can, of course, define the package to have a small number of tables (thus limiting the connections), but in real life I need to transfer almost 700 tables, so limiting the number of tables per task to 20 or 25 produces an awkwardly large, and error prone, number of tasks. And since this definition must be done for multiple (not quite identical) databases, the extra effort of defining small collections of tables is signifcant.

[rmiao]

Did you check Microsoft sql2k5 newsgroup? May have answer there.

[Lee Adrian]

Which newsgroup do you mean? I have done searches with a variety of ways of describing this question through the SQL2k5 Help function (which also looks at the MS site) and through Google. I've not found anything directly applicable either place. Do you have a more specific reference that might help me?

[rmiao]

Checked this link? http://forums.microsoft.com/MSDN/Sho...ID=80&SiteID=1

[Lee Adrian]

Thanks for the reference, but I couldn't find anything directly applicable to my current dilemna . I did find a good article that might help me downstream, if I ever get past this hurdle.

[rmiao]

You can post your issue there, Microsoft support guys monitor that forum.

[Lee Adrian]

Thanks. I've taken your suggestion.