You need to:

  • Export your SSIS Environments (Variables)
  • Replicate it on your new server
  • Assign these variables to your new project

SSIS Catalog is not complex, but we don’t really want to spend our time to copy manually our configuration from one server to another one.
Often, we don’t have access to the production environment.
The SQL Code below helps you to create a script and automate all these very ennuying steps.

I found the base line of this code on internet:
Jeff Jordan did a really nice work.

His code helps you to:

  • Create your SSIS Catalog folder
  • Create your SSIS Environment
  • Add SSIS Variables in your SSIS Environment

I added some extra steps:

  • Assign your SSIS Environment to your SSIS Project
  • Assign your SSIS Environment Variables to your SSIS Project Variables
  • + Some correction
  • + A super nice ASCII message when everything is done.. Who can help you to have a smile and maybe a better life. (You should trust the power of smiling 🙂 )

How does it works?

  1. Copy the SQL Script below and paste it into a new blank query in SSMS (Management studio) –> Connected to your existing server (The server who has your environment already configured / Not the new one)
  2. Configuration: Change the XXXX value in the query
    1. @folder : Folder Name (Your configured server)
    2. @env : Environment Name (Your configured server)
    3. @destination_folder_name : Folder Name (Your new server)
    4. @destination_environment_name : Environment Name (Your new server)
    5. @destination_project_name : Project Name (Your new server)
  3. Run your query and copy the result into a new blank query in SSMS. –> Connected to your new server
  4. FIRST OF ALL: You should deploy your SSIS Project!! With the Deployment wizard or Visual Studio
  5. Change the configuration information into the generated query
  6. Run the query
  7. Smile 🙂

Pin It on Pinterest

Share This