Shared SSIS Staging/Dev environment

gibsonian

We are in the process of setting up a shared staging/development enviroment in which multiple developers will deploy their packages for testing etc.

The plan is to allow the developers to remote in to execute their packages, but we are worried about the concurrency limit of 2 users per terminal services.

It is my understanding that you cannot execute packages remotely (ie you have to be on THAT box to kick it off. (FYI - we are not planning on using SQL Agent at this point - That will be with DBAs in UA)

Please let me know what options are available/best practices...

Thanks!

DarrenSQLIS


I would make developers dev on their local machines. When you are ready to deploy to a server environment, call it what you want, then you should be using the real execution host, such as SQL Agent. I don't understand your rationale for wanting to have dev/users executing packages interactively in a server environment, but it doesn't feel right. Whilst I accept it is sometimes handy for debugging at the Dev level, it should not be the norm.

jwelch

I agree. We have our developers working locally, and we deploy packages to a common dev server at regular intervals to ensure that everything works together properly.

gibsonian

I should not have included "dev" in the environment description. The developers will be working on their own workstations. This is a shared environment to make sure everything works together, as described by jwelch.

We seem to have a DBA vs. Developer culture here - so the developers are expected to debug etc. without access to SQLAgent

The crux of the question was really how to accomodate more than 2 concurrent users to execute SSIS packages on a box at a given time.

jwelch

You'll need to execute the packages programatically on the server. You can do that by using SQL Agent (and calling sp_start_job), if you can set up a job on the dev server. Otherwise, you might check out some of the methods described in this article: http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx