Friday, 21 August 2015

Deploying environments and variables with PowerShell (C: csv)

Case
I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?
Environment with variables














Solution
You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:
  1. Store values in (multi-)array
  2. Store values in database table
  3. Store values in CSV file
C: Store values in CSV file
This example works with two powershell files. The first only contains four parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a CSV file instead of an array or database table. You can of course still store the values in a central database and then export the records to a CSV file for the PowerShell script.

A list of all parameters:


  • SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
  • EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.
  • EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.
  • FilepathCsv contains the path of the CSV file. This example uses $PSScriptRoot to get the location of the current PowerShell file and then concatenates the filename of the CSV to it. You can also use a regular path like d:\folder\EnvironmentVariables.csv.


  • This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.


    #PowerShell: ParamsForGeneralEnvironmentDeploymentWithCsv.ps1
    #################################################################################################
    # Change source and destination properties
    #################################################################################################
    # Ssis
    $SsisServer ="."
    $EnvironmentFolderName = "Environments"
    $EnvironmentName = "Generic"
    
    # Path of CSV containing variables (you can also use format d:\file.csv)
    $FilepathCsv = "$PSScriptRoot\EnvironmentVariables.csv"
    
    # Execute deployment script
    . "$PSScriptRoot\GeneralEnvironmentDeploymentWithCsv.ps1" $SsisServer $EnvironmentFolderName $EnvironmentName $FilepathCsv
    
    


    Second file:


    #PowerShell: GeneralEnvironmentDeploymentWithCsv.ps1
    ################################
    ########## PARAMETERS ##########
    ################################ 
    [CmdletBinding()]
    Param(
        # SsisServer is required
        [Parameter(Mandatory=$True,Position=1)]
        [string]$SsisServer,
        
        # EnvironmentFolderName is required 
        [Parameter(Mandatory=$True,Position=2)]
        [string]$EnvironmentFolderName,
        
        # EnvironmentName is required
        [Parameter(Mandatory=$True,Position=3)]
        [string]$EnvironmentName,
        
        # FilepathCsv is required
        [Parameter(Mandatory=$True,Position=4)]
        [string]$FilepathCsv
    )
    
    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "==                                                                 Used parameters                                                                    =="
    Write-Host "========================================================================================================================================================"
    Write-Host "SSIS Server             :" $SsisServer
    Write-Host "Environment Name        :" $EnvironmentName
    Write-Host "Environment Folder Path :" $EnvironmentFolderName
    Write-Host "Filepath of CSV file    :" $FilepathCsv
    Write-Host "========================================================================================================================================================"
    
    
    #########################
    ########## CSV ##########
    #########################
    # Check if ispac file exists
    if (-Not (Test-Path $FilepathCsv))
    {
        Throw  [System.IO.FileNotFoundException] "CSV file $FilepathCsv doesn't exists!"
    }
    else
    {
        $FileNameCsv = split-path $FilepathCsv -leaf
        Write-Host "CSV file" $FileNameCsv "found"
    }
    
    
    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to SSIS server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;
    
    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
    
    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
    
    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
      Throw  [System.Exception] "Failed to connect to SSIS server $SsisServer "
    }
    else
    {
       Write-Host "Connected to SSIS server" $SsisServer
    }
    
    
    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]
    
    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
        # Catalog doesn't exists. The user should create it manually.
        # It is possible to create it, but that shouldn't be part of
        # deployment of packages or environments.
        Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
        Write-Host "Catalog SSISDB found"
    }
    
    
    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$EnvironmentFolderName]
    
    # Check if folder already exists
    if (-not $Folder)
    {
        # Folder doesn't exists, so create the new folder.
        Write-Host "Creating new folder" $EnvironmentFolderName
        $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
        $Folder.Create()
    }
    else
    {
        Write-Host "Folder" $EnvironmentFolderName "found"
    }
    
    
    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Create object for the (new) environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]
    
    # Check if folder already exists
    if (-not $Environment)
    {
        Write-Host "Creating environment" $EnvironmentName in $EnvironmentFolderName
    
        $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
        $Environment.Create()
    }
    else
    {
        Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
        # Optional: Recreate to delete all variables, but be careful:
        # This could be harmful for existing references between vars and pars
        # if a used variable is deleted and not recreated.
        #$Environment.Drop()
        #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
        #$Environment.Create()
    }
    
    
    ###############################
    ########## VARIABLES ##########
    ###############################
    $InsertCount = 0
    $UpdateCount = 0
    
    
    Import-CSV $FilepathCsv -Header Datatype,ParameterName,ParameterValue,ParameterDescription,Sensitive -Delimiter ';' | Foreach-Object{
     If (-not($_.Datatype -eq "Datatype"))
     {
      #Write-Host $_.Datatype "|" $_.ParameterName "|" $_.ParameterValue "|" $_.ParameterDescription "|" $_.Sensitive
      # Get variablename from array and try to find it in the environment
      $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$_.ParameterName]
    
    
      # Check if the variable exists
      if (-not $Variable)
      {
       # Insert new variable
       Write-Host "Variable" $_.ParameterName "added"
       $Environment.Variables.Add($_.ParameterName, $_.Datatype, $_.ParameterValue, [System.Convert]::ToBoolean($_.Sensitive), $_.ParameterDescription)
    
       $InsertCount = $InsertCount + 1
      }
      else
      {
       # Update existing variable
       Write-Host "Variable" $_.ParameterName "updated"
       $Variable.Type = $_.Datatype
       $Variable.Value = $_.ParameterValue
       $Variable.Description = $_.ParameterDescription
       $Variable.Sensitive = [System.Convert]::ToBoolean($_.Sensitive)
    
       $UpdateCount = $UpdateCount + 1
      }
     }
    } 
    $Environment.Alter()
    
    
    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null 
    Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount
    


    CSV file:



    CSV: EnvironmentVariables.csv
    Datatype;Parameter Name;Parameter Value;Parameter Description;Sensitive (true or false)
    String;MIS_STG_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to stage database;false
    String;MIS_HST_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to historical stage database;false
    String;MIS_DWH_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data warehouse database;false
    String;MIS_MTA_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to metadata database;false
    String;MIS_DM_ConnectionString;"Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";Connectionstring to data mart database;false
    String;FtpPassword; 53cr3t!;Secret FTP password;true
    String;FtpUser;SSISJoost;Username for FTP;false
    String;FtpServer;ftp://SSISJoost.nl;FTP Server;false
    String;FolderStageFiles;d:\sources\;Location of stage files;false
    Boolean;EnableRetry; true;Enable retry for Webservice Task;false
    Int16;NumberOfRetries;3;Number of retries for Webservice Task;false
    Int16;SecsPauseBetweenRetry;30;Number of seconds between retry;false
    


    New environment

    Existing environment





























    Download scripts

    Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.

    Deploying environments and variables with PowerShell (B: table)

    Case
    I want to deploy an environment with variables to the Integration Services Catalog. How do I do that?
    Environment with variables














    Solution
    You can't create them outside SQL Server Management Studio and then deploy them to the catalog. The environment isn't part of the ISPAC file. But it's possible to generate some TSQL code and then execute those Store Procedure calls on all environments of the DTAP.
    Or you can use PowerShell script to generate an environment with variables. This blog post shows three different options to use PowerShell to create environments:
    1. Store values in (multi-)array
    2. Store values in database table
    3. Store values in CSV file
    B: Store values in database table
    This second example works with two powershell files. The first only contains five parameters. The second file contains the real logic and is called by the first. A benefit of two separate files is that you can use the scripts for multiple environments, but all logic is stored in a single file. In this example the variables are stored in a table instead of an array. An array can't be used as parameter between two separate files.

    A list of all parameters:


  • SsisServer contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.
  • EnvironmentName contains the name of the environment. If the environment already exists then it just adds or updates variables. There are deliberately no deletes. but that's possible.
  • EnvironmentFolderName contains the foldername where the environment is located. If the folder doesn't exists, it will be created.
  • SqlServer contains the servername (and instance name) of the Sql Server that has a database with the table containing the variables. Note that this server should be reachable from all environments where you want to run this PowerShell script.
  • SqlDatabase contains the database name that has the table containing the variables. 

  • This example uses strings, Int16 and Boolean, but there are other datatypes like DataTime, Int32, Int64, etc.). The script contains a lot of feedback. If it's to excessive for you, you can skip a couple of Write-Host lines by adding a hashtag in front of it. For deploying it's not necessary to change anything in the second file.


    #PowerShell: ParamsForGeneralEnvironmentDeploymentWithTable.ps1
    #################################################################################################
    # Change source and destination properties
    #################################################################################################
    # Ssis
    $SsisServer ="localhost"
    $EnvironmentFolderName = "Environments"
    $EnvironmentName = "Generic"
    
    # SqlServer (config table)
    $SqlServer ="localhost"
    $SqlDatabase ="ssisjoost"
    
    # Execute deployment script
    . "$PSScriptRoot\GeneralEnvironmentDeploymentWithTable.ps1"  $SsisServer $EnvironmentFolderName $EnvironmentName $SqlServer $SqlDatabase
    



    Second file:


    #PowerShell: GeneralEnvironmentDeploymentWithTable.ps1
    ################################
    ########## PARAMETERS ##########
    ################################ 
    [CmdletBinding()]
    Param(
        # SsisServer is required
        [Parameter(Mandatory=$True,Position=1)]
        [string]$SsisServer,
        
        # EnvironmentFolderName is required 
        [Parameter(Mandatory=$True,Position=2)]
        [string]$EnvironmentFolderName,
        
        # EnvironmentName is required
        [Parameter(Mandatory=$True,Position=3)]
        [string]$EnvironmentName,
        
        # SqlServer is required
        [Parameter(Mandatory=$True,Position=4)]
        [string]$SqlServer,
        
        # SqlDatabase is required
        [Parameter(Mandatory=$True,Position=5)]
        [string]$SqlDatabase
    )
    
    clear
    Write-Host "========================================================================================================================================================"
    Write-Host "==                                                                 Used parameters                                                                    =="
    Write-Host "========================================================================================================================================================"
    Write-Host "SSIS Server             :" $SsisServer
    Write-Host "Environment Name        :" $EnvironmentName
    Write-Host "Environment Folder Path :" $EnvironmentFolderName
    Write-Host "Sql Server (for config) :" $SqlServer
    Write-Host "Database (for config)   :" $SqlDatabase
    Write-Host "========================================================================================================================================================"
    
    
    ###############################
    ########## VARIABLES ##########
    ###############################
    # Get variables from table
    
    # Load SqlServer.SMO assembly to get data out of SQL Server
    Write-Host "Connecting to SSIS server $SqlServer "
    Add-Type -AssemblyName "Microsoft.SqlServer.SMO, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
    
    # Create SQL Server object using integrated security 
    $SqlServerConf = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer
    
    # Check if connection succeeded
    if (-not $SqlServerConf)
    {
      Throw  [System.Exception] "Failed to connect to SQL server $SqlServer "
    }
    else
    {
       Write-Host "Connected to SQL server" $SqlServer
    }
    
    # Specify which database to use for the query 
    $SqlDatabaseConf = $SqlServerConf.Databases.Item($SqlDatabase)
    # Specify query to get parameters out of helper table
    $SqlQueryConf = "SELECT Datatype, ParameterName, ParameterValue, ParameterDescription, Sensitive FROM EnvironmentVariables"
    # Execute query an put the result in a table object 
    $TableConf = $SqlDatabaseConf.ExecuteWithResults($SqlQueryConf).Tables[0]
    
    Write-Host "Found" $TableConf.Rows.Count "variables in config table"
     
    
    ############################
    ########## SERVER ##########
    ############################
    # Load the Integration Services Assembly
    Write-Host "Connecting to SSIS server $SsisServer "
    $SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    [System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;
    
    # Create a connection to the server
    $SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
    
    # Create the Integration Services object
    $IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
    
    # Check if connection succeeded
    if (-not $IntegrationServices)
    {
      Throw  [System.Exception] "Failed to connect to SSIS server $SsisServer "
    }
    else
    {
       Write-Host "Connected to SSIS server" $SsisServer
    }
    
    
    #############################
    ########## CATALOG ##########
    #############################
    # Create object for SSISDB Catalog
    $Catalog = $IntegrationServices.Catalogs["SSISDB"]
    
    # Check if the SSISDB Catalog exists
    if (-not $Catalog)
    {
        # Catalog doesn't exists. The user should create it manually.
        # It is possible to create it, but that shouldn't be part of
        # deployment of packages or environments
        Throw  [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
    }
    else
    {
        Write-Host "Catalog SSISDB found"
    }
    
    
    ############################
    ########## FOLDER ##########
    ############################
    # Create object to the (new) folder
    $Folder = $Catalog.Folders[$EnvironmentFolderName]
    
    # Check if folder already exists
    if (-not $Folder)
    {
        # Folder doesn't exists, so create the new folder.
        Write-Host "Creating new folder" $EnvironmentFolderName
        $Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $EnvironmentFolderName, $EnvironmentFolderName)
        $Folder.Create()
    }
    else
    {
        Write-Host "Folder" $EnvironmentFolderName "found"
    }
    
    
    #################################
    ########## ENVIRONMENT ##########
    #################################
    # Create object for the (new) environment
    $Environment = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName]
    
    # Check if folder already exists
    if (-not $Environment)
    {
        Write-Host "Creating environment" $EnvironmentName in $EnvironmentFolderName
    
        $Environment = New-Object $SsisNamespace".EnvironmentInfo" ($Folder, $EnvironmentName, $EnvironmentName)
        $Environment.Create()
    }
    else
    {
        Write-Host "Environment" $EnvironmentName "found with" $Environment.Variables.Count "existing variables"
        # Recreate to delete all variables, but be careful:
        # This could be harmful for existing references between vars and pars
        # if a used variable is deleted and not recreated.
        #$Environment.Drop()
        #$Environment = New-Object $SsisNamespace".EnvironmentInfo" ($folder, $EnvironmentName, $EnvironmentName)
        #$Environment.Create()
    }
    
    
    ###############################
    ########## VARIABLES ##########
    ###############################
    $InsertCount = 0
    $UpdateCount = 0
    
    
    foreach ($Row in $TableConf)
    {
        # Get variablename from array and try to find it in the environment
        $Variable = $Catalog.Folders[$EnvironmentFolderName].Environments[$EnvironmentName].Variables[$Row.Item("ParameterName")]
    
        # Check if the variable exists
        if (-not $Variable)
        {
            # Insert new variable
            Write-Host "Variable" $Row.Item("ParameterName") "added"
            $Environment.Variables.Add($Row.Item("ParameterName"), $Row.Item("Datatype"), $Row.Item("ParameterValue"), $Row.Item("Sensitive"), $Row.Item("ParameterDescription"))
    
            $InsertCount = $InsertCount + 1
        }
        else
        {
            # Update existing variable
            Write-Host "Variable" $Row.Item("ParameterName") "updated"
            $Variable.Type = $Row.Item("Datatype")
            $Variable.Value = $Row.Item("ParameterValue")
            $Variable.Description = $Row.Item("ParameterDescription")
            $Variable.Sensitive = $Row.Item("Sensitive")
    
            $UpdateCount = $UpdateCount + 1
        }
    }
    $Environment.Alter()
    
    
    ###########################
    ########## READY ##########
    ###########################
    # Kill connection to SSIS
    $IntegrationServices = $null 
    Write-Host "Finished, total inserts" $InsertCount " and total updates" $UpdateCount
    



    The table create script and some insert statements to fill the table with sample data.


    --TSQL: CreateTableEnvironmentVariables.sql
    USE [ssisjoost]
    GO
    
    /****** Generate Table [dbo].[EnvironmentVariables]   ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[EnvironmentVariables](
     [Datatype] [varchar](50) NULL,
     [ParameterName] [varchar](50) NULL,
     [ParameterValue] [varchar](255) NULL,
     [ParameterDescription] [varchar](255) NULL,
     [Sensitive] [bit] NULL
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    -- Insert Test data
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_STG_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_STG;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to stage database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_HST_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_HST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to historical stage database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DWH_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DWH;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data warehouse database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_MTA_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_MTA;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to metadata database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'MIS_DM_ConnectionString', N'Data Source=.\sql2016;Initial Catalog=MIS_DM;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;', N'Connectionstring to data mart database', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpPassword', N'53cr3t!', N'Secret FTP password', 1)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpUser', N'SSISJoost', N'Username for FTP', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FtpServer', N'ftp://SSISJoost.nl', N'FTP Server', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'String', N'FolderStageFiles', N'd:\sources\', N'Location of stage files', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Boolean', N'EnableRetry', N'true', N'Enable retry for Webservice Task', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'NumberOfRetries', N'3', N'Number of retries for Webservice Task', 0)
    GO
    INSERT [dbo].[EnvironmentVariables] ([Datatype], [ParameterName], [ParameterValue], [ParameterDescription], [Sensitive]) VALUES (N'Int16', N'SecsPauseBetweenRetry', N'30', N'Number of seconds between retry', 0)
    GO
    


    New environment

    Existing environment





























    Download scripts

    Tip: if you keep the parameter names in your SSIS projects equal to the variable names in the environment, then you could automatically references them during deployment using this PowerShell script.
    Related Posts Plugin for WordPress, Blogger...