Yet another way to ETL without Integration Services?
Jeffrey McManus recently wrote about database queries with PowerShell – a small script that lets you query a SQL data store. This is really powerful. Rather than context switch into SQL Express (or TOAD, or your other favourite administration tool,) you can do what you need from PowerShell.
It goes even further, though. A lot of PowerShell's built-in commands have a set-oriented flavour: Where-Object, Select-Object, Group-Object, and Sort-Object. I also blogged about a set intersection script here: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx. After spending a ton of time in Oracle databases during an internship at General Electric, I remember always wishing that DIR supported a WHERE clause. Well, with PowerShell, it does!
One thing Jeffrey's post doesn't fully highlight is that the .NET Framework can also return fully structured objects that represent the results of your query. If your SELECT query returns a recordset with Name and Address columns, you can get back objects with Name and Address properties. With that, you can slice and dice the results even further in the shell.
PS D:\Temp> Invoke-SqlCommand.ps1 -Sql "SELECT TOP 5 * FROM Orders" | Format-Table
OrderID CustomerID EmployeeID OrderDate RequiredDat ShippedDate ShipVia Freight
e
------- ---------- ---------- --------- ----------- ----------- ------- -------
10248 VINET 5 7/4/1996... 8/1/1996... 7/16/199... 3 32.38
10249 TOMSP 6 7/5/1996... 8/16/199... 7/10/199... 1 11.61
10250 HANAR 4 7/8/1996... 8/5/1996... 7/12/199... 2 65.83
10251 VICTE 3 7/8/1996... 8/5/1996... 7/15/199... 1 41.34
10252 SUPRD 4 7/9/1996... 8/6/1996... 7/11/199... 2 51.3
No comments:
Post a Comment