Author Posts

January 1, 2012 at 12:00 am

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.