SSIS Project Deployment via Powershell - Project Parameters configured to use Env Variables

While I am a long time user of Stackoverflow, this is my first question.
I am working to deploy SSIS Projects via Powershell. I am a Senior .NET Developer not a DBA. I have very little familiarity with SSIS package deployment.

I have worked through several blog / tutorials to gain the understanding I now have but something is still amiss.

I believe I setup my SSIS package correctly. According to my understanding, the connection managers are setup appropriately. I posted a snapshot of my connection managers below. Please note the 'fx (project)' next to the connection manager descriptions.

.

I have two Powershell scripts. One that deploys the package and another that sets up the environment variables and project parameters.
The problem I am encountering has to do with the environment not being selected when the project is executed despite my setting up the relationship. Hence my project parameters do not see my environment variables.

I receive the following errors when the package is executed:

The parameter "FlatFileConnectionManager_ConnectionString" is configured to use an environment variable, but no environment has been selected. Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.

The parameter "LocalHostAdventureWorksDW2012_ConnectionString" is configured to use an environment variable, but no environment has been selected. Check the "Environment" checkbox and specify the environment to use, or specify a literal value for the parameter.

Powershell Deployment script:

Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$sqlInstance = "xxxxxxx"
$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$isNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

$catalogPwd = "xxxxxx"


$ssisServer = New-Object $ISNamespace".IntegrationServices" $sqlConnection


if ($ssisServer.Catalogs.Count -gt 0)             
{             
    $ssisServer.Catalogs["SSISDB"].Drop()             
}            


$catalog = New-Object $isNamespace".Catalog" ($ssisServer, "SSISDB" ,$catalogPwd  )
$catalog.Create()

$ssisCatalog = $ssisServer.Catalogs["SSISDB"]

$ssisFolderName = "PowerShell Demos"
$ssisFolderDescription = "Created with PowerShell"
$ssisFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($ssisCatalog, $ssisFolderName, $ssisFolderDescription)
$ssisFolder.Create()

$ssisProjectName = "TestProject"
[byte[]] $ssisProjectFile = [System.IO.File]::ReadAllBytes("C:\Users\xxxxx\Documents\Visual Studio 2010\Projects\DeployTest\TestProject\bin\Development\TestProject.ispac")
$ssisFolder.DeployProject($ssisProjectName, $ssisProjectFile)

Powershell Environment and Project Parameter setup script:

Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"

$sqlInstance = "xxxxxxx"
$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$ssisCatalog = "SSISDB"
$isNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

$environmentName = "UPMVariables"
$projectName = "TestProject"
$folderName = "PowerShell Demos"

$integrationServices = New-Object "$isNamespace.IntegrationServices" $sqlConnection

$catalog = $integrationServices.Catalogs[$ssisCatalog]

$folder = $catalog.Folders.Item($folderName)

$environment = $folder.Environments[$environmentName]

if (!$environment)
{
    Write-Host "Creating environment ..." 
    $environment = New-Object "$isNamespace.EnvironmentInfo" ($folder, $environmentName, "testDescription")
    $environment.Create()             
}

# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description

Write-Host "Adding environment variables ..." 
$eFlatFileConnectonString = $environment.Variables.Item("LocalHostAdventureWorksDW2012_ConnectionString")
if (!$eFlatFileConnectonString)
{

    $environment.Variables.Add("FlatFileConnectionManager_ConnectionString", [System.TypeCode]::String, "C:\newDestination\test.txt", $false, "FlatFile ConnectionString")

    $environment.Alter()

    $eFlatFileConnectonString = $environment.Variables.Item("LocalHostAdventureWorksDW2012_ConnectionString")        

}

$eAdvWorksConnectionString = $environment.Variables.Item("LocalHostAdventureWorksDW2012_ConnectionString")
if (!$eAdvWorksConnectionString)
{

     $environment.Variables.Add( "LocalHostAdventureWorksDW2012_ConnectionString", 
    [System.TypeCode]::String, "Data Source=xxxxxxx;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;User Id=xxxx;Password=xxxxx;", $false, "LocalHostAdventureWorksDW2012 ConnectionString")

    $environment.Alter()

    $eAdvWorksConnectionString = $environment.Variables.Item("LocalHostAdventureWorksDW2012_ConnectionString")
} 


$project = $folder.Projects[$projectName]

$ref = $project.References.Item($environmentName, $folderName)

if (!$ref)
{
    # making project refer to this environment
    Write-Host "Adding environment reference to project ..."
    $project.References.Add($environmentName, $folder.Name)
    $project.Alter() 
}

$projectVariable = $project.Parameters[$eAdvWorksConnectionString.Name]
$project.Parameters[$eAdvWorksConnectionString.Name].Set("Referenced", $eAdvWorksConnectionString.Name) 

$projectVariable = $project.Parameters[$eFlatFileConnectonString.Name]           
$project.Parameters[$eFlatFileConnectonString.Name].Set("Referenced", $eFlatFileConnectonString.Name)            
$project.Alter()

I hoping someone on Stackoverflow understands what is causing this issue. Thanks in advance for any help and best regards!

******* Clarification of Question...More Information *******

I understand how to manually deploy, create Sql Server Environment Variables and configure Project parameters to reference the Sql Server Environment Variables via SSMS. I am trying to understand how to do so via Powershell script with no manual intervention. And, while I may be mistaken, I believe it is possible to automate this deployment / setup according to the research I have done.

When the scripts I included with my question execute, the following things occur successfully.

• The SSIS project deploys successfully to SQL Server. Please see the following snapshot.

  

• The Environment Variables are present after the script runs. The Environment Variables can be seen by right clicking the Environment Variable object, in this case titled 'UPMVariables'. These Variables are present due to the following part of the Powershell script.

  $environment.Variables.Add( "LocalHostAdventureWorksDW2012_ConnectionString", [System.TypeCode]::String, "Data Source=xxxxxxxx;Initial Catalog=AdventureWorksDW2012;Provider=SQLNCLI11.1;User Id=xxxx;Password=xxxxxx;", $false, "LocalHostAdventureWorksDW2012 ConnectionString")

  $environment.Variables.Add("FlatFileConnectionManager_ConnectionString", [System.TypeCode]::String, "C:\newDestination\test.txt", $false, "FlatFile ConnectionString")

  $environment.Alter() $eAdvWorksConnectionString = $environment.Variables.Item("LocalHostAdventureWorksDW2012_ConnectionString") $eFlatFileConnectonString = $environment.Variables.Item("FlatFileConnectionManager_ConnectionString")

  

• Project Parameters are present, as they should be, after deployment. These Project Parameters are created during the SSIS Project creation.

  

• A relationship, between the Sql Server Environment Variables and the SSIS Project, should be established via the following code from the Powershell script I included with my question.

  $project.References.Add($environmentName, $folder.Name)

  $project.Alter()

• Here is the challenge and the reason for my question. When you right click the SSIS pack and click execute (see first picture below), the Environment Variable checkbox has to manually be checked before the package can use the Environment Variable values.

  

  

• Once the Environment Variable check box is manually clicked, the Sql Server Environment Variables become available to the SSIS Project Parameters. This is the step the script is not establishing.

  

• This is the step I am trying to automate as manually checking that Environment Variable checkbox can not be managed. These SSIS Packages are run via job scheduling.

• As an aside, one of the tutorials I read said you can generate a T-SQL script that establishes the relationship between Project Parameters and Sql Server Environment Variables by clicking the script button in the upper left of the 'Execute Package' dialogue. Please see the snapshot of the script and the 'Execute Package' dialogue below. We could execute the T-SQL script from VSTS as a final step but unfortunately running this script did not result in the checkbox being checked.

TSQL

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', 
@execution_id=@execution_id OUTPUT, @folder_name=N'PowerShell Demos', 
@project_name=N'TestProject', @use32bitruntime=False, @reference_id=1
Select @execution_id

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
@object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

I hope this additional information will provide greater insight into the problem I am facing. I sincerely appreciate any help! Thank you!

****** UPDATE 03/06/2018 ******

May be my expectations are errant? It is my understanding that I should be able to:

1. Create my SSIS project / package in SSDT (2012). This includes:

   A. Create the data flow task

   B. Create connection managers if needed

   C. Setup parameters with default values...and mark them required / sensitive if needed

   D. Ensure the created parameters are scoped correctly (i.e. project or package level)

2. Deploy the SSIS Package via Powershell script. This includes:

   A. Package deployment to SQL Server

   B. Create SQL Server environment variables

   C. Create a relationship between the project folder and the Sql Server environment variables

   D. Create a relationship between the created SQL Server environment variables and the scoped parameters created in SSDT

3. Run the package via a job scheduler with no concern for project parameters

My challenge is creating the relationship between SQL Server environment variables and scoped parameters. Looking back at the T-SQL I posted yesterday.

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', 
@execution_id=@execution_id OUTPUT, @folder_name=N'PowerShell Demos', 
@project_name=N'TestProject', @use32bitruntime=False, @reference_id=1
Select @execution_id

DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  
@object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

This is to setup logging for a package execution and then executing the package. Good stuff but it does not setup the scoped parameters to use SQL Server environment variables.
What I found since my last post is a T-SQL script that first sets parameters before executing the package.

I know you have to set the object_type to either 30 (package mode) or 20 (project mode) execution. That is simple enough.

Do you have to add Parameters and Values at the time of execution like the following?

@parameter_name=N'pSourceDir'
@parameter_value='c:\Temp'

This seems redundant considering we create scoped Parameters in SSDT with default values. Then Sql Server environment variables with values. This is what does not make sense to me and hence why I am confused.

I am hoping beyond hope someone will clarify this bit for me. Thanks again Stackoverflow!