May 30, 2008 at 2:17 pm
This week, I ran into a very interesting situation. I finished building a rather complex SSIS package in the BI Dev Studio, it ran perfectly in there, but when I deployed it to the server it is meant to run on, it would run the first step, then fail with an error about needing a higher level of SSIS.
Since this test deployment was to a copy of Dev Edition, which is the same as Enterprise, this seemed quite odd to me.
After investigation, and a call to MS, it turned out that, while SSIS was installed on the server, it couldn't be turned on as a service (didn't even show up in the list of services to turn on/off/configure). MS isn't sure why, but my conclusion was that it was caused by the fact that the default instance on that server was Workgroup Edition, which doesn't support SSIS.
Uninstalling the default instance, and replacing it with an edition that supports SSIS, solved the problem.
It turns out (and this makes sense), that the Dev Studio doesn't use the SSIS service to run packages, it uses something built into it. It's kind of an "of course!" thing on that one, but it had never occured to me.
So, after several hours of trying to figure out what the permissions issue was (as per the MSDN article on packages that run in Dev but not in Agent), it turned out to be a problem with the service instead.
So, if anyone else runs into a similar problem, here's a possible solution.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 1, 2008 at 11:05 pm
Wow.
Okey, thanks for the advice.
There were a couple of postings on when people deployed their respective packages to server and they didn't run, but so far in most cases it was narrowed down to either SSIS Configuration, or permissions assigned to the Agent.
Thanks once more
~PD
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply