“If Your Only Tool Is a Hammer Then Every Problem Looks Like a Nail.” My hammer has been SSIS for most of the last 8 years. I have reluctantly used scripting language like .Net framework (C# and VB.Net) and even VBA and (don’t choke) Visual Basic 6.0. But I think that I have found a screwdriver for problems that really aren’t nail problems. That screwdriver is PowerShell. I am going to look at some interesting uses for PowerShell in the next few posts.
One of the challenges of using SSIS is licensing. You have to pay megabucks to install SQL Server on a machine if you are going to use SSIS. But what if the tasks you are working on don’t require the power of SSIS? What if all you need a particular server to do is to download a file from an FTP server? Or what if you need to shape a file for loading, removing columns and rows or sorting it? If you could do this on a server separate from your normal SSIS server, you can reserve that server for the heavy lifting of data validation and data loading.
PowerShell can handle quite a few simple ETL tasks. It may not be the fastest tool, but if the task is not in the critical path, perhaps it can be done offline on a second server.
Have you have had any experience using PowerShell for your ETL tasks? Please share your stories.