Passing parameters to an ssis package

Welcome Forums General PowerShell Q&A Passing parameters to an ssis package

Viewing 2 reply threads
  • Author
    Posts
    • #224205
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: Major Contributor

      I want to pass a parameter to an ssis package and execute it. When I run the script below, I get ‘You cannot call a method on a null-valued expression’.  What am I doing wrong?

      *The process runs ok, executing the package, if I skip adding the parameter.

      [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+
      "Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL") | Out-Null
      $sqlConnectionString = `
      "Data Source=" + $SSISServer + ";Initial Catalog=master;Integrated Security=SSPI;"
      $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
      $SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
      $SSIS = New-Object $SSISNamespace".IntegrationServices" $sqlConnection
      $catalog = $SSIS.Catalogs["SSISDB"]
      $ssisPackage = $catalog.Folders[$FolderName].Projects[$ProjectName].Packages[$PackageName]
      $ssisParameter = "NetworkInternalCode"
      $ssisParameterValue = "alpa"
      $ssisPackage.Parameters[$ssisParameter].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$ssisParameterValue);
      $ssisPackage.Alter()
      $ssisPackage.Execute("false", $null)
      
      

       

      • This topic was modified 3 weeks, 5 days ago by Iain.
      • This topic was modified 3 weeks, 5 days ago by Iain.
    • #224211
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: Major Contributor

      Full exception message is:

      
      Exception : System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
      at CallSite.Target(Closure , CallSite , Object , ParameterValueType , Object )
      at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
      at System.Management.Automation.Interpreter.DynamicInstruction`4.Run(InterpretedFrame frame)
      at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
      
      
    • #224259
      Participant
      Topics: 48
      Replies: 194
      Points: 767
      Helping Hand
      Rank: Major Contributor

      The problem I was having was due to the parameters being at Project and not Package level.

      This works;

       

      
      $sqlConnectionString = `
      “Data Source=” + $SSISServer + “;Initial Catalog=master;Integrated Security=SSPI;”
      $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
      $SSISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
      $SSIS = New-Object $SSISNamespace”.IntegrationServices” $sqlConnection
      $catalog = $SSIS.Catalogs[“SSISDB”]
      $ssisProject = $catalog.Folders[$FolderName].Projects[$ProjectName]
      $ssisProject.Parameters[“NetworkInternalCode”].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal, “alpa”)
      $ssisProject.Parameters[“ControlId”].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal, “alpa”)
      $ssisPackage = $ssisProject.Packages[$PackageName]
      $ssisPackage.Alter()
      $ssisPackage.Execute(“false”, $null)
      
      
Viewing 2 reply threads
  • You must be logged in to reply to this topic.