Parameterised powershell script through SSIS Execute Process Task (with UNC)

Question

I've done varied amounts of research into how to run a parameterised PS script from SSIS. I am having issues getting a parameterised script running. PS script is as follows, if I hard code the parameters into the script it behaves as expected:

Param ([string]$filepath,[string]$filename)

$Path = $filepath
$InputFile = (Join-Path $Path $filename)
$Reader = New-Object System.IO.StreamReader($InputFile)

While (($Line = $Reader.ReadLine()) -ne $null) {
    If ($Line -match 'FILE\|([^\|]+)') {
        $OutputFile = "$($matches[1]).txt"
    }
    Add-Content (Join-Path $Path $OutputFile) $Line
}

Running in SSIS execute process task, I am trying to build the Arguments command via an expression as follows:

"-ExecutionPolicy ByPass -File " + @[User::vPSScriptLocation] + " " + @[User::vFilePath]+ " "+ @[User::vFileName]

Evaluating the expression gives the following:

-ExecutionPolicy ByPass -File \\WorkDirectory\Script.ps1 \\transfer datafile.data

Upon execution, the task fails. The .ps1 is deleted from the work directory and SSIS gives the following error code:

Error: 0xC0029151 at Execute powershell script, Execute Process Task: In Executing "C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe" "-ExecutionPolicy ByPass -File \\WorkDirectory\Script.ps1 \\transfer datafile.data" at "", The process exit code was "-196608" while the expected was "0".

Looks like it's getting an empty string where it shouldn't? Any pointers appreciated.

So your process is supposed to read the file "\\transfer\datafile.data"? That is not a valid UNC path. Does your script work if you run the code shown in the error? It also looks like it is passing all arguments as one string but that might just be how the error is written. Will have to check if -File supports UNC. This question got your error trying to use a UNC path to the file. — Matt
– Matt, Commented May 4, 2017 at 12:18
It's supposed to read \\WorkDirectory\Script.ps1, which again isn't UNC. I've also tried passing the arguments in speech marks but get the same result. It could be the UNC, although it is very strange behaviour that it deletes the ps1 file from the read location. — TJB
– TJB, Commented May 4, 2017 at 14:00
"\\WorkDirectory\Script.ps1" isn't a valid path so I think powershell does not know what you are trying to run. Is that supposed to be a relative path or something? I'll bet you do not have a server called WorkDirectory which is what PowerShell would be looking for. The same issue should happen in your script, which is what I was trying to point out before where you are building a similar path that isnt valid — Matt
– Matt, Commented May 4, 2017 at 14:42
It has a proper server name, \\WorkDirectory is just illustrative. Like I said in the original question, if I run this script in PS ISE with values assigned to the parameters it works OK. I guess this is lack of compatibility between SSIS and UNC paths for this task. — TJB
– TJB, Commented May 4, 2017 at 14:50
It has a proper server name, \\WorkDirectory is just illustrative. Ok then. That is fine but you should use proper named examples. Since malformed paths could be part of your issue it made me focus on that part of your question i.e. "\\Server\Scripts\Script.ps1". Do you real paths have spaces in them? — Matt
– Matt, Commented May 4, 2017 at 14:53

TJB · Accepted Answer · 2017-05-05 09:43:02Z

OK so as it appears I could not call a UNC path to execute this using an Execute Process Task, I decided to execute this within a Script Task with a reference added to System.Management.Automation which allowed me to create a PowerShell instance. This is far from my ideal solution as I really wanted to call a .ps1 file, but looks like this is my only solution given I need to use a UNC path.

I build the PS script with my Dts variables and then executed it within the instance, which achieved the desired result:

    public void Main()
    {
        string filepath = Dts.Variables["User::vUNCPath"].Value.ToString();
        string filename = Dts.Variables["User::vFileName"].Value.ToString();
        string searchterm = Dts.Variables["User::vSearchTerm"].Value.ToString();
        bool fireAgain = true;

        // Build powershell script
        string script = "$Path = \""+filepath+"\";"+
                        "$InputFile = (Join-Path $Path \""+ filename+"\");" +
                        "$Reader = New-Object System.IO.StreamReader($InputFile);" +
                         "While (($Line = $Reader.ReadLine()) -ne $null) {" +
                        "If ($Line -match '"+searchterm+"') { "+
                        "$OutputFile = \"$($matches[1]).txt\"};" + 
                        "Add-Content (Join-Path $Path $OutputFile) $Line}";

        Dts.Events.FireInformation(0, "Info", "Powershell script built: " + script, String.Empty, 0, ref fireAgain);

        try
        {
            // Create instance to run script
            using (PowerShell psinstance = PowerShell.Create())
            {
                //Assign built script to this instance
                psinstance.AddScript(script);

                //Run powershell script
                psinstance.Invoke();

            }

            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception ex)
        {
            Dts.Events.FireError(0, "Error", ex.Message, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
    }

Ronny Kaufmann · Accepted Answer · 2017-05-05 11:21:21Z

0

If you're running your script as a ps1-File with a param-block like this, your execution-call should name the parameters by their name:

"-ExecutionPolicy ByPass -File " + @[User::vPSScriptLocation] + " -filepath " + @[User::vFilePath]+ " -filename "+ @[User::vFileName]

This should do it, if you use a valid filepath and filename.

If it doesn't work, please try to write your script as a function and try it in a powershell-console. Your script as a function looks like this:

function SearchLines
{
  Param (
    [string]$filepath,
    [string]$filename
  )
  $Path = $filepath
  $InputFile = (Join-Path $Path $filename)
  $Reader = New-Object System.IO.StreamReader($InputFile)
  While (($Line = $Reader.ReadLine()) -ne $null) {
     If ($Line -match 'FILE\|([^\|]+)') {
        $OutputFile = "$($matches[1]).txt"
    }
    Add-Content (Join-Path $Path $OutputFile) $Line
  }
}

Usage:

SearchLines -filepath "\\your\unc\path\here" -filename "filename.txt"

If this don't work for you, please let us know which error you got.

Thx.

UPDATE:

Based on your comments, i wrote your function new in the hope, it meets your requirements as close as possible. The function now looks like this:

function SearchLines
{
  Param (
    [string]$InputFile
  )
  $FileContent = Get-Content $InputFile
  foreach($Line in $FileContent)
  {
    If ($Line -match 'FILE\|([^\|]+)') 
    {
       $OutputFile = "$($matches[1]).txt"
    }    
  Add-Content -Path $OutputFile -Value $Line
  }
}

Usage:

SearchLines -InputFile c:\your\path\to\your\file.log

This function creates for every line in your given file a new file in the actual folder named what is written in the line. The Cmdlet Join-Path simply adds the two strings together, without any check for plausibility. That's why you can simply commit the full path to your file instead of the path and the file in separate parameters.

If you need the path of the inputfile to set it for your outputfiles, you can get it with these lines:

$tmpPath = Get-Childitem $InputFullPath
$Path = $tmpPath.Directory.FullName

Because you didn't explained what exactly this script should do, i hope you can use this to get what you wanted.

Greetings

edited May 5, 2017 at 11:21

answered May 4, 2017 at 13:59

Ronny Kaufmann

4862 silver badges7 bronze badges

7 Comments

TJB Over a year ago

First attempt gives the same error as the original question. Second in console returns the below, which is probably my lack of knowledge on powershell... The term 'SearchLines' is not recognized as the name of a cmdlet, function, scr ipt file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

Ronny Kaufmann Over a year ago

Copy and paste the complete sourcecode from my answer in a powershell console, before you try to use it. If you using it in your scriptfile, you can copy it to the beginning of your scriptfile, after the param-block.

TJB Over a year ago

OK using your it's stating "Exception calling ".ctor" with "1" argument(s): Could not find file "\\your\unc\path\here\-" " At line 9 char 23 $Reader = New-Object <<<< System.IO.StreamReader($InputFile)

Ronny Kaufmann Over a year ago

The error message says it can't find your input file. Perhaps Join-path works different from what you think it does. I'm on my way home at the moment, reply later for a more useful answer. What should your script do in the first place?

Ronny Kaufmann Over a year ago

I've added some to my solution - Hope it fits to your needs

|

Collectives™ on Stack Overflow

Parameterised powershell script through SSIS Execute Process Task (with UNC)

2 Answers 2

Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related