Best Practices Question

Environment is SQL Server 2005 x64 Enterprise running under Windows Server 2003 x64 Enterprise with four processors and 16GB of ram.

I have 28 data copy routines I would like to add to a SSIS package. They use the Data Reader Source to an ODBC database (InterSystems Cache) and copy the table contents to a SQL2005 database for reporting needs. The data rows in these 28 routines range from only 100 rows to over 6 million rows depending on the table. I have tested these individually and they work fine. My question is, is it a good practice to have all of these routines in a single package or can I expect performance degragation?

A single package is fine, but multiple packages are more supportable.|||

Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?

|||

windthorstking wrote:

Thanks Phil. I should note that in this package, all 28 copy routines are in the same data flow object. Would it be better to have them in separate data flow objects? Also, when you say more supportable, are you referring to making it easier to troubleshoot?

I'd probably put the distinct "copy routines" into their own data flows, at the very least.

And yes, I mean it will be easier to troubleshoot.sql