SSIS Running Powershell with Custom Functions (psm1's)

user_9324260 0
Viewed 202

Let me start by saying I'm not new to Powershell, but I am new to custom functions.

So I have created a custom function (with a single cmd-let) to handle logging to our db. It works like a charm. Let's call it write-myfunction.

At first, It worked when I ran it in ISE but then wouldn't work on the scheduler. I fixed that by adding the hosted folder with the PSM1 to my PATH powershell variables. Boom. Now it works on scheduler. Great. Wonderful. Moving on.

Problem NOW is that we're running SSIS (Same GA User) and having SSIS call the powershell script, it doesn't seem to be finding my psm1. I get "write-myfunction isn't a valid name of a cmd-let blahblah"

Worth Noting: I have my function folder hosted on our network share in a place with totally open permissions (I have it backing up on the daily and alarms, so I'll get alarm bells if something in that folder changes)

Questions!

  1. Is keeping a central repository of custom modules on some other server a smart move? Should I just deploy a copy of that folder into c:\program files\powershell to every computer instead via GP?
  2. What am I missing on this SSIS thing? I figure if I run as an AD GA account, it should have access to the folder with the script, and the fact that the SSIS package is running on the local system that I edited the PATH variables to encompass my hosted modules folder right? So.. like.. what do?

*edit Here is where I was able to fix the first issue: PSModulePath addition

And here's me confirming I can GCI to the hosted files in a powershell window

enter image description here

ssis powershell function sql-server
1 Answers

1: Yes! Keep your modules somewhere central that gets backed up, and consider adding them to a git repository to track changes over time. Be aware of the permissions if you share the location with others!

Also note that if the remote server is not a "trusted location" for powershell, you may have to manage your execution policy settings and/or Unblock-File before running your scripts.

2: (Assuming you are running your SSIS packages on a server) SSIS generally runs commands locally on the server you're connected to. You'll want to make sure that server can connect to your shared location and that the service account running the server instance has access.

What does your SSIS task look like? I would use an Execute Process task with:

Executable: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Arguments:  -ExecutionPolicy Unrestricted -command '\\path\to\script.ps1'

Where script.ps1 contains:

# Import your custom modules:
Import-Module '\\path\to\module.psm1'

# Run your custom functions
Write-MyFunction -foo X -bar Y

Check out this write-up for more details on the package itself

If you continue to have issues running the script, try saving the script locally on the server. Windows has some restrictions on second-hop remoting that can make accessing file shares through SSIS difficult.

user_74118850
Related