How to find how many SSIS jobs are deployed and enabled in each servers and total ?

We have almost 40 SQL Servers and we have deployed and running many SSIS Package jobs in each server.
Can you please tell me
1.      How can we find how many jobs are deployed and enabled in each servers?.
2.      Each enabled job total counts seperately (adding each enabled jobs counts from all the servers like Job A - 30 and Job B -20 like that)
Thanks a lot.
PKTGAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Incidentally, unless you are prepared to make some cosmetic changes to the jobs containing those packages, this may be a case of "You can't do that" (http://www.experts-exchange.com/help.jsp#hs=29&hi=405)
0
 
Anthony PerkinsCommented:
1. That really depends.  Define "deployed". Supposing you have 2 SQL servers and 1 app server that runs your SSIS packages (DTSX files) to move data between SQL Server 1 and 2.  How would you propose computing that.  In other words you could have 100 packages and not one is actually installed on a SQL Server box, yet all of them access at least one SQL Server box.

2. I am even more confused how you propose to compute this.
0
 
Alpesh PatelAssistant ConsultantCommented:
Select distinct name From sysjobs sj Inner join sysjobsteps sjs
ON sj.job_id = sjs.job_id WHERE subsystem='SSIS'
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
PKTGAuthor Commented:
I need to get count of Deployed and Enabled SSIS jobs count. for ex: i have deployed 3  ssis jobs like job A, B & C  in one SQL Server(Server1) but enabled only A & B not C. And also I have deployed and enabled job A,B & C in another SQL Server Machine(Server2). Basically i need

1. Server Based Job Count:
Server1: total SSIS enabled job count is 2
Server 2: total SSIS enabled job count is 3

2. Job Based Count:
Job A: 2
Job B: 2
Job C: 1
0
 
Anthony PerkinsCommented:
Again we are confusing terminology here.  SSIS are packages not jobs.  One SSIS job could be in one or more SQL Server jobs.  So do you want a list of unique SSIS packages in all SQL Server Agent jobs that are enabled?
0
 
PKTGAuthor Commented:
Yes
0
 
Anthony PerkinsCommented:
I can see a number of problems with the solution provided, but have you tried it?  Does it meet your requirements?
0
 
PKTGAuthor Commented:
nope. still waitting to get correct solution. Thanks for all your help
0
 
Anthony PerkinsCommented:
>>nope. still waitting to get correct solution.<<
Perhaps I am being dense, but how do you know it is not the correct solution if you have not tried it?

Here is my problem, anything that I could help you with is based on the same system tables, if you are saying that is not for you, then I am afraid I have no idea.  If on the other hand you state that it has potential, than I can work with you to meet your requirements as best as I can.
0
 
PKTGAuthor Commented:
I already tried with the above "Select distinct name From sysjobs sj Inner join sysjobsteps sjs
ON sj.job_id = sjs.job_id WHERE subsystem='SSIS' " and it is returning only one ssis package details but i have deployed almost 20 SSIS package. That's whay i am saying still waitting for the solution. Thanks.
0
 
Anthony PerkinsCommented:
>>I already tried with the above <<
Ok, so you have tried it.  I just wanted to make sure of that.  Please post a typical value from the Command column in the sysjobsteps that contains an SSIS package.  I am trying to determine what type of package you are executing and how you are doing that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.