PowerShell and SQL Agent: A Powerful Combination

Many people know that PowerShell integrates well with SQL Server especially when you use the SQLPS module.  But not everyone knows that this same module makes integration with SQL Agent a snap. 


I recently had the need to allow a technically proficient end user the ability to submit some SQL Agent jobs.  My first thought was to give her SQL Server Management Studio.  However, the company has rules against this which precluded that option.  Then I realized that PowerShell offers another option.  Why not give her a PowerShell script that allows her to submit  jobs on demand.


To do this, I developed a script that loads a list of SQL Agent jobs into the good ole Out-GridView where she can select jobs she wants to run.  Let's look at the script...

if(-not(Get-Module -name "sqlps") {
          Import-Module "sqlps"
          }
# Set where you are in SQL Server...
set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\JobServer\Jobs

while (1 -eq 1)
{
   $jobname = $null
   $jobname = Get-ChildItem | Select-Object -Property Name, Description, LastRunDate, LastRunOutcome  | Where-Object {$_.name -like "User*"} | out-gridview -Title "Job Run" -OutputMode Single
  
   If (!$jobname) { Break }


   $jobobject = Get-ChildItem | where-object {$_.name -eq $jobname.Name}
  
   $jobobject.start()
}




The initial block of code just loads the SQLPS module if it is not loaded.  Note: If you installed SQL Server Management Studio, you have this module.  If not, you can download is at:
http://www.microsoft.com/en-us/download/details.aspx?id=29065




Let's look at the code that loads the module below.



if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }


Pretty simple.  If the module is not loaded, load it.  The SQLPS module take a minute to load so I like to check rather than waste the time of reloading it.


PowerShell supports a feature called Providers.  This is when an a resource such as SQL Server or the Windows Registry provides an interface that simulates the file system.  The SQLPS module implements a provider that allows us to navigate around SQL Server as if it were a file system.  Hence, we can do the command below.


set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\JobServer\Jobs




The line above sets the current location to the SQL Agent jobs on the default instance of my machine. 


Although this could be a fall through script that submits the job and exits, I thought it would be nice if it came back to the selection list after submitting a job so you could submit more.  To do this, I set up an infinite loop with a While statement as follows...



while (1 -eq 1) {


Since 1 will always equal 1, the loop never ends.  But we can use the break statement to exit when we are ready.   


Within the While loop, we are going to test the job name for null as a condition to exit.  Therefore  we want to initialize $jobname to null.
$jobname = $null


Then we have a rather complex looking statement.  Fear not! It is actually pretty easy to follow.  We are leveraging PowerShell's ability to pipe (a fancy word for stack) commands, i.e. the output of one command becomes the input of the next.  The bar '|' separates the commands in the stack.  Let's look at the statement below more closely.


   $jobname = Get-ChildItem | Select-Object -Property Name, Description, LastRunDate, LastRunOutcome  | Where-Object {$_.name -like "User*"} | out-gridview -Title "Job Run" -OutputMode Single


Although the variable $jobname is on the left, it is the last part of the statement to be executed.  In other words, everything to the right of '$jobname =' must be executed before the value is returned to $jobname.  Get-ChildItem simply returns the list of items in a folder.  However, I want to restrict the jobs available to the user to job names prefixed with 'User'.  This allows me to set aside specific jobs for the user to execute.  I can filter on the list returned by piping the output of Get-ChildItem into a Select-Object CommandLet which extracts just the properties I am interested in. In turn, I pipe that into the Where-Object CommandLet which filters on the name property.  Note:  piping means that multiple items are being passed so the $_ is a reserved variable that we use to get the current instance, i.e. applies to each item.  In this case, we filter on name like 'User*' and pipe the results into Out-GridView which provides a nice GUI display list.  I want to call attention to the Out-GridView part of this line.  Let's look at the code below. 


out-gridview -Title "Job Run" -OutputMode Single


The -Title parameter gives a custom title to the grid view window.  The -OutputMode parameter controls whether multiple items can be selected or as we want here, just one item, i.e. Single. 


An interesting facet is that this line will halt execution of your script until the user closes the grid view window.  Whatever item they selected will be stored in $jobname.  If the user clicks Cancel, nothing is returned and the line below will check for that condition and exit the script.


If (!$jobname) { Break }


So !$jobname means they did not select a job and Break ends the script.


If a job was selected, we need to get an object reference to the job so we can submit it.  We do that as follows.




 $jobobject = Get-ChildItem | where-object {$_.name -eq $jobname.Name}



Again, the Get-ChildItem is piped into a Where-Object CommandLet allowing us to get the specific job we want.




Finally, we submit the job as follows.




$jobobject.start()


Summary

This example just touches on the possibilities.  You can also create, modify, delete and even schedule SQL Agent jobs from PowerShell scripts.  Note:  SQL Agent also supports calling PowerShell scripts.  PowerShell also has a built in job scheduler which can be used in combination with SQL Agent or independent of.  However, I think it is clear that the combination of PowerShell and SQL Agent is a marriage made in heaven. 


Thanks,


Bryan

Web Automation with PowerShell aka the Ghost at the Machine

PowerShell is good at running and manipulating Windows applications.  For example, I once had a client that needed a process to log on to a web site, fill in a number of text boxes and click a button to the download a file.  I was able to accomplish this quickly with PowerShell.


In this blog, I want to demonstrate how  to automate applications such as Internet Explorer using PowerShell. I have a script below that uses a web site that provides a map with zip codes for a specified location.  The PowerShell script will navigate to the site, enter a location into the address text box and click the button to look display the map. 


Before we begin I need to point out that the script uses a free module called WASP which provides a number of convenient automation CommandLets such as sending key strokes to an application.  Typically a module adds extra support for a specific type of functionality.  Unless you have already downloaded WASP, you will need to do so.  You can download it from http://wasp.codeplex.com/.  After you download it, extract the file WASP.DLL and copy it to a folder where PowerShell looks for modules. 


To determine where PowerShell searches for modules, enter the following command.

$env:PSModulePath

In the output, you should see a list of folder paths separated by semi colons.  This environment variable holds the places PowerShell will look for modules.  Pick one of the folders, ideally one under your documents folder, and create a new folder named WASP in it.  Then paste the unzipped WASP.dll in that folder.  A module must have its own folder. 

Now we are ready.  Let's review the Web Automation script below.

Import-Module WASP

$url = "http://maps.huge.info/zip.htm"

stop-process -processname iexplore*   #  End any other Explorer sessions.

start-sleep 2  # Wait above to finish.

$ie = New-Object -comobject InternetExplorer.Application

$ie.visible = $true

$ie.silent = $true

$ie.Navigate( $url )

while( $ie.busy){Start-Sleep 1}

Select-Window "iexplore" | Set-WindowActive

$txtArea=$ie.Document.getElementsByName("address")

Foreach($element in $txtArea)
    {
        $element.value = "Boston MA"
    }

$btnFind=$ie.Document.getElementsByName("BUTTON")

Foreach($btn in $btnFind)
    {
        $btn.Click()
    }

Let's review this code in detail.  First we need to import the WASP module which do with the line below.

Import-Module WASP

The next line simply stores the URL we are going to use into a variable as shown below.

$url = "http://maps.huge.info/zip.htm"

Then we need to stop any other Internet Explorer processes so our program won't get confused when it tries to being up an IE window.

stop-process -processname iexplore*   #  End any other Explorer sessions.

Now let's review several lines of  related code below.

$ie = New-Object -comobject InternetExplorer.Application

$ie.visible = $true

$ie.silent = $true

$ie.Navigate( $url )

The first line above creates an instance of Internet Explorer and places a reference to it into the variable $ie.  The line "$ie.visible = $true"  makes the IE application visible.  The line "$ie.silent = $true "  says to suppress messages.  Finally the line "$ie.Navigate( $url )" tells IE to go to the URL specified in the $url variable.

IE needs time to finish the above tasks which we give it with the line below.

while( $ie.busy){Start-Sleep 1}

Since IE is in the background, we need to bring it activate and select it using WASP CommandLets as shown below.

Select-Window "iexplore" | Set-WindowActive

Ok. The screen is up and waiting for input.  To fill in the address field, we need to get a reference to it first which we do as follows.

$txtArea=$ie.Document.getElementsByName("address")

Now we can use the reference to fill in the text box.  Here's where each web automation script will be different.  To navigate around the web page, we use DOM but to do that you need to know what the web page source code is.  In IE, you can right mouse click on the page and select View Source to see it.  Then you need to review it to see what the elements are and how they are identified.  If the element has a unique id, you can use the GetElementByID method to get a reference to it, i.e. $myelement = $ie.GetElementByID.  In this case, all we have is a name.  Unfortunately, GetElementsByName returns an array of elements even if there is only one which is true here.  So we need to loop through the array to enter the text as shown below.

Foreach($element in $txtArea)
    {
        $element.value = "Boston MA"
    }

In the code above, the Foreach iterates over the $txtArea array storing each item into $element.  We use this in the loop to set the value, i.e.   $element.value = "Boston MA".  Ok.  It's a bit awkward but Web Automation can be like that since you don't usually have any control over the HTML of the page.

Ok.  The location is entered, now we need to click the Find button.  As before, there is no unique ID so we'll use GetElementsByName and loop through the collection. 

$btnFind=$ie.Document.getElementsByName("BUTTON")

Foreach($btn in $btnFind)
    {
        $btn.Click()
    }

In the above code, we are using the button click method to execute the find.

If it worked, you see the page with a map showing Boston, Massachusetts and the zip codes for the area. 

Internet Explorer is only one of many applications that can be automated.  Office applications, Win form applications, and many more applications can be automated in a similar manner.  This feature can help automate a routine manual task to save you time or be integrated into a production job.  It's another reason why PowerShell is such a valuable tool.

Thanks,

Bryan

Leveraging PowerShell's Overlooked -PassThru Paramter

One of my favorite PowerShell CommandLets is Out-GridView.  It provides a nice graphical grid display with filtering and sorting.  But an often overlooked feature of Out-GridView is the ability to pass selected rows through to the pipeline.  This can be put to good use on a number of database and ETL applications.

Let's consider a hypothetical requirement.  Human Resources wants to give some employees an additional 40 hours of vacation time.  Ok, not the most likely requirement but substitute salary adjustment, title changes, etc, and these types of mass changes are needed all the time.  So how our process will work is:

- Display a list of potential employees to get the increase in a grid view.
- The user will select the employees they want the increase to go to and click Ok.
- Another grid view pops up with just the selected rows so the user can confirm the data.
- The selected employees have their vacation hours increased by 40 hours.
- A grid view pops up showing the employees including the updated ones.

To make this easy for you to run this code, I used good old Adventure Works.  Here's the whole script but I'll go over it in detail.

if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }

$qry = "
SELECT e1.[BusinessEntityID] as EmpID
      ,e1.[FirstName] + ' ' + e1.[MiddleName] + ' ' + e1.[LastName] as Name
      ,e1.[JobTitle]
      ,VacationHours
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
  FROM [AdventureWorks].[HumanResources].[vEmployee]            e1
  join [AdventureWorks].[HumanResources].[vEmployeeDepartment]  e2
    on e1.BusinessEntityID = e2.BusinessEntityID
  join [AdventureWorks].[HumanResources].[Employee]  e3
    on e1.BusinessEntityID = e3.BusinessEntityID"

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables
 
$list =  Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Select records to reset"  -PassThru | Out-GridView -Title "Confirm Selection..." -PassThru

foreach ($item in $list)
{
      $sql = "update [HumanResources].[Employee] set [VacationHours] = [VacationHours] + 40 where BusinessEntityID = " + $item.EmpID + ";"
     
      Invoke-Sqlcmd -Query $sql -QueryTimeout 25
}

Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Vacation Hours Updated..."


Let's Review What's Happening in the Script...

The first few lines are just checking to see if the SQL Server module "sqlps" is loaded and if not, it will load it.  If you have SQL Server Management Studio, you should have the "sqlps" module.

if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }

Then I just declare a variable $qry to hold the SQL code I want to run.

$qry = "
SELECT e1.[BusinessEntityID] as EmpID
      ,e1.[FirstName] + ' ' + e1.[MiddleName] + ' ' + e1.[LastName] as Name
      ,e1.[JobTitle]
      ,VacationHours
      ,[AddressLine1]
      ,[City]
      ,[StateProvinceName]
      ,[PostalCode]
      ,[CountryRegionName]
  FROM [AdventureWorks].[HumanResources].[vEmployee]            e1
  join [AdventureWorks].[HumanResources].[vEmployeeDepartment]  e2
    on e1.BusinessEntityID = e2.BusinessEntityID
  join [AdventureWorks].[HumanResources].[Employee]  e3
    on e1.BusinessEntityID = e3.BusinessEntityID"

To connect to the database and set the right location, I use this line...

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables



This abstraction of the SQLPS module lets us treat SQL Server like a file system which makes it easy to connect to the server and database I need.

The next line packs a lot of punch.  It is piping the results of one command to the next.
 
$list =  Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Select records to reset"  -PassThru | Out-GridView -Title "Confirm Selection..." -PassThru

The cool part is how it does all the work on the right side of the equal sign and then assigns the final result to out variable $list.  So first the select query is invoked and passes the result to Out-GridView so we see something like the screen below.

The user can then select the employees they want updated by clicking on them.  In my testing, I selected the first four so I could verify the update occurred. After selecting the rows desired, the user clicks Ok to continue.  The user will then see another grid like the one below.

You should see just the rows you selected.  The -PassThru option on the first Out-GridView caused  ONLY the selected rows to be passed down the pipe.  From here, you need to select the rows again so they will get passed along.  Note: Control + A will select them all.  Click Ok when you are ready to continue.

Now the update code will loop through the selected rows and update the vacation hours.

foreach ($item in $list)
{
      $sql = "update [HumanResources].[Employee] set [VacationHours] = [VacationHours] + 40 where BusinessEntityID = " + $item.EmpID + ";"
     
      Invoke-Sqlcmd -Query $sql -QueryTimeout 25
}

Finally, the original query is executed again and piped to Out-GridView so we can inspect the updates. 

Invoke-Sqlcmd -Query $qry -QueryTimeout 25 | out-gridview -Title "Vacation Hours Updated..."



And that's all there is to it!  I encourage you to use your imagination on how you could do similar things.  For example, you could select files from a folder to be processed.  You might use it to allow users to approve or reject data.  By combining the power of the "sqlps" module with the Out-GridView -PassThru paramter, you can do a lot of work with very little code.

Thanks,

Bryan Cafferky
Business Intelligence Consultant
BPC Global Solutions LLC


 


  
 
 
 

ETL With PowerShell (SQL Server to Excel to CSV)

I've been doing a lot of BI work with PowerShell lately and wanted to demonstrate how easy it is to move data around with PowerShell.  The code below queries a table in SQL Server using the free Microsoft provided SQLPS module, loads the data into a newly created Excel spreadsheet and then exports the spreadsheet to a CSV file.

Note the code at the top that checks before loading SQLPS.  When running multiple times, this can save you a lot of time.

#  Check if the SQL module is loaded and if not, load it.
if(-not(Get-Module -name "sqlps"))
    {
      Import-Module "sqlps"
    }

Actually, if my goal was to get the data to a CSV file, I could do that in a lot less commands.  My goal is to highlight how easy it is to use the Excel COM object to leverage the power of the Excel application and to augment that with access to databases such as SQL Server.  The SQLPS module even returns the results in a format consumable by PowerShell.  You could access a different database platform via ODBC and that will be a future blog.  Dare I say that PowerShell can do ETL and may even challenge SSIS?  Hmmm....

#  Check if the SQL module is loaded and if not, load it.
if(-not(Get-Module -name "sqlps"))
    {
      Import-Module "sqlps"
    }

set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\Databases\Adventureworks\Tables

$territoryrs = Invoke-Sqlcmd -Query "SELECT [StateProvinceID],[TaxType],[TaxRate],[Name] FROM [AdventureWorks].[Sales].[SalesTaxRate];" -QueryTimeout 3

$excel = New-Object -ComObject Excel.Application

$excel.Visible = $true

$workbook = $excel.Workbooks.Add()

$sheet = $workbook.ActiveSheet

$counter = 0

foreach ($item in $territoryrs) {

 $counter++

    $sheet.cells.Item($counter,1) = $item.StateProvinceID
    $sheet.cells.Item($counter,2) = $item.TaxType
    $sheet.cells.Item($counter,3) = $item.TaxRate
    $sheet.cells.Item($counter,4) = $item.Name
}

#  Exporting Excel data is as easy as...
$sheet.SaveAs("taxrates.csv", 6)

Thanks,

Bryan

PowerShell Modules Make Life Easier

When I first started developing in PowerShell, I just dot sourced any code I needed. 

.  C:\Users\BryanCafferky\Documents\PowerShell\function\myfunction1.ps1

Myfunction1 “parm1” “parm2”

But as the complexity of code and number of functions increased, this became more and more of an issue.  Consider that when you move the code to run on a server, you now have to manually change all the paths to your functions.  
  

A quick and dirty fix would be to add a variable to the top of the script that has the path to the code, i.e.

$codepath = “C:\Users\BryanCafferky\Documents\PowerShell\function\”

. ($codepath + "ufn_copy_file.ps1")

. ($codepath + "ufn_move_file.ps1")

Ufn_copy_file “file” “file2”

 

However, a better idea is to use modules because PowerShell has the ability to automatically find modules via the environment variable $env:psmodulepath and it can load as many functions as you need via one line of code.

What is a module?

A module is just a collection of PowerShell functions that can be automatically loaded together as easy as:

Import-module “mymodule”

For example, entering,

Import-Module “sqlps”

Will load the SQL Server PowerShell module with all the useful command-lets it contains. 

There are several ways of creating modules but the easiest way is to simply take a bunch of functions you want included in the module and put them all in one file with an extension .psm1 .  Then place this file in a folder where PowerShell will find it.  To determine what folders that is, just type $env:psmodulepath and press enter as shown below.

As a rule, you will see \WindowsPowerShell\Module under your documents folder and you can place your module there, i.e. on my machine this is…

C:\Users\BryanCafferky\Documents\WindowsPowerShell\Modules

But you can place it in any folder listed in $env:psmodulepath.  

Is that All?

Not quite.  A module needs to have a folder of its own named for the module, i.e. MyModule and the script file that holds the functions needs to be in the module folder with the same name, i.e MyModule.psm1 as shown below.

That’s all there is to it and from now on when you need a function from that module, all you need to do is enter…

Import-Module “MyModule”

A couple of side notes here.  First, if you ever get a module from somewhere and PowerShell can't find it, check to make sure you put it in a folder contained in $env:psmodulepath.  Second, if you copy your module to another machine, make sure you place it in a folder contained in $env:psmodulepath.

Say good bye to dot sourcing and hello to automatically loaded functions.