I've been working with SSIS for several years now, but I still don't have a good concept about how it uses memory. Here's our scenario: We have two Servers (Windows Server 2008 R2), both with SQL Server 2008 Standard installed, and both with SSIS installed. Server A is what we call our "ETL" server, which we initially planned to install all SSIS packages for consistency. Server B is a data warehouse which has data extracted via SSRS and SSIS from various other servers.
My question is: If I have an SSIS package that creates an XML file using data on Server B (data warehouse), how would memory be utilized by installing the package on server A and having it pull data from Server B, versus installing directly on server B?
The actual package uses OLE DB Sources (inside data flow tasks) which call stored procedures on Server B. The data sets are returned into objects variables, then looped through by a For Each Loop, and each record is acted on individually buy C# scripts, which write the data to a file.
I realize this may be a broad/vague question, so if any more detail is needed, please let me know.