?
Solved

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

Posted on 2011-10-10
11
Medium Priority
?
284 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:PKTG
  • 6
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36945915
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36947229
Select distinct name From sysjobs sj Inner join sysjobsteps sjs
ON sj.job_id = sjs.job_id WHERE subsystem='SSIS'
0
 

Author Comment

by:PKTG
ID: 36966400
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36970360
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
 

Author Comment

by:PKTG
ID: 36971000
Yes
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36971782
I can see a number of problems with the solution provided, but have you tried it?  Does it meet your requirements?
0
 

Author Comment

by:PKTG
ID: 36974901
nope. still waitting to get correct solution. Thanks for all your help
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976252
>>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
 

Author Comment

by:PKTG
ID: 36976267
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36976307
>>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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 36976314
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question