Passing SSIS variable to Powershell

This topic contains 0 replies, has 1 voice, and was last updated by Profile photo of Forums Archives Forums Archives 5 years, 3 months ago.

  • Author
    Posts
  • #6153

    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.

You must be logged in to reply to this topic.