SSIS Deployment Environments

Question

Post reply

SSIS Deployment Environments

tim.ffitch 25252

SSCrazy

Points: 2055
More actions
October 14, 2016 at 4:46 am

#331032

I have been using Project deployment with SQL 2012 for the past 2 years of more, but in a 3 tier distinct server set up. I now have to use it on a single server where we will need to set up environments for DEV, UAT and Live. What I am not understanding is how on a single server can you deploy DEV changes or UAT Testing packages without affecting Live? I have read several articles but nothing seems to make this distinction. Nowhere in the environment configuration does its seem to allow you to select a deployment version of the package.
Also I can see that once you have your base environments set up, when you select the package to configure you can select the Environment at the Local Level or Project Level. Can I assume from this these are 2 distinct logical layers so that anything at the Local Level is available to all projects on the server and anything at the Project Level is only available to that project?
Some advice and pointers to more in-depth articles would be really helpful.
Thanks
Tim.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

So you want to set up a single SSIS server containing multiple version of packages, is that correct?

Or merely have the same packages using different variable values, depending on target environment?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 2

Hi Phil.

Not sure how the final solution will pan out at the moment as everything is moving over to AWS, so lets say development will be done on the users local machine. Once the developer has finished the development and gone through Peer Testing (if any) :-P, it will be deployed to the SSIS server for UAT testing, so hence the UAT environment in the SSIS Catalog. But as we have now deployed a change to the SSISDB this must also affect live.

Once UAT is completed then we make the Live deployment. In a 3 tier distinct server environment this all works naturally and nothing can interfere across distinct servers.

So I guess in answer to your question, yes it will be multiple versions of the same package.

Tim.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

tim.ffitch 25252 (10/14/2016)
Hi Phil.
Not sure how the final solution will pan out at the moment as everything is moving over to AWS, so lets say development will be done on the users local machine. Once the developer has finished the development and gone through Peer Testing (if any) :-P, it will be deployed to the SSIS server for UAT testing, so hence the UAT environment in the SSIS Catalog. But as we have now deployed a change to the SSISDB this must also affect live.
Once UAT is completed then we make the Live deployment. In a 3 tier distinct server environment this all works naturally and nothing can interfere across distinct servers.
So I guess in answer to your question, yes it will be multiple versions of the same package.
Tim.

I presume that you know that the best-practice solution to this is to have a separate SSIS server for production? Having dev versions of packages on a prod server is a risk, no matter how segregated, because those packages can adversely affect production service.

OK, now I've got that out of the way, if we assume that there is only one instance of SSISDB available to you, your best option is to use folders within the Integration Services Catalog to keep things separate.

Once you have your folders created, say UAT and Production, you can deploy your packages accordingly. I suggest that you automate this process & deploy directly from source control, in an attempt to avoid any mistakes.

You should also create separate SSISDB 'environments', to power the packages in the different environments. Do it right and the process of moving a package from UAT to production will not require any package changes – environment variables drive all of the params and connections which vary between UAT and prod.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tim.ffitch 25252 SSCrazy Points: 2055 More actions · Answer 4

Hi Phil.

Thanks, I had already come to this same conclusion.

Tim