Execute PowerShell Scripts in SSIS Packages with Task Factory

As a product manager, I need to understand what pain points plague data professionals. Interacting with the customers I serve is an excellent way to accomplish this.

Earlier this summer, I had the opportunity to connect with SentryOne customer—and brilliant Applegate BI Developer—Matt Cushing. As a bright engineer and longtime Task Factory user, Mr. Cushing had an ingenious feature suggestion that would improve not only his experience using the software but also the experience of other users as well.

Task Factory provides great value where out-of-the-box SQL Server Integration Services (SSIS) functionality lacks—the inability to execute PowerShell natively in SSIS packages was a limitation. After a follow-up phone call to fully understand Mr. Cushing’s use case and the pain points this feature could address, a work item was logged and a beautiful Task Factory feature was born! So, what is it?

Task Factory’s PowerShell Task

When defining your ETL process, PowerShell script execution can be tricky to incorporate. It’s much easier to run PowerShell scripts directly in SSIS (rather than creating SQL Agent jobs that run PowerShell scripts using CmdExec, for instance). This is exactly what the PowerShell Task in Task Factory accomplishes. It’s powerful, convenient, and easy to use.

The PowerShell Task window is broken into 2 tabs—the PowerShell Command tab and Script Results tab.

The PowerShell Command tab includes a pane where you enter the script you want to execute, with full variable replacement support. You’re also able to capture responses generated by the script and save them in an Object datatype variable.

The Script Results tab is where you’re directed after testing a script, and you’ll be shown the execution output and objects returned.

PowerShell is an extremely powerful task automation tool. SSIS does not provide a native PowerShell script task, so utilizing Task Factory’s new PowerShell functionality will open the door to countless automation scenarios and save you from the hassle of configuring the native SSIS Execute Process Task to meet your needs.

Don’t believe me? Check out Microsoft’s documentation for a look at the steps needed to execute a PowerShell script in SSIS using native functionality.

Get Started with the PowerShell Task

We want everyone to get their hands on the latest version of Task Factory.

If you’re new to SentryOne, welcome! You can download a free Task Factory license that allows you to execute packages with Task Factory components within BIDS, SSDT, and Visual Studio forever—yeah, I said forever—here.

Already use Task Factory? Follow our Task Factory upgrade instructions to download and install the latest version.

After you’ve given the new PowerShell functionality a try, give me a shout on Twitter and let me know what you think. And don’t forget to thank Mr. Cushing (@SQLKohai) for the awesome Task Factory feature suggestion!