Which systems memory will be used by ssis package?

Question

I have few questions about the memory usage in ssis package.

1. If i am loading data from server A to Server B and the ssis package is in my desktop System and running through BIDS,Whether the buffer creation(memory usage) will happen in my desktop system?If this is the case,the performance(low memory compare to servers) will be slow right?

2. How to enable the usage of server resources while developing package in my desktop system?

3. Please help me, if i have 3 ssis developer and all are developing different packages at a time,What is the best development method?

Answer 1

To expand on #3, the best way I have found to allow teams to work on a single SSIS solution is to decompose a problem (package) down into smaller and smaller chunks and control their invocation through a parent-child/master-slave type relationship.

For example, the solution concerns loading the data warehouse. I'd maybe have 2 Controller packages, FactController.dtsx and DimensionController.dtsx. Their responsibility is to call the various packages that solve the need (loading facts or dimensions). Perhaps my DimensionProductLoader package is dealing with a snowflake (it needs to update the Product and the SubProduct table) so that gets decomposed into 2 packages.

The goal of all of this is to break the development process down into manageable chunks to avoid concurrent access to a single package. Merging the XML will not be a productive use of your time.

The only shared resource for all of this is the SSIS project file (dtproj) which is just an XML document enumerating the packages that compromise the project. Create an upfront skeleton project with well-named, blank packages and you can probably skip some of the initial pain surrounding folks trying to merge the project back into your repository. I find that one-off type of merges go much better, for TFS at least, than everyone checking their XML globs back in.