Passing SSIS variable to Powershell

Welcome Forums General PowerShell Q&A Passing SSIS variable to Powershell

Viewing 0 reply threads
  • Author
    Posts
    • #6153
      Participant
      Topics: 1562
      Replies: 0
      Points: 1
      Rank: Member

      by Mike777 at 2012-10-02 15:21:21

      Hi All,

      Please be kind. This is my first post and I am new to Powershell.
      I am executing a Powershell script from my SSIS package by using the Process Task transformation.
      I am trying to Pass a SSIS variable to Process Task so it will use the variable to download the correct file.
      The download from a static or hardcoded file name works fine if it is already in the PS script. It fails when I try to pass the
      variable to the script. Can anyone explain or show me how to pass a variable to a PS script using SSIS?
      I commented out several attempts with the ## sign.

      Thank you in advance very much.

      Here is my code:
      ##ananytics_ndw_DownloadSelectedHistoricWeather.ps1
      ##
      ## File being downloaded is the Historic Weather text file – file format (DS3505)
      ## This will down load a file that was created by the NCDC (National Climate Data Center) because it was
      ## manaully requested for a specifc STATE, YEAR on the weather website.
      ##
      CLEAR
      param(
      [string]SelectedFileName
      )

      $shell_app=new-object -com shell.application
      if(!$?){
      echo "Error Creating Shell Application"
      exit 99
      }
      $clnt = new-object System.Net.WebClient
      if(!$?){
      echo "Error Creating WebClient"
      exit 99
      }

      ## inside varible works fine
      ##set-variable -name newfilename -value "3816995904168dat.txt"
      ##$file = get-variable -value newfilename

      ## trying to get it to pass from the ssis task Execute proccess task to powershell is the problem

      $file = $ssis.variables["User];

      ##$file = Get-Variable -value $SelectedFileName
      ###$file = $ssis Variables SelectedFileName

      $url = "http://www1.ncdc.noaa.gov/pub/orders/" + "$file"

      $file_mypath = "x:\SQL\DATA\NDW\Weather\Historical\SelectedHistoricWeather" + "\$file"

      if(!$?){
      echo "Error Obtaining File Location"
      exit 99
      }
      $clnt.DownloadFile($url, $file_mypath)

      ## END OF CODE

      by coderaven at 2012-10-03 04:42:25

      It seem to me that you are not passing the parameter correctly. I would start looking here. I have no experience with SSIS jobs.

Viewing 0 reply threads
  • The topic ‘Passing SSIS variable to Powershell’ is closed to new replies.