Monitoring Running Packages under non Admin Account
Hi guys. I’ve built a service that starts and manages the execution of packages in SSIS, but so far I was using my custom logging for this. Now, we’ve got a new requirement where the user can cancel the current execution of packages. In order to achieve this, I tried to use C# to get all Running Packages (Microsoft.SqlServer.Dts.Runtime, Application, GetRunningPackages). What I’ve found and didn’t expected is that the user that runs the Service isn’t an Administrator in the SSIS Box and neither is it the user that run’s the package, so he can’t see the running packages. I can think of two options. First is to change the “DTEXEC …..” command to in some way use the Active Directory user that runs the service. The second is to find some way in that I can setup the SSIS Box to allow this AD User to see the running packages without having to grant him Admin rights. Do you have any idea in how to achieve this? Or can you thing about any other solutions? Thanks in advance. Sorry about my English, it’s rusted of years not using it. Regards from Brazil. Take Care
November 30th, 2010 9:57am

Why not to use SQL Server Agent to run the package as its job, this way you set to run it under a proxy account and to stop a package you stop the job, elegant IMHO. How to run a SQL Server 2005 Integration Services package as a SQL Server Agent job step: http://support.microsoft.com/kb/912911Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
November 30th, 2010 10:06am

I agree with ArthurZ... This might give you an idea on how to execute a job remotely. Might help to get the creative juices flowing.Please 'Mark as Answer' if found helpful - Chris@tier-1-support
November 30th, 2010 3:43pm

Thanks for your answers "ArthurZ" and "Tier 1 Support". I tried it, but it won't work in my case because I have a odd implementation in my packages, that I've not seen so far. The company requested for an security system where all company product's would retrieve Connection Strings, User Information, and Common Configurations from a single place, thus know as ConfigSystem. We come with an C#/C++ unmanaged code know for us as SecuritySystem, that have all the security information for Cryptography, Hash, SQL Response Validation and so on stored in the unmanaged C++ so it would be very difficult for an occasional hacker/cracker to decrypt our Cryptographic algorithms and Hash/Cryptographic keys. The afore mentioned ConfigSystem uses this Security class to store and encrypt the configuration information. This works perfectly and our security team was unable to hack our system so far, thus everybody is happy. Or was happy until we decided to use it on our SSIS pkgs. The first step of the SSIS Pkg is a Script Task that gets all connections informations using the ConfigSystem class, that then access the SecuritySystem class, that consumes the C++, and here my friends, the problem begins when trying to use the SQL Agent/Jobs. C++ unmanaged isn't much flexible while using 32/64 bit systems, due to some InterOp issues, and for some reason, the job tries to run the pkg under 32 bits even though the 32 bit flag in the configuration isn't set, this way the 64 bit SecuritySystem class that is installed in the 64 bit server crashes. After testing this for a while, and as we couldn't find a solution for this odd issue, we decided to change our approach to a different way. Now, I'd like to hear your thoughts about this new solution. We created a service that runs in the DB Server( i.e. runs in the same machine as SQL) and run's under the desired Account. This service monitors a table in the database that tells the service there is a package scheduled for execution and pass the package parameters. Then using C# DTS.Runtime we start the desired package. This way, we are able to monitor from the App Server whether the desired package is still running, and monitor the steps through the implemented log system. If it wasn't due to the C++ restrictions we found with the SQL Agent/Jobs, the solution you guys presented would work perfectly (I've tested our system this way, configuring the Package manually instead of using the SecuritySystem class, and it worked perfectly). Thanks a lot guys, and please let me know if you need clarification about this big post or if you guys can come with different solutions! Regards form Brazil. Jhon
Free Windows Admin Tool Kit Click here and download it now
December 6th, 2010 12:15pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics