Run SQL Server 2012 package with Project environment

• manamana

  Grasshopper

  Points: 23

  More actions

  July 15, 2013 at 12:01 pm

  #296395

  Hi experts,

  I try to launch SSIS 2012 packages stored into SSISDB with PowerShell. In my SSISDB, I have several Project environements (DEV,PROD,...); I would like to change project environnement from my PS script but I can't.

  function launch_package()

  {

  $instanceName = "MYSERVER"

  $SSISDBFolderName = "MyFolder"

  $projectName = "MyProject"

  $packageName = "MyPackage.dtsx"

  $SSISEnvironmentName = "DEV"

  # import SQL Server module

  Import-Module SQLPS -DisableNameChecking

  # load the IntegrationServices assembly

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

  $connectionString = "Data Source=$instanceName;Initial Catalog=master;Integrated Security=SSPI;"

  $conn = New-Object System.Data.SqlClient.SqlConnection $connectionString

  $SSISServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $conn

  $SSISDB = $SSISServer.Catalogs["SSISDB"]

  $SSISDBFolder = $SSISDB.Folders[$SSISDBFolderName]

  $SSISDBAssoc = $SSISDBFolder.Environments[$SSISEnvironmentName]

  # Here is the issue :

  $SSISDBAssoc.References.Add($SSISEnvironmentName)

  $SSISDBAssoc.Alter()

  $SSISDBAssoc.Projects[$projectName].Packages[$packageName].Execute($false, $null)

  }

  Error message is : You cannot call a method on a null-valued expression.

  Any one can help ?