Hi Everybody, Since the SSIS package I'm developing locally will be deployed to the DEV, PRE, and PROD servers by a network admin., I'm now tasked with developing a pre-flight script that will determine (1) if SSIS is installed on the current SQL Server and if so (2) will ensure the SSIS service is running (that is, it must start the service if it's not already started). My initial disappointment came when I read that SSIS exposes no T-SQL API (http://www.bigresource.com/Tracker/Track-ms_sql-24Z1gGXN/). Is there some other way to do these two tasks programmatically? I imagine that Powershell is a viable option, but there's only about 4 things I know how to do with Powershell--three of those are "get-service," "start-service," and "stop-service"--but I don't have any idea how to use just a Powershell script to do these 2 tasks and return some message. Could somebody assist me with a very simple Powershell script that could get me started (again, I can write a very simple one, but I don't know to read statement results and output some statement like "SSIS is not yet installed!") or else point me to a spot on the web where I can figure it out on my own? Any help would be greatly appreciated. Thank you, Eric B.

I understand the need to check if SSIS is installed or not, but I am not sure you need to check for the status of the service as it is not required for running packages. Anyway, a non powershell option is to check if the SSIS service is installed or not by using SC.exe command. e.g if you want to check for SSIS 2008 R2 you can run: SC Query MsDtsServer100 If the service is installed you should get: SERVICE_NAME: MsDtsServer100 TYPE : 10 WIN32_OWN_PROCESS STATE : 1 STOPPED WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 otherwise you should get an error: [SC] EnumQueryServicesStatus:OpenService FAILED 1060: The specified service does not exist as an installed service. You would need to find a way to script this. Here is a link you may find helpful: http://www.msfn.org/board/topic/37978-how-to-check-if-a-service-is-running-via-cmd/ Help Others! Don't forget to mark your thread as answered

Hi Rafael, Thanks for your assistance; this is very helpful. I didn't know SSIS service wasn't required to be running in order to run the package. That alone cuts my task in half! Although I definitely need to learn PowerShell, I can't wait for that to happen before I get this task complete. I don't know anything about the "SC" command, but I'll be a pro by this time tomorrow! Once I figure out how to script it, I'll mark this thread as answered and include the final script(s) here. (Until then, maybe somebody else will offer some other methods.) Thanks again. Also, your SSIS blog has been very helpful to me since I'm new to SSIS 2008 and haven't touched SSIS 2005 for a couple of years now after using it for only a short time. -Eric

An alternative would be to check via the regsitry, perhaps look for a value under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\Setup\DTSPath. It is the sort of thing you do when building an MSI installer, where registry checks are very easy to do, and often used to find things like install paths which are then used in the installation itself.http://www.sqlis.com | http://www.konesans.com

What does it mean if I find an entry in the registry just like above but also get an error [SC] EnumQueryServicesStatus:OpenService FAILED 1060: when using SC Query MsDtsQuery100? Does it mean SSIS is installed of not? I keep getting this error: [Description: To run a SSIS package outside of Business Intelligence Development Studio you must install Standard Edition of Integration Services or higher.]