May 13, 2008 at 4:42 pm
We have a core set of SSIS packages that are used by each server (12 Servers totals). It's become a real pain to deploy these packages each time we bring a new server on-line. I'd like to store a single copy of each SSIS package on a dedicated ETL Server.
Does anyone know of any serious performance issues this might cause? Each of our SQL 2005 Servers would be calling the same SSIS package at the same time...or at least within a minute or two...of the other server.
If a package is called from a remote server...where does the processing occur?
I've searched all around this site and microsoft and can't seem to find the answer...any help is greatly appreciated!!
May 14, 2008 at 6:46 am
The server (or file system location) a package is stored in does not impact the execution of the package unless it is really, really big. The only impact it can have is the time it takes to load the XML of the package onto the server that is running it.
Now, environment - if you have an SSIS server that just stored the packages - call it ServerA. Then you have a SQL server running the job agent and executing the package - call it ServerB. The package runs on ServerB in the context of ServerB (meaning it uses the C: drive on ServerB if you reference files).
Now, include a ServerC in the equation - another SQL server running it's own job agent and running the same package stored on ServerA. If you use package configurations and store them in a local drive folder (call it C:\Config), the package configuration file used by the package will be the ones on ServerB and ServerC - this is a good way to re-use a package on different servers and have it connect appropriately.
Remember a couple of things. If data is being moved from ServerC to ServerD, but the package is being executed by the agent on ServerE, data has to move to ServerE along the way. Last - the server RUNNING the package, NOT the server storing the package MUST be an SSIS server of the appropriate edition for the components you have used. Where you store it is irrelevant, it is where it runs that matters.
May 14, 2008 at 9:15 am
Thanks...that was exactly what I was looking for!
Although I do have one more question to clarify based on your last example...several packages perform imports of csv, or txt files from a file server....nothing over 500 records on a nightly basis. If server A is my package repository and server B is my import destination running the scheduled job (using a config file for connection reference)...is the text file passing through server A?
Thanks again!!
May 14, 2008 at 10:03 am
No, in your scenario, ServerA could sinply be a file server with a DTSX file sitting on it. Other than being the source of the definition of the package, it will do nothing.
May 14, 2008 at 11:27 am
Thanks!!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply