SSIS Catalog Environments– Step 20 of the Stairway to Integration Services

• Andy Leonard

  SSChampion

  Points: 10151

  More actions

  February 18, 2015 at 12:00 am

  #314685

  Comments posted to this topic are about the item SSIS Catalog Environments– Step 20 of the Stairway to Integration Services

  Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

• Phil Parkin

  SSC Guru

  Points: 247117

  More actions

  February 18, 2015 at 12:29 am

  #1778263

  Nice article Andy. I have never seen it all set out so clearly before, well done.

  ---

• Paresh Motiwala

  SSCommitted

  Points: 1772

  More actions

  March 28, 2020 at 2:47 pm

  #3738629

  Hi @Andy you remember the environment you had help me migrate? well, that whole server and it backup infrastructure just failed drastically. We were able to recover the SSISDB. I can see the packages, but not the environments. Any ideas where to look for those?

  Attachments:

  You must be logged in to view attached files.

  Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

• Andy Leonard

  SSChampion

  Points: 10151

  More actions

  March 28, 2020 at 3:06 pm

  #3738635

  Hi Paresh,

  I don't remember, but I am getting old (and I've helped a few people).

  You may want to execute the following query to see if environments, environment variables, and environment references exist in the recovered SSIS DB:

  Select * From SSISDB.[catalog].environments

  Select * From SSISDB.[catalog].environment_variables

  Select * From SSISDB.[catalog].environment_references

  You may also with to attempt a test deploy operation. It's possible to recover SSISDB improperly. If the deploy fails, the SSISDB database may have been restored, but not SSIS Catalog constituents. I blogged about this (for SSISDB 2016) at https://andyleonard.blog/2017/07/deploying-ssis-projects-to-a-restored-ssis-catalog-ssisdb/

  Hope this helps,

  Andy

  Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

• Phil Parkin

  SSC Guru

  Points: 247117

  More actions

  March 28, 2020 at 3:46 pm

  #3738639

  Paresh Motiwala wrote:

  Hi @Andy you remember the environment you had help me migrate? well, that whole server and it backup infrastructure just failed drastically. We were able to recover the SSISDB. I can see the packages, but not the environments. Any ideas where to look for those?

  I agree with Andy. The way in which you 'recovered' SSISDB is very important ... how did you do it? A simple RESTORE is not enough for SSISDB, because some of the columns in it are encrypted.

  ---