Scripting Environments in SSIS

  • miguel1974

    SSC Enthusiast

    Points: 114

    Hi Jeff,

    Thank you so much for this post and the script. It saved me tons of time, for sure.

    I had to do 2 small changes in your script, as it wasn't working as I'd like:
    - Instead of several PRINT, I use a temporary table and SELECT the SQL code at the end. This will overcome the issue with the PRINT size limit. Copy the code and paste it in a new query.
    - When the environment variable type is "nvarchar", declare the local variable in the script as "nvarchar(1000)"

    Please see attached my version of your script.

    Thank you and keep up the great work,
    Miguel Oliveira

  • ichv

    SSC-Addicted

    Points: 449

    I had to make correction, too. Just want to emphasize that  use
     nvarchar   without lenth  leads to truncation of configured values to 1 symbol. So it is is not just improvement but necessity. 

    (SELECT statement that starts from line 49 of original script)

      SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type
            + CASE ev.type WHEN 'String' THEN ' (1000) ' ELSE '' END
            +'= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
            , [name] = ev.name
       INTO #env_var
       FROM [SSISDB].[catalog].[folders] f  
       INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id  
       INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id 
       WHERE (f.name = @folder) AND (e.name = @env);

  • TampaMike

    Grasshopper

    Points: 20

    Thanks, Jeff!  This is a great article!  Question, though.  Once you have duplicated your environment variables across servers, you still need to map each of your project/package parameters to those environment variables.  Do you know of an easy way to script out and copy the mappings of projects to environment variables? I have dozens of projects, each with a dozen or more mappings to environment variables. These projects need to be migrated to a new server. I would like to avoid the tedious task of remapping all of the variables if possible. Thanks!

  • Dave Pendleton

    Old Hand

    Points: 394

    ichv - Friday, March 24, 2017 7:00 AM

    I had to make correction, too. Just want to emphasize that  use
     nvarchar   without lenth  lids to truncation of configured values to 1 symbol. So it is is not just improvement but necessity. 

    (SELECT statement that starts from line 49 of original script)

      SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type
            + CASE ev.type WHEN 'String' THEN ' (1000) ' ELSE '' END
            +'= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
            , [name] = ev.name
       INTO #env_var
       FROM [SSISDB].[catalog].[folders] f  
       INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id  
       INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id 
       WHERE (f.name = @folder) AND (e.name = @env);

    You can simplify this further by just using sql_variant since that is what the stored procedures expect anyway. The procedure as-written also does not support environment variables with spaces in their names. This will fix both.

    SELECT [env_var] = @tab + @tab + ', @' + REPLACE([ev].[name], ' ', '_') + ' sql_variant = N'''
            + ISNULL(CONVERT(varchar(MAX), [ev].[value]), '<REPLACE_ME>') + '''',
        [name]  = [ev].[name]
      INTO  [#env_var]
      FROM  [ssisdb].[catalog].[folders] [f]
        INNER JOIN [ssisdb].[catalog].[environments] [e] ON [f].[folder_id] = [e].[folder_id]
        INNER JOIN [ssisdb].[internal].[environment_variables] [ev] ON [e].[environment_id] = [ev].[environment_id]
      WHERE ( [f].[name] = @folder ) AND ( [e].[name] = @env );

    The code that generates the procedure calls will have to be changed also:

          + @tab + ', @value=@' + REPLACE([ev].[name], ' ', '_') + @cr + @tab + @tab + ', @data_type=N''' + [ev].[type] + '''' + @cr + @tab

  • Phil Parkin

    SSC Guru

    Points: 244733

    I enjoyed reading this post again.

    It's somewhat disappointing that we're still in a position where it's nearly impossible to have SSISDB environment variables and their mappings to project parameters scripted in such a way that it can run as part of a CI process ... an SSISDB post-deployment script.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • corey lawson

    Hall of Fame

    Points: 3731

    Hmm... in my last job where I did SSIS work, we had .dtsConfig files with database connections for all our database servers and databases, one .dtsConfig file/database. They're stored in the same local folder, with the same file name (say, c:\SSISConfigs) on all the SSIS servers - dev, it, prod, etc. So the config files to AdventureWorks were named just that - "AdventureWorks.dtsConfig", not "AdventureWorksDev.dtsConfig". The .dtsConfig file name needs to be the same regardless of environment though, since the package can't selectively load from different .dtsConfig files on its own; the important information (server name, actual database name, trusted security vs username/password, etc) is within the .dtsConfig file anyways.

    As developers, we all have our local copies (for the dev and test servers, of course) in the same folder. The Server gods do not grant developers file system access to the SSIS servers, so any secret sauce stored in those .dtsConfig files remains protected, at least from us - developers can't copy the prod versions to their local computers. We have an in-house application that we use to push our SSIS packages (by way of a proxy account) to our SSIS servers. [yes, malevolent SSIS developer could deploy a package to prod that copies the prod .dtsConfig files off that SSIS server...]

    As SSIS package developers, we also use flavors of those .dtsConfig files locally, stored in the same local directory structure. By doing this, we can now seamlessly deploy SSIS packages to different servers, whether deployed in file system or SSIS database, and it all Just Works with 0 post-deployment configuration, since the SSIS packages are looking for .dtsConfig files in the same directory structure. (remember, SSIS packages do not really do relative folder paths...for anything. That's why BIDSHelper has its "fix relative paths" functionality).

    Our deploy tool also freed the system admins from having to set up their system jobs to get the package configurations right in the DTEXEC command lines, since the packages themselves already had that built-in to them.

    Since Visual Studio caches the configuration file(s) information when the package is initially opened anyways, I could live with "open package with dev flavor of .dtsConfig files, make changes, close project, copy IT/QA/etc. .dtsConfig files to C:\SSISConfigs, reopen project & package" cycle.

    Previous experience working with system admins to get production configurations just right was always a frustrating time sink for all involved. Especially for trying to write the deployment/configuration steps for the system admins to try and follow.

    YMMV.

  • Phil Parkin

    SSC Guru

    Points: 244733

    corey lawson - Friday, March 2, 2018 10:04 AM

    Hmm... in my last job where I did SSIS work, we had .dtsConfig files with database connections for all our database servers and databases, one .dtsConfig file/database. They're stored in the same local folder, with the same file name (say, c:\SSISConfigs) on all the SSIS servers - dev, it, prod, etc. So the config files to AdventureWorks were named just that - "AdventureWorks.dtsConfig", not "AdventureWorksDev.dtsConfig". The .dtsConfig file name needs to be the same regardless of environment though, since the package can't selectively load from different .dtsConfig files on its own; the important information (server name, actual database name, trusted security vs username/password, etc) is within the .dtsConfig file anyways.

    As developers, we all have our local copies (for the dev and test servers, of course) in the same folder. The Server gods do not grant developers file system access to the SSIS servers, so any secret sauce stored in those .dtsConfig files remains protected, at least from us - developers can't copy the prod versions to their local computers. We have an in-house application that we use to push our SSIS packages (by way of a proxy account) to our SSIS servers. [yes, malevolent SSIS developer could deploy a package to prod that copies the prod .dtsConfig files off that SSIS server...]

    As SSIS package developers, we also use flavors of those .dtsConfig files locally, stored in the same local directory structure. By doing this, we can now seamlessly deploy SSIS packages to different servers, whether deployed in file system or SSIS database, and it all Just Works with 0 post-deployment configuration, since the SSIS packages are looking for .dtsConfig files in the same directory structure. (remember, SSIS packages do not really do relative folder paths...for anything. That's why BIDSHelper has its "fix relative paths" functionality).

    Our deploy tool also freed the system admins from having to set up their system jobs to get the package configurations right in the DTEXEC command lines, since the packages themselves already had that built-in to them.

    Since Visual Studio caches the configuration file(s) information when the package is initially opened anyways, I could live with "open package with dev flavor of .dtsConfig files, make changes, close project, copy IT/QA/etc. .dtsConfig files to C:\SSISConfigs, reopen project & package" cycle.

    Previous experience working with system admins to get production configurations just right was always a frustrating time sink for all involved. Especially for trying to write the deployment/configuration steps for the system admins to try and follow.

    YMMV.

    OK, but what point are you trying to make?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • jordan.jeffrey

    SSC Veteran

    Points: 297

    Corey, I understand what you're saying.  However, the scenario you're talking about here is using configuration files and SSIS Environments and Environment variable, or the Project deployment models.  These we're meant to be an improvement over using config files.  Can you still deploy you packages the old way and use configuration files?  Yes, that works perfectly fine but there are several advantages to using the project deployment model and SSIS environment/variables.

    Thanks,
    Jeff

  • jordan.jeffrey

    SSC Veteran

    Points: 297

    Thanks Phil!  I'm glad you enjoyed it.

  • Mohammad Hoque

    SSC Journeyman

    Points: 75

    Absolutely great scripts . However if we add the following code we can also scripts out the SSIS package local parameter reference to environmental variables

    SET @sql += @tab + '/*************************************************************' + @cr;

       SET @sql += @tab + @tab + 'References Package Variable to Enviromental variables' + @cr;
       SET @sql += @tab + '**************************************************************/' ; 
       PRINT @sql;

       /* Generate the variable creation */
       SELECT [cmd] = @tab + 'RAISERROR(''Referencing variable: ' + p.parameter_name + ' ...'', 10, 1) WITH NOWAIT;' + @cr
                  + @tab + 'EXEC @ReturnCode = [SSISDB].[catalog].[set_object_parameter_value]' + @cr
                  + @tab + @tab + '@object_type=' + CONVERT(nvarchar(5),p.object_type) + @cr
                  + @tab + @tab + ', @parameter_name=N''' + p.parameter_name + '''' + @cr
                                            + @tab + @tab + ', @object_name=N''' + p.object_name + '''' + @cr
                                            + @tab + @tab + ', @folder_name=N''' + f.name + '''' + @cr
                  + @tab + @tab + ', @value_type=N''' + p.value_type + '''' + @cr
                                            + @tab + @tab + ', @parameter_value=N''' + p.referenced_variable_name + '''' + @cr
                  + @tab + 'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;' + @cr
               , [parameter_id] = p.parameter_id
       INTO #cmd1

            FROM [SSISDB].[internal].[object_parameters] p
            inner join [SSISDB].[internal].[projects] p2 on p.project_id = p2.project_id
            inner join [SSISDB].[internal].[folders] f on p2.folder_id = f.folder_id
            WHERE p.Value_type='R' and f.name = @folder

       /*Print out the variable creation procs */
       WHILE EXISTS (SELECT TOP 1 1 FROM #cmd1)
       BEGIN
          SELECT TOP 1 @sql = cmd, @parameter_id = parameter_id FROM #cmd1 ORDER BY parameter_id;
          PRINT @sql;
         
          DELETE FROM #cmd1 WHERE parameter_id = @parameter_id;
       END;. 

    Thank You

  • jordan.jeffrey

    SSC Veteran

    Points: 297

    Mohammad,

    Thanks for the reply and I appreciate the code improvement.  I'll have to see if I can incorporate your suggestion and update the scripts in the article.  You are, of course, free to use and modify the scripts to fit your needs.

    Thanks,

    Jeff

  • Octavian C.

    SSC Veteran

    Points: 224

    jordan.jeffrey - Friday, March 2, 2018 7:35 PM

    Mohammad,

    Thanks for the reply and I appreciate the code improvement.  I'll have to see if I can incorporate your suggestion and update the scripts in the article.  You are, of course, free to use and modify the scripts to fit your needs.

    Thanks,

    Jeff

    I faced exactly the same issue with the string variables being truncated. I have not tested all the scenarios but from what i see the definition of the values in the stored procedure call is expecting sql_variant. It might make better sense to define all environmental variable values as sql_variant?

    SO I would suggest instead:
    SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type + '= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + '''' ...

    to have
    SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' sql_variant= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''

    I have not tested with the other data types other than strings. The other option is to specify a long enough (n)varchar variable.
    HTH

  • Marek Grzymala

    SSCommitted

    Points: 1928

    Dave Pendleton - Friday, June 2, 2017 12:35 PM

    ichv - Friday, March 24, 2017 7:00 AM

    I had to make correction, too. Just want to emphasize that  use
     nvarchar   without lenth  lids to truncation of configured values to 1 symbol. So it is is not just improvement but necessity. 

    (SELECT statement that starts from line 49 of original script)

      SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + ev.base_data_type
            + CASE ev.type WHEN 'String' THEN ' (1000) ' ELSE '' END
            +'= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
            , [name] = ev.name
       INTO #env_var
       FROM [SSISDB].[catalog].[folders] f  
       INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id = e.folder_id  
       INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id 
       WHERE (f.name = @folder) AND (e.name = @env);

    You can simplify this further by just using sql_variant since that is what the stored procedures expect anyway. The procedure as-written also does not support environment variables with spaces in their names. This will fix both.

    SELECT [env_var] = @tab + @tab + ', @' + REPLACE([ev].[name], ' ', '_') + ' sql_variant = N'''
            + ISNULL(CONVERT(varchar(MAX), [ev].[value]), '<REPLACE_ME>') + '''',
        [name]  = [ev].[name]
      INTO  [#env_var]
      FROM  [ssisdb].[catalog].[folders] [f]
        INNER JOIN [ssisdb].[catalog].[environments] [e] ON [f].[folder_id] = [e].[folder_id]
        INNER JOIN [ssisdb].[internal].[environment_variables] [ev] ON [e].[environment_id] = [ev].[environment_id]
      WHERE ( [f].[name] = @folder ) AND ( [e].[name] = @env );

    The code that generates the procedure calls will have to be changed also:

          + @tab + ', @value=@' + REPLACE([ev].[name], ' ', '_') + @cr + @tab + @tab + ', @data_type=N''' + [ev].[type] + '''' + @cr + @tab

    Using that version all integer variables declared as sql_variant get assigned a value like so: sql_variant = N'4' and consequently calling create_environment_variable with @value as the parameter results in a conversion error (not compatible with the data type of the 'Int32').

    The solution is to either manually edit all your int variable declarations in the scripted output and remove the N'' (sql_variant = 4) or perhaps improve the ichv's version to declare each string variable using its original length, rather than giving all of them length of 1000:

    SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' '  + ev.base_data_type  + CASE ev.type
    WHEN 'String' THEN '('+CAST(LEN(ISNULL(CONVERT(VARCHAR(MAX), ev.value), '<REPLACE_ME>')) AS VARCHAR)+')'
    ELSE '' END +'= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
     , [name] = ev.name
    INTO #env_var
    FROM [SSISDB].[catalog].[folders] f   
    INNER JOIN [SSISDB].[catalog].[environments] e ON f.folder_id =  e.folder_id   
    INNER JOIN [SSISDB].[internal].[environment_variables] ev ON e.environment_id = ev.environment_id 
    WHERE (f.name = @folder) AND (e.name = @env);

  • lapras_dewgong

    SSC Enthusiast

    Points: 164

    Great way to script those environments. Thank you very much.

  • alainmartin00

    Newbie

    Points: 8

    Hello,

    I just use it but need to modify it like this.

      SELECT [env_var] = @tab + @tab + ', @' + ev.name + ' ' + REPLACE(ev.base_data_type,'nvarchar','nvarchar(4000)') + '= N''' + ISNULL(CONVERT(varchar(max), ev.value), '<REPLACE_ME>') + ''''
            , [name] = ev.name

    Thanks for the good work!

Viewing 15 posts - 16 through 30 (of 33 total)

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