This month’s tools help you streamline Windows PowerShell operations and use drag-and-drop functions to build extensions for Microsoft SQL Server Integration Services.
Windows PowerShell is a powerful tool for Windows administration. However, there’s often a high learning curve for creating your own reusable scripts—especially because there’s no built-in debugger for tracing the code. PowerWF Studio from Devfarm Software can help speed up script generation. It simplifies scripting by separating you from some of the syntactic details of the Windows PowerShell IDE.
PowerWF Studio claims that it “acts like PowerShell, looks like workflow.” In short, it lets you drag and drop script components and flow control items from a “toolbox” onto a design surface. Then you can set the parameters for each item, and run or debug the resulting Windows PowerShell script.
If you’re familiar with Visual Studio or the Business Intelligence Development Studio (BIDS), you’ll appreciate the similarity of the PowerWF Studio environment. Your task toolbox is in the left pane, your “code” and workflow in the center pane, and the properties and outline views in the right pane. It also displays Windows PowerShell at the bottom of the center pane so you can see your output or run ad hoc commands. If you don’t like the default layout, you can move the different interface components around and “pin” them in a new location. The interface supports multiple workflows open at once, so you can easily reference or copy between scripts to help speed your work.
The PowerWF Studio toolbox has a ton of different tasks for building your workflow. Most are wrappers to underlying Windows PowerShell commands that you’ll surely recognize. For example, there are activities related to Background Intelligent Transfer Services, tasks for reading and writing to files and databases, tasks for reading and writing Windows Management Instrumentation (WMI) information, tasks for Internet operations like HTTP GETs and POSTs, FTP transfers, sending e-mails via SMTP, and tasks for Active Directory integration.
If you’re familiar with Microsoft Workflow Foundation, you’ll also recognize the workflow canvas and options for flow control. You be able to drag and drop “while,” “for” and “for each” loops, as well as use conditional operators. You can create parallel executions, handle events and throw exceptions, as well.
As you build your Windows PowerShell workflow, the Outline view lets you quickly jump to a specific section to edit properties or add steps. PowerWF Studio also has debugging functionality. You can set break points at different steps within your workflow to verify state and isolate any issues before releasing your script into the wild.
There’s also a “Watch” view that lets you see the state of your parameters and variables as you debug. This helps you find issues and ensure the application state is as you expect. Then you can compile the workflow to ensure you’ve initialized all settings and correctly configured the flow of control. You can see which tasks you still need to configure, as they’ll be marked with a red exclamation point.
Similarly, each Windows PowerShell activity has a blue Windows PowerShell icon. This brings up a Windows PowerShell editor to examine the underlying task. You can also add parameters to let you pass data and maintain state within your Windows PowerShell workflow.
Once your script is compiled, debugged and tested, you’ll most likely want to deploy it. PowerWF Studio gives you a number of options for doing so. You can save it to a specified directory for either console or windowed execution. You can create a cmdlet, snap-in or Windows PowerShell module. You can even make it a reusable Toolbox Activity. You can compile it into a .NET assembly for distribution or use by other .NET projects, or save it as a Visual Studio Workflow solution if you want to extend or modify the underlying C# code. Other deployment options include publishing it to a workflow agent, scheduling it via the Task Scheduler or even exporting it to a PowerGUI or PowerScripter environment.
Pricing starts at $125 for PowerSE which is a script editor. PowerWF is $250 and includes PowerSE as part of the bundle. PowerWF Studio has a few useful add-ons for your Microsoft System Center deployment: PowerWF for SCOM is $499 and includes all PowerWF, PowerSE, and ability to create MP for SCOM; PowerWF for SCSM is $999 and includes all PowerWF, PowerSE, and the ability to create MP for SCOM; and PowerWF for System Center includes SCOM, SCSM, PowerSE, and PowerWF for $1250.
Microsoft SQL Server Integration Services (SSIS) give you drag-and-drop extract, transform and load (ETL) design and workflow. It includes a number of built-in tasks to get your data-processing systems up and running. One of the coolest features of SSIS (and most of the newer Microsoft server products), though, is its extensibility.
Capitalizing on that extensibility in SSIS, /n Software SQL Server SSIS Tasks extend the base functionality of the Microsoft product with a number of useful additional SSIS tasks written fully in managed .NET C# code. These integrate directly into your BIDS environment. /n Software SQL Server SSIS Tasks support both SSIS 2005 and 2008, so if you haven’t been able to update to the newer version of SSIS, you’re still in luck.
Once you’ve installed the product and added toolbox items to your BIDS task toolbox, drag and drop items to the SSIS design surface as you would any other task. Then configure them to meet your specific needs.
There are a number of tasks, including one to help you with bulk credit-card transaction processing. This connects to your payment merchant gateway. There are also three compression tasks to help you manage file archives: one for GZIP, one for ZIP and a ZCompress task for archives based on the LZ-compress algorithm. You also get a secure-FTP task that supports various proxies and firewall configurations. You can enable this for either SSL or SSH. There is an OpenPGP (Pretty Good Privacy) task that lets you encrypt, decrypt, sign and verify files.
There are multiple messaging tasks:
All those are useful for package notifications. There’s also an RSS task for consuming feeds, an Amazon Simple Storage Service (S3) task for sending and retrieving data from the Amazon cloud storage system, and an SSH task that lets you securely access a remote host and perform remote task execution. Finally, there are detailed secure e-mail tasks that support SMTPS, POPS and IMAPS for ensuring privacy in your e-mail communication.
The /n Software SQL Server SSIS Tasks package is priced by server and CPU: a one-CPU server license runs $249, two CPUs for $499 and four CPUs for $999. You can request a quote for other server and CPU configurations. There’s also a 30-day trial version. If you’re using Microsoft SSIS and looking for a few features that native tasks don’t support, the tasks provided by the /n Software product may fit the bill.
/n Software SQL Server SSIS Tasks