1

I am developing in .NET and c#.

How can I tell if SSIS Integration Services are enabled on the SQL server that I'm working on?

tsells
  • 2,751
  • 1
  • 18
  • 20
daniely
  • 7,313
  • 5
  • 29
  • 46

1 Answers1

1

SQL Server Integration Services runs as a service so you could use this...

Check if a service exists on a particular machine without using exception handling

To check to see if it's running. The curve ball is that the name may be...

SQL Server Integration Services
SQL Server Integration Services 10.0

or other variants, so you could check for services where the name begins with "SQL Server Integration Services" or contains "SQL" "Integration" and "Services"

Edit:

This is rather nasty but provided you have the right privileges it will work...

Create Table #Output (CmdOutput Varchar(2000));
Insert Into #Output Exec XP_CmdShell 'SC query MsDtsServer100';
Select * From #Output;
Drop table #Output;

This will give you something like this...

CmdOutput
------------------------------------------------------
NULL
SERVICE_NAME: MsDtsServer100 
    TYPE               : 10  WIN32_OWN_PROCESS  
    STATE              : 4  RUNNING 
                            (STOPPABLE, NOT_PAUSABLE, ACCEPTS_SHUTDOWN)
    WIN32_EXIT_CODE    : 0  (0x0)
    SERVICE_EXIT_CODE  : 0  (0x0)
    CHECKPOINT         : 0x0
    WAIT_HINT          : 0x0
NULL

If it doesn't exist then you will get something like this...

[SC] EnumQueryServicesStatus:OpenService FAILED 1060:
NULL
The specified service does not exist as an installed service.
NULL
NULL

If you don't have privilege then I'm afraid that this sort of stuff is probably not possible.

Community
  • 1
  • 1
Ciarán
  • 3,017
  • 1
  • 16
  • 20
  • It seems MsDtsServer is the name of the process to check for. – daniely Aug 13 '14 at 18:02
  • What if I do not have enough privilege to get the list of windows services from the machine that SQL server runs on? Cannot open Service Control Manager on computer 'MachineName'. This operation might require other privileges. Is there a SQL query that I can run to get the same information? – daniely Aug 18 '14 at 15:03
  • You are going to need privilege for any activity of this nature. Do you have an account with dba privileges? If not then I'm afraid you won't be able to do it at all. – Ciarán Aug 18 '14 at 20:00