Execute PowerShell Scripts in SSIS Packages with Task Factory

Tyler Lynch

Published On: October 9, 2019

Categories: SSIS, Task Factory, PowerShell 0

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.

PowerShell Task Tweet - Feature Idea - PowerShell

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. 

PowerShell Task PowerShell Command View

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 Task Script Results View

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. (You can also view the Task Factory release notes here).

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! For more on how Applegate uses various Task Factory components to achieve a seamless data integration process, check out this case study.

Tyler is an Associate Technical Product Manager, assisting in the development and delivery of the SentryOne Data DevOps product portfolio. After working as a software developer for almost two years, he transitioned to Product Management with a desire to be more directly involved with SentryOne customers and to leverage the technical experience he's gained to understand the problems and solutions in the industry better. He is thrilled to be a part of the next generation that is committing to improving the lives of the Microsoft Data Professional.


Comments

SentryOne Monitor Ad