August 29, 2011 at 4:08 pm
OK - I've got an SSIS package that does 98% of the work on Server 1. I'd like to deploy the package to Server 1 and run the job on Server 1.
The fly in the ointment is that Server 1 is a virtual IP that can point to any number of servers, depending on which one is in rotation. Not a problem to deploy the package to all the servers associated with the virtual IP. The problem is that the job can't be scheduled on all the servers since it only needs to run every X [interval].
The DBAs are not pleased with the thought of creating a job on the virtual server. Their suggestion is to have the package deployed to each of the server instances and then create a job on "our" server to call the package off the virtual one.
Yes, I can do this. The job will connect to the virtual IP and run the package from the msdb of whatever server is in rotation. Here's my question:
If I create a SQL job on Server 2 and have a step that runs an SSIS package on Server 1, which one is actually running the package?
August 30, 2011 at 8:53 am
If you're asking which machine is doing the work, then it would be the machine that has the SSIS package deployed on it. So, in your case, Server 2.
Of course, the bulk of the work is probably being done by whatever database is containing the data, but that's another story.
August 31, 2011 at 8:33 am
Thanks so much. I was hoping that was the case since it means I can get this thing set up and running, knowing it won't take forever going between servers.
The intent here is for the database on that same server to be doing the work. So it's all local to the machine.
August 31, 2011 at 10:52 am
I believe which server is doing the bulk of the work will depend on how your SSIS package is set up. Any database level commands would do their work on Server 1, for example is you ran an UPDATE command or an expensive select command to generate a dataset. However any tasks in the SSIS package would run on Server 2, so for example any scripting tasks or data manipulation tasks in the dataflow.
August 31, 2011 at 12:11 pm
That would only be the case if the database was actually located on Server 2.
If I understand the OP correctly, there's a whole bunch of servers and machines which are in some sort of rotation, and one single server that is running SQL Server agent for the purposes of execution of the SSIS package.
So, the SQL Server Agent DB Server, let's call it Server 1, runs the SSIS package against Server 2, which contains *both* the SSIS package and the server that has the tables/procs/etc that the SSIS package is using.
September 1, 2011 at 7:14 am
kramaswamy (8/31/2011)
That would only be the case if the database was actually located on Server 2.If I understand the OP correctly, there's a whole bunch of servers and machines which are in some sort of rotation, and one single server that is running SQL Server agent for the purposes of execution of the SSIS package.
So, the SQL Server Agent DB Server, let's call it Server 1, runs the SSIS package against Server 2, which contains *both* the SSIS package and the server that has the tables/procs/etc that the SSIS package is using.
My understanding was that Server 1 had both the SQL Agent job and the SSIS package so that the package only needed to be deployed once. The package would run on Server 1, but all of the data would be on Server 2.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 1, 2011 at 12:57 pm
drew.allen (9/1/2011)
kramaswamy (8/31/2011)
That would only be the case if the database was actually located on Server 2.If I understand the OP correctly, there's a whole bunch of servers and machines which are in some sort of rotation, and one single server that is running SQL Server agent for the purposes of execution of the SSIS package.
So, the SQL Server Agent DB Server, let's call it Server 1, runs the SSIS package against Server 2, which contains *both* the SSIS package and the server that has the tables/procs/etc that the SSIS package is using.
My understanding was that Server 1 had both the SQL Agent job and the SSIS package so that the package only needed to be deployed once. The package would run on Server 1, but all of the data would be on Server 2.
Drew
Drew:
Server 1 would have the SQL Agent and the job. All the servers that use the Server 1 virtual IP would have the package deployed locally. Otherwise the job wouldn't know what server to use.
With the answer that the server where the package is deployed "does the work" I could then have it deployed on all the virtual IP server and call it from Server 2.
The fly in my ointment comes from security and privacy issues. Server 2 is in a higher security segment so it has to push/pull the data. If the package is out on the VIP servers that won't happen. So I have to deploy and run the package on Server 2 regardless of potential performance issues.
But this has been very helpful for potential future projects.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply