I'm brand new to these forums, so please let me know if I'm posting this in the wrong group.
We are building a new SSIS environment. We plan to use TFS to deploy solutions to servers we called SSISDEV and SSISPROD. And then use CA-Automic(aka UC4, which is a batch scheduler product) to execute the solutions.
We are not using dtexec. We are running a series of database SQL commands that update parameters, execute the solution in synchronous mode, and query and report the results via UC4.
We have been using this model for quite a while in test mode without TFS. Developers have been deploying manually to SSISDEV from Visual Studio and with database admin rights, and UC4 can execute those deployed solutions.
We are now trying to use TFS to deploy with a service account, so that developers will no longer be required to have database admin rights. But we have run into unexpected issues! Our TFS expert set up the ability to deploy the solution to the proper server and the proper folder, but when UC4 executes the solution we now get this error;
PKG_<packagename>:Error: Failed to decrypt protected XML node "DTS:Password" with error 0x80070002 "The system cannot find the file specified.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is availab (trunkated message)
We believe the root cause of this problem is with the SSIS package protection level settings. We have been experimenting with different configurations to resolve this issue, but are currently floundering around not really knowing or understanding all of the ramifications of this problem. For instance I only recently learned that package protection level settings can render code to be unusable by other developers, if you don't know what you are doing (and I don't think that we do.)
So some of our burning questions include; what should we use for our package protection level when developing our solutions? What should we use as our package protection level when deploying? Can TFS automatically "correct" it for us? Should/could UC4 manipulate package protection level and/or password at runtime?
In our test use-case, the developer, the TFS expert, and the UC4 expert are three different people. (I'm the UC4 expert but I also dabbled in SSIS a few years ago.)
Is our road map even doable?
Surely we aren't the first shop to attempt something like this.....?