{"id":12195,"date":"2019-01-11T16:42:58","date_gmt":"2019-01-11T15:42:58","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/"},"modified":"2019-01-11T16:42:58","modified_gmt":"2019-01-11T15:42:58","slug":"copy-or-migrate-a-ssisdb-environment","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/","title":{"rendered":"Copy or Migrate a SSISDB environment"},"content":{"rendered":"<p style=\"margin: 0in;font-family: 'Courier New';font-size: 10.0pt\">Integration catalog is easy to work with and the environments feature is as well very convenient.<\/p>\n<p style=\"margin: 0in;font-family: 'Courier New';font-size: 10.0pt\">In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is only possible in another folder of your SSIS catalog. But how if you will to duplicate your environment or move it on another server.<\/p>\n<p style=\"margin: 0in;font-family: 'Courier New';font-size: 10.0pt\">With the below steps, you will be able to generate the script, to create your environment, on the same server changing the folder or\/and the environment name or to run it on another server.<\/p>\n<ol style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in;font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\" type=\"1\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\" value=\"1\"><span style=\"font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\">Run the script below where you have initially created your SSIS catalog environment<\/span><\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<pre class=\"brush: actionscript3; gutter: true; first-line: 1\">DECLARE @FolderName nvarchar(128) = N'SSISDemo'\nDECLARE @EnvName nvarchar(128)= 'Env_SSISDemo1'\nSELECT \n'DECLARE @FolderName nvarchar(128) = N''' + @FolderName + '''\nDECLARE @EnvName nvarchar(128)= ''' + @EnvName + '''\nEXEC [SSISDB].[catalog].[create_environment] @folder_name=@FolderName, @environment_name=@EnvName, @environment_description=N''' + COALESCE(e.description, '') + '''' \nas tsql_EnvCopy \nFROM SSISDB.catalog.folders f\nINNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id\nWHERE f.name = @FolderName\nAND e.name = @EnvName\nUNION ALL \nSELECT \n'EXEC [SSISDB].[catalog].[create_environment_variable] \n@folder_name=@FolderName, \n@environment_name=@EnvName, \n@variable_name=N'''+ ev.name + ''', \n@data_type=N'''+ ev.type + ''', \n@sensitive='+ CONVERT(NCHAR,ev.sensitive) +', \n@value = ' + \nCASE ev.sensitive\nWHEN 0 THEN \nCASE ev.type \nWHEN 'Date Time' THEN ''''+ CONVERT(NVARCHAR(max),ev.value) + '''' \nWHEN 'String' THEN 'N'''+ CONVERT(NVARCHAR(max),ev.value) + '''' \nELSE CONVERT(NVARCHAR(max),ev.value)\nEND \nWHEN 1 THEN \n'##########'\nEND + ',\n@description=N'''+ ev.description + ''''\nas tsql_EnvVarcreate\nFROM SSISDB.catalog.folders f\nINNER JOIN SSISDB.catalog.environments e on e.folder_id = f.folder_id\nINNER JOIN SSISDB.catalog.environment_variables ev on ev.environment_id = e.environment_id\nWHERE f.name = @FolderName\nAND e.name = @EnvName<\/pre>\n<ol style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in;font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\" type=\"1\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\" value=\"2\"><span style=\"font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\">Copy the result of the script (column tsql_EnvCopy) in a copy it in a SSMS Query window connected to the server where your want to deploy your environment.<\/span><\/li>\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">Adapt the script if needed.<\/span><\/li>\n<\/ol>\n<ul style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in\" type=\"disc\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">On the server where you want to create the new environment, be sure that:<\/span>\n<ul style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in\" type=\"circle\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">The folder with the defined FolderName is existing.<\/span><\/li>\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">FolderName\/EnvName combination is not already existing on the server.<\/span><\/li>\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">Change the values of the @FolderName or\/and @EnvName if needed.<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in\" type=\"disc\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\"><span style=\"font-family: 'Courier New';font-size: 10.0pt\">Change the values of the sensitive parameters:<\/span><\/li>\n<\/ul>\n<p style=\"margin: 0in;margin-left: .375in;font-family: 'Courier New';font-size: 10.0pt\">As you can imagine the parameter values cannot be retrieved when they are set as sensitive. If you have such parameter replace the values ########## with the real ones before running the script.<\/p>\n<ol style=\"margin-left: .375in;direction: ltr;margin-top: 0in;margin-bottom: 0in;font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\" type=\"1\">\n<li style=\"margin-top: 0;margin-bottom: 0;vertical-align: middle\" value=\"4\"><span style=\"font-family: 'Courier New';font-size: 10.0pt;font-weight: normal;font-style: normal\">Run the script and you&#8217;re done<br \/>\n<\/span><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Integration catalog is easy to work with and the environments feature is as well very convenient. In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is [&hellip;]<\/p>\n","protected":false},"author":38,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[294,229,198,99],"tags":[1549,15,592],"type_dbi":[],"class_list":["post-12195","post","type-post","status-publish","format-standard","hentry","category-business-intelligence","category-database-administration-monitoring","category-database-management","category-sql-server","tag-environment","tag-migration","tag-ssis"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Copy or Migrate a SSISDB environment - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Copy or Migrate a SSISDB environment\" \/>\n<meta property=\"og:description\" content=\"Integration catalog is easy to work with and the environments feature is as well very convenient. In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-01-11T15:42:58+00:00\" \/>\n<meta name=\"author\" content=\"Christophe Cosme\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christophe Cosme\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\"},\"author\":{\"name\":\"Christophe Cosme\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a\"},\"headline\":\"Copy or Migrate a SSISDB environment\",\"datePublished\":\"2019-01-11T15:42:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\"},\"wordCount\":257,\"commentCount\":1,\"keywords\":[\"environment\",\"Migration\",\"SSIS\"],\"articleSection\":[\"Business Intelligence\",\"Database Administration &amp; Monitoring\",\"Database management\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\",\"name\":\"Copy or Migrate a SSISDB environment - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2019-01-11T15:42:58+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Copy or Migrate a SSISDB environment\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a\",\"name\":\"Christophe Cosme\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g\",\"caption\":\"Christophe Cosme\"},\"description\":\"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Copy or Migrate a SSISDB environment - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/","og_locale":"en_US","og_type":"article","og_title":"Copy or Migrate a SSISDB environment","og_description":"Integration catalog is easy to work with and the environments feature is as well very convenient. In SQL Server management Studio, there are a lot of features available to administrate your SQL Server instances, but some are missing that can be very useful. You have the possibility to move an SSISDB environment but this is [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/","og_site_name":"dbi Blog","article_published_time":"2019-01-11T15:42:58+00:00","author":"Christophe Cosme","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Christophe Cosme","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/"},"author":{"name":"Christophe Cosme","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"headline":"Copy or Migrate a SSISDB environment","datePublished":"2019-01-11T15:42:58+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/"},"wordCount":257,"commentCount":1,"keywords":["environment","Migration","SSIS"],"articleSection":["Business Intelligence","Database Administration &amp; Monitoring","Database management","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/","url":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/","name":"Copy or Migrate a SSISDB environment - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2019-01-11T15:42:58+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/copy-or-migrate-a-ssisdb-environment\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Copy or Migrate a SSISDB environment"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/5cbf7c261d165a45977c85f535d6cb6a","name":"Christophe Cosme","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/736ec7a3216e381c2ef81f210f381e613297d76c2120169411c4423c3c58992c?s=96&d=mm&r=g","caption":"Christophe Cosme"},"description":"Christophe Cosme has more than 22 years of experience in architecture, development and implementation of IT solutions including 17 years in the Business Intelligence area as well as 10 years of experience working with the Microsoft SQL Server Business Intelligence suite. His experience and qualifications cover project management, business analysis, functional and technical requirements. His expertise also includes the implementation of IT solutions in national as well as international organizations. Christophe Cosme is Expert in MS SQL Server DB Engine, SSIS, SSAS, SSRS and PowerBI. Prior to joining dbi services, Christophe Cosme was Solution Lead Business Intelligence Diabetes Care at Hoffmann-La-Roche Ltd in Basel . He also worked as Senior Manager Head of Business Intelligence for Swiss International Airlines Ltd. Christophe Cosme holds a Master's degree in Engineering, Automation from the IT\u2013 ENSISA (F). His branch-related experience covers pharmaceuticals, IT and the airline sector.","url":"https:\/\/www.dbi-services.com\/blog\/author\/christophe-cosme\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12195","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=12195"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/12195\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=12195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=12195"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=12195"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=12195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}