February 2, 2016 at 12:16 pm
I've about 200 SSIS packages that needs to be run on 10 different instances. What is the best way to configure this dynamically? Ideally I'd like to store them in CMS server and run them on all the SQL instances that I need. Is this possible? Any suggestions/ideas would be appreciated.
February 3, 2016 at 10:13 am
SQL_Surfer (2/2/2016)
I've about 200 SSIS packages that needs to be run on 10 different instances. What is the best way to configure this dynamically? Ideally I'd like to store them in CMS server and run them on all the SQL instances that I need. Is this possible? Any suggestions/ideas would be appreciated.
This is an interesting requirement.
Maybe something like this:
1) For each server
a) Copy package(s) to server
b) For each package
i) Call DTExec to execute the package, perhaps using something like PSExec to execute them locally to the server.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 3, 2016 at 10:18 am
I just had another idea, which may or may not work in practice.
Create a 'master' package. This package searches a specific local folder and executes any packages it finds there.
Deploy the master package to each of your servers.
Create a SQL Agent job on each of the servers to execute the master package.
Your CMS process then becomes:
1) Copy packages to specified remote folders on remote servers.
This should also remove any packages no longer required on the remote folders.
2) Execute the SQL Agent jobs on the remote servers.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 3, 2016 at 10:26 am
SQL_Surfer (2/2/2016)
I've about 200 SSIS packages that needs to be run on 10 different instances. What is the best way to configure this dynamically? Ideally I'd like to store them in CMS server and run them on all the SQL instances that I need. Is this possible? Any suggestions/ideas would be appreciated.
Why do they need to run ON the specific instances as opposed to AGAINST the 10 instances? Anything you can do in SSIS to be executed locally can be executed remotely. In theory you can run each Package on a single machine and have it configured to do the necessary work on each of the remote machines. What are these Packages doing?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 3, 2016 at 10:54 am
Orlando Colamatteo (2/3/2016)
SQL_Surfer (2/2/2016)
I've about 200 SSIS packages that needs to be run on 10 different instances. What is the best way to configure this dynamically? Ideally I'd like to store them in CMS server and run them on all the SQL instances that I need. Is this possible? Any suggestions/ideas would be appreciated.Why do they need to run ON the specific instances as opposed to AGAINST the 10 instances? Anything you can do in SSIS to be executed locally can be executed remotely. In theory you can run each Package on a single machine and have it configured to do the necessary work on each of the remote machines. What are these Packages doing?
Great question.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply