Determining How Memory is Used in Two-Server Setup

Last Post 26 Oct 2011 10:44 AM by gunneyk. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Clint Spann
New Member
New Member

26 Oct 2011 08:49 AM
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.

New Member
New Member

26 Oct 2011 10:44 AM
The majority of the memory usage will be on the server that is executing (where the package resides) the SSIS package. So if the package is executed on Server A but reads data from server B then most of the memory utilization from the SSIS package will be on Aerver A. Any memory utilization on Server B will be fromt he query itself just like any other query regardless of the source. So typically you would want to run your SSIS packages on a server dedicated for that purpose so the memory and CPU of the package itself is not conflicting with the memory and CPU from SQL Server. Again with the exception of the resources used by the query itself. But all other processing of the data by the SSIS package will be on the SSIS server not the SQL Server. Hope that helps.

Acceptable Use Policy