CODEWITHSUNDEEP
sql-serverssis
daniely
daniely

Reputation: 7733

Detect SSIS enabled from code

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?

Upvotes: 1

Views: 232

Answers (1)

Ciarán
Ciarán

Reputation: 3057

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.

Upvotes: 1

Related Questions