Speaking

Just returned home from my sixth SQL Saturday speaking engagement on Powershell and SQL Server.  Had a great time in Madison this year, visiting with various people I have met over the years and visiting with lots of PASS MN folks that I rarely see now that I am telecommuting.

My session A Powershell Driven Life (scripts available) was well attended again and follows the theme I have used the past three SQL Saturdays, using pictures of various beers, etc as my lead in to each script.  The demos all worked perfectly (something I noticed a few speakers really struggled with) and I found myself going much slower than I intended too - getting through even less demos than I had figured was my minimum based on trial runs.  I don't know how the great speakers can predict timing so effectively.  One thing that surprised me was the sheer number of people new to Powershell that came to my "intermediate" session.  I tried to slow down and explain a few concepts that tripped me up when I was learning.  The feedback was excellent, and my numerical scores were lower than usual.  Personally, the feedback is more useful than the arbitrary grading scale.  (Or that is what someone with lots of 3's and 4's would say :) )

The thing I thought about this morning, is the sheer amount of commitment that is required from the organizers and speakers of these events.  My presentation code was basically already done, because it was developed "in the line of duty".  I spent an inordinate amount of time rehearsing - and hoping to develop a some comedic relief as I went along.  Speaking of comedic relief, my coworker @PhoneticTalk did a fantastic job of intermixing humor and content in his Power View session.  In developing this presentation, I did not track my hours, but if I was consulting it would be in the $1000's for billable time.  In talking with some fellow speakers, I still marvel at why people are willing to travel, finance their own hotels, etc and spend inordinate amounts of time speaking.  So, I will share in brief what speaking did for my career.

My current boss and I met when I was speaking at a user group.  That occurred years before I got the opportunity to work with them and so I have written off the tons of hours preparing and giving presentations as worth it. I am one of the lucky people with a challenging job on a great team of smart people who are fun to work with.  Not too mention, I am learning loads of new technologies and getting real world experience working with Analysis Services, after ~11 years on the OLTP side.  In addition, I know some really smart people scattered around the region and if needed, could probably reach out to them for help when I am in a bind.

So, I may not speak again for awhile, I enjoy Powershell talks but I am not sure where to take it from here - and its time to let some other folks jump in.  Based on the amount of folks that haven't used Powershell , I may be inclined to work on an introduction course if one is not offered next year.

If for some reason you read this and have thought about speaking but haven't, give it a try, a great opportunity might be sitting in the audience.

The eye pleasing data alert

There comes a time in any developer's life where they must write some code that emails folks when there are data problems.  Regardless of the system or causation of a data problem, an alert must be sent.  In the days of being a DBA, the alert would generally be a text file or just plain text dumped into the body of an email.  This worked fine for me for several years as I was generally the primary consumer of such alerts.  Those days are over.

Enter working in business intelligence.  Now the data matters and the format it is delivered in.  The learning curve for me has been steep; I have always been more focused on content than aesthetics.  Now both are a requirement.  Initially, I thought this was silly but I am starting to understand the importance of things being right and looking good.  Alerts, reports, etc will be used and paid attention to when they have the proper coloring and gradients to express what the data is saying.  Just dumping numbers to the screen will not suffice anymore.  In addition, the obvious mobile device usage in this day and age predicates that the alerts look good on a mobile phone as well.

The process is brute force HTML construction, but it yields a pretty nice looking alert and does not require outright looping or much code.  Essentially, you construct a style sheet and then use a few TSQL tricks to generate the data set and create ladderstriping with Row_number() functions. (yet another term I was unfamiliar with until BI).  I would not call the process graceful, but it passes the eye test with the very picky data consumers I have :).



DECLARE @Alert NVARCHAR(MAX) = '' +
      
N'' +
N'Data Alert Title' + 
    N' Order NumberLineBusiness UnitDocumentDocument TypeCompanyCustomer ' 

SELECT @Alert = coalesce(@Alert,'') +
' CASE WHEN (ROW_NUMBER() OVER (ORDER BY [Date] desc,Document desc,OrderNumber desc)) %2 = 0 THEN 'r2' ELSE 'r1' END 
+ '">' +
'' + ISNULL(OrderNumber,'') + '' +
'' + ISNULL(CAST(LineNumber AS VARCHAR(10)),'') + '' +
'' + ISNULL(BusinessUnit,'') + '' +
'' + ISNULL(CAST(Document AS VARCHAR(24)),'') + '' +
'' + ISNULL(CAST(DocType AS VARCHAR(24)),'') + '' +
'' + ISNULL(Company,'') + '' +
'' + ISNULL(CustomerNumber,'') + '' 
FROM 
FROM dbo.MyDataTable
        WHERE alertProblem=1 )
DECLARE @rc INT = @@ROWCOUNT

SELECT @Alert += N'
' ;
IF(@rc > 0 AND @Alert IS NOT NULL) 
BEGIN
DECLARE @MailProfile VARCHAR(255) = 'mailprofile'
DECLARE @subj varchar(128) = @@SERVERNAME + ': DataAlertName'
DECLARE @FromAddress varchar(128) = @FromAddress

EXEC msdb.dbo.sp_send_dbmail  
@from_address = @FromAddress
,@reply_to = @FromAddress
,@recipients = @email
,@body = @Alert
,@subject = @subj
,@profile_name = @MailProfile
,@body_format = 'HTML' ;
END

Looking forward to trying one of these out for fixing fences and hauling rocks this spring.

Solving the Space Wars via Powershell

The constant battle as the consumption of data grows is storage space.  When a disk space alert goes off, you generally will jump on the server over RDP and start hunting through the drive for what has caused the growth to hit the "wake me up" limit.  

This has been an occurrence in my career for a number of years, but seems to be happening more and more often.  As my career has progressed, I create more and more monitoring for various things that cause outages ultimately but this was never put together.  Given the simplicity of this script, I wish I had done it when my primary job was being a DBA.  Sure would have helped on our development and QA servers with finding what data file grew out of sight, or what log file had gotten carried away.

So here it is, a pretty basic thing that just dumps the information to the out-gridview control.  This could be shortened and condensed but that would sacrifice the readability and that is more important :)

Note: You may need a second variable for the SQL Instance name if not default

 $Server = "DBServer"  
 [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")  
 
 #Load up the SMO for the server      
 $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $Server  
 
 # Grab list of drives - this should get us mount points as well as regular drives (not tested)  
 [Array]$Drives = Get-WmiObject -ComputerName $server -Class Win32_Volume |   
     Select -Property DriveLetter, Label, @{Name=’FreeSpaceMB’;Expression={$_.FreeSpace/1MB} } |  
     Where-Object{$_.DriveLetter -ne $null}   

 # Grab the data/index files for each database  
 $dbFiles = $sqlServer.Databases |   
   Select-Object FileGroups -ExpandProperty FileGroups |  
   Select-Object Files -ExpandProperty Files |  
   Select FileName,Name,UsedSpace,Size  

 # Grab the log Files for each database    
 $dbFiles += $sqlServer.Databases |   
   Select-Object LogFiles -ExpandProperty LogFiles |  
   Select FileName,Name,UsedSpace,Size  

 #Container to hold the information in    
 [Array]$diskSummary=@()  

 #Loop through each drive ordered by free space on the drive  
 foreach($drive in $drives | Sort-Object FreeSpaceMB )  
 {  
   Write-Host $drive.DriveLetter  
   Write-Host $drive.Label   
   #Grab the size and used space for each database file on the current drive  
   $size = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object Size -sum).Sum   
   $used = ($dbFiles | Where-Object{$_.FileName.StartsWith($drive.DriveLetter)} | Measure-Object UsedSpace -sum).Sum      
   #Add that information to the array for output later  
   $diskSummary += $drive | Select DriveLetter,Label,FreeSpaceMb,@{Label="DBFileSizeMB";Expression={$size/1024}},@{Label="DBFileUsedMB";Expression={$used/1024}}  
 }    
 #Show the files and disk information  
 $dbFiles | Sort-Object DriveLetter,FreeSpaceMB -descending | out-gridview -Title "DB Files"  
 $diskSummary | out-gridview -Title "Disk Summary"  

So there is the basics to get you started.

I will be speaking on this and a lot more bright and early on April 6th, 2013 at SQL Saturday 206 in

Hope to see you there.

Telecommuting, the beginning is ahead of us

Fascinating stuff has been written lately about telecommuting.  Yahoo is pulling in their telecommuters and in the wake of it, Scott Hanselman wrote a really accurate piece on the matter (my opinion based on 4.5 months of actually doing it).  And again, Thomas Larock wrote about it.  It is hard to know what the real motivation is at Yahoo, but the key public component comes down to accountability.

Or simply put.

Can the company see that you are doing your job and can you prove you are doing it well? 

In thinking about how it works for the team I am on, I can safely say the fact that we deliver cubes, reports, etc every 2 weeks helps in terms of visibility to the business and the stories completed/points burned shows that individuals are doing their part.

Here is how the team setup unfolds:

• The bulk of my team, including the leads are remote.  The folks that live near the office work remote a fair amount of time.  I think that addresses the work place jealousy aspect (or at least I presume it does).
• We use Yammer, Lync, IP phones and do Agile so everyone has at least 5 minutes of vocal contact per day and multiple ways to converse, badger each other, support, whatever. 

On the personal side:

• I have been in IT for 12 years, and a "senior" level SQL DBA, so I have been in the industry awhile.
• My previous jobs have allowed some degree of telecommuting, but never any extensive amounts.
• My career path and presentations prove to some degree that I am "driven".
• I require very little social interaction, thus the need for daily office interaction is less important.

With all that said - here is what I have experienced/noticed in my first few months on the job in no real order.

1. I work too much.  Just plain not used to being the weak spot on a team, so its hard to not keep going, especially during these miserable winter days and nights.  Not to mention sometimes what I am working on is interesting enough I just keep working on it.
2. Communication can be hard.  Nearly every single time a phone call, IM or screen share will solve it, but I think sometimes being in person would be easier.
3. I get monumentally more work done in a day working from home.  I don't have to listen to a loud salesman "we can help align your vertical markets with value added synergies" and the bizarre lives of nearby cube mates.  I have become somewhat blissfully ignorant of the outside world - probably not a long term thing, but has sure allowed me to think about things that do matter, like fishing trips with the kids, foraging for mushrooms and the impending maple syrup season:)
4. Daylight - I have been able to spend more lunch hours hiking with my dog (w or w/o a gun) then I ever have during the week.  I live in Wisconsin, daylight is short in the winter, commuting chewed up an hour and a half of my day.  Lunch hour hikes are the norm now, to the point my dog comes in and reminds me.  At this rate I might be in decent shape this year.
5. Sunday nights used to be source of dread for me, the long drive in on Monday, the traffic, snow storms, rain, crashes, etc.  There has not been a Sunday since I started telecommuting where I hated what Monday brought.  That may be because my job is challenging and interesting too, but I know I hated the commute more than the work all along.
6. Kids.  I have been to 4 school functions in the past few months, which might total the amount I had seen the previous few years.  I even wait for the bus with my son occasionally.  Some mornings they will get up excited and I get to be there to talk about fishing, ninjas, bugs or video games.
7. Internet speed.  I live in the country, and I have good internet speeds, but not great.  At times this has become a bit of an issue as doing screen sharing where I am the host can be problematic.  But that is about the only real problem I have had.
8. Leaving your work in the home office.  There is always talk about never being able to leave your work when its at home, but I don't recall that ever being the case in my neck of IT, we were working after we got home then too.  You just don't have to haul a laptop home with you.  
9. I make fun of the "green" movement, but my family actually lives it.  Meaning a huge garden, lots of raising, harvesting and foraging of our food, and all the other subtle things we can do to lessen our impact on the environment.  Even though I carpooled into work as much as I could, the amount of miles I put on my car has gone down dramatically.  Not to mention I utilize a space in my house that would be heated regardless and I found a really cool highly efficient lamp that I use for light (when the sun isn't out).  These are all things my work place would have to provide for someone who is there 9 hours out of 24 during the week.
10. While I am thinking about it from the business side, I think a telecommuting worker is willing to work for less money.  Some might not, but for someone like me that lives out in the country, that commute was a killer in many aspects of the word.  I was willing to give up money for life (and a really good opportunity).

There is more really, but that is a good place to stop.  Already looking forward to a noon time hike to a nice waterfalls with all this new snow on the ground and 34 degree temperatures.  

My office if lived on a lake :)

On that note, I better get back to work.

Moving SSIS Projects in SQL Server 2012

How do you move SSIS packages from one server to another when using the SSIS Catalog on SQL Server 2012?

Before I start, here are the parameters of the post:

1. We are using the "Integration Services Catalog" on SQL Server 2012
2. We deploy from BIDS to staging and production - no "ispac" files
3. We use the project deployment method

When confronted with this task recently, I immediately went off to www.google.com and started searching, I was unable to find anything for my current situation.  There were several posts together that helped get me started including this one by Matt Masson .  The complexity that was introduced was primarily around the fact that I could not figure out how to move SSIS packages without an "ispac" file.  This script is primarily courtesy of the post mentioned, but I then utilized a stored procedure from the SSIS catalog SSISDB.catalog.get_project to get the project bytes and load them on the destination server.

Hope this helps someone out in the future, although I would guess there is a method that doesn't require querying the catalog to get the project bytes, here is the script I came up with.

 $SourceServer = "SourceServer"  
 $DestinationServer = "DestServer"  
 $ProjectFolder = "ProjectFolderName"  
 $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"       
 [Reflection.Assembly]::Load("$ISNamespace,Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") | out-null      
 Write-Host "Connecting to server ..."       
 # Create a connection to the server       
 $constr = "Data Source=$SourceServer;Initial Catalog=master;Integrated Security=SSPI;"       
 $destconstr = "Data Source=$DestinationServer;Initial Catalog=master;Integrated Security=SSPI;"  
 $con = New-Object System.Data.SqlClient.SqlConnection $constr       
 $destcon = New-Object System.Data.SqlClient.SqlConnection $destconstr       
 # Create the Integration Services object       
 $ssis = New-Object $ISNamespace".IntegrationServices" $con   
 $destssis = New-Object $ISNamespace".IntegrationServices" $destcon   
 #Grab the SSISDB catalog  
 $catalog = $ssis.Catalogs | Where-Object{$_.Name -eq "SSISDB"}  
 $destcatalog = $destssis.Catalogs | Where-Object{$_.Name -eq "SSISDB"}  
 #Grab the Folders where the project  
 $curFolder = $catalog.Folders | Where{$_.Name -eq $ProjectFolder}  
 $destFolder = $destcatalog.Folders | Where{$_.Name -eq $ProjectFolder}  
 # If the destination folder is missing - create it  
 if($destFolder -eq $null)  
 {  
   $destFolder = New-Object $ISNamespace".CatalogFolder" ($destCatalog, $curFolder.Name, $curFolder.Description)       
   $destFolder.Create()       
 }  
 #This deploys everything under the projects  
 Write-Host "Starting Project Deployments for " $curFolder.Name  
 foreach($proj in $curFolder.Projects)  
 {  
   if($con.State -eq "Closed") {$con.Open()};  
   $cmd = New-Object System.Data.SqlClient.SqlCommand  
   $cmd.CommandType = "StoredProcedure"  
   $cmd.connection = $con  
   $cmd.CommandText = "SSISDB.catalog.get_project"  
   $cmd.Parameters.Add("@folder_name",$curFolder.Name) | out-null;  
   $cmd.Parameters.Add("@project_name",$proj.Name) | out-null;  
   [byte[]]$results = $cmd.ExecuteScalar();  
   if($results -ne $null) {  
     $deployedProject = $destFolder.DeployProject($proj.Name,$results)  
     Write-Host $proj.Name $deployedProject.Status  
   }  
   else {  
     Write-Host $proj.Name " failed - no results from Get_Project"  
   }  
 }  

If you are looking to learn more, I will be presenting on this and many other similar scripts at SQL Saturday 206 in Madison, WI .  Here is a map of the campus.

Rye Whiskey and an SSRS Inventory Report

In my previous post, Moving SSRS 2012 with Powershell and Bourbon I covered how to change a data source for any or all the reports on the server.  That worked out so well, I poured myself a small taste of the brand new Rye Whisky from http://www.45thparallelspirits.com/ and celebrated.

The next morning I was talking through the results with our SSRS maven @Equerystrian and she requested that each report, when it had a shared data source, have the data source path listed next to the report.  I started thinking about modifying the script that changes data sources and decided I would rather have a script that is just an inventory of SSRS reports and data sources.  The basics are the same for both, but this report is much cleaner for its purpose.

The CSV file created lists the report path and name, connection string, type of data source and the path to the shared data source.

 CLS  
 $SSRS2012Server = "SSRSServer"   #SSRS Server  
 $ReportPathMatch = "*" # To specify certain reports, * for all
 $file = "C:\Users\AleAsylumMakesGoodBeer\Production_SSRS_Inventory.csv"

 #URL to the web service  
 $SSRSurl = "http://{0}/ReportServer/ReportService2010.asmx" -f $SSRS2012Server  
 # Get a handle on the service  
 $SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;  
 # Get a collection of the reports on the server  
 $itemlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "Report" -and $_.Path -like $ReportPathMatch -and $_.Path -notlike "/Users Folders*"} ;  
 Write-Host "Reports to review" $itemlist.Count  
 #Creating a report tracker  
 $Reports =@()  
 #Creates a report tracking object  
 function Add-Reports  
 {  
   param([string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$dataSourcePath)  
   $report = New-Object System.Object  
   $report | Add-Member -type NoteProperty -name "Report" -value $reportPath  
   $report | Add-Member -type NoteProperty -name "Connection" -value $connString  
   $report | Add-Member -type NoteProperty -name "DataSourceType" -value $dataSourceType  
   $report | Add-Member -type NoteProperty -name "DataSourcePath" -value $dataSourcePath  
   return $report   
 }  
 #Loop Through Each Report  
 Foreach($item in $itemlist)  
 {  
   Write-Host "Checking " $item.Path  
   try  
   {  
     $dsList = $null #clearing it out explicitly  
     #Any given report may have multiple data sources, grab the collection  
     $dsList = $ssrs.GetItemDataSources($item.Path)  
     #Loop through each DS in the report  
     for($i=0;$i -le $dsList.Length-1;$i++)  
     {  
       $ds = $dsList[$i];  
       #If the DS has a reference it would appear to be a shared datasource  
       if($ds.Item.Reference -ne $null)  
       {  
         $dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);  
         $Reports += Add-Reports -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Shared" -dataSourcePath $ds.Item.Reference  
       }  
       else #embedded datasource  
       {  
         $dataSource = $ds.Item  
         $Reports += Add-Reports -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Embedded" -dataSourcePath ""  
       }  
     } #for each datasource   
   }  
   catch{  
     Write-Host "Error on " $item.Path $Error[0]  
     $Error.Clear();  
   }  
 } # Foreach Report  
 $Reports | Export-Csv $file -Force  

Moving SSRS 2012 with Powershell and Bourbon

Recently, talk of splitting a fairly large SSRS instance off on to its own box came up.  Given the BI shop at my employer is aggressive and gets things done often, there are lots of reports and they are used by people daily to make decisions.  The one issue as I see it with being aggressive and not having heavy handed code reviews or dictatorial team leads is things don't get done the same way each time.  So, in this case, what you have is some reports pointing to localhost, some point to the server name and others to the server alias.  In our case, we want to get everything pointing to the alias to make it easier when we move.

Naturally, Powershell came up as the magical elixir to this predicament, even though there may be other viable ways.  My thoughts were I would be able to grab someone else's script easily enough and just modify it for our environment, but that didn't work out well.  It appears few people are using SQL Server 2012 and SSRS in native mode (or at least they choose to blog about it, ask question on it in forums, etc)  For one reason or another, Sharepoint seems to be more prominent, and that is where we are going as well, piece by piece.

This script follows the easy road of getting collections into variables and then looping through them, there are much more condensed ways to write this script.  I doubt this will work with any other of the Reporting Services versions.

So here is the script - be darn careful since I haven't kicked the tires on this completely yet.  I have commented out the main damaging part toward the bottom, as of my tests, this report will manage both shared and embedded data sources.  I have commented this pretty hard, and as the careful eye of @Equerystrian pointed out, I even had lies in my comments.  Which explains why commenting code is bad :)

Note: I made a few edits after not liking how it handled the shared data sources, the process now collects them for each report and then updates a unique list at the end, a bit cleaner.  

 CLS  
 [Boolean]$DoDamage = $false     #If you wish to update the DataSources  
 $SSRS2012Server = "MySSRSServer"   #SSRS Server  
 $OldSQLServer = "MyOldDataSource"    #Old DataSource Server Name you want to replace  
 $NewSQLServer = "MyNewDataSource"     #New DataSource Server Name   
 $ReportPathMatch = "/Development*" # To specify certain reports, * for all  
 #URL to the web service  
 $SSRSurl = "http://{0}/ReportServer/ReportService2010.asmx" -f $SSRS2012Server  
 # Get a handle on the service  
 $SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;   
 # Get a collection of the reports on the server  
 $itemlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "Report" -and $_.Path -like $ReportPathMatch} ;  
 #Creating a report tracker  
 $ReportChanges =@()  
 $SharedDataSources = @(); #Track Shared Data Sets for Updating after checking all reports  
 #Creates a report tracking object  
 function Add-ReportChanges  
 {  
   param([string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$newConnString)  
   $report = New-Object System.Object  
   $report | Add-Member -type NoteProperty -name "Report" -value $reportPath  
   $report | Add-Member -type NoteProperty -name "Connection" -value $connString  
   $report | Add-Member -type NoteProperty -name "DataSourceType" -value $dataSourceType  
   $report | Add-Member -type NoteProperty -name "NewConnection" -value $newConnString  
   return $report    
 }  
 #Loop Through Each Report  
 Foreach($item in $itemlist)  
 {  
   [Boolean]$DataSourceChanged=$false   
   Write-Host "Checking " $item.Path  
   try  
   {   
     $dsList = $null #clearing it out explicitly  
     #Any given report may have multiple data sources, grab the collection  
     $dsList = $ssrs.GetItemDataSources($item.Path)  
     #Loop through each DS in the report  
     for($i=0;$i -le $dsList.Length-1;$i++)  
     {   
       $ds = $dsList[$i];  
       $newItem = $null  
       $dsType = $null    
       $dataSource = $null  
       $oldConnString = $null  
       #If the DS has a reference it would appear to be a shared datasource  
       if($ds.Item.Reference -ne $null)   
       {  
         $dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);  
         $dsType = "Shared"  
       }  
       else #embedded datasource  
       {  
         $dataSource = $ds.Item  
         $dsType = "Embedded"  
       }  
       $oldConnString = $dataSource.ConnectString  
        # Old Name Exist in Connection  
       if($oldConnString -ilike "*Data Source=$OldSQLServer*")  
       {  
         # If a Shared Datasource add to list for updating later, if embedded, update it.    
         If($dsType -eq "Shared")   
         {  
           $SharedDataSources += $ds.Item.Reference   
           $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString "Shared"        
         }   
         else   
         {  
           # Make a Copy of the datasource  
           $newitem = $dataSource  
           # Simple replace of the server name  
           $newItem.ConnectString = $oldConnString -iReplace $OldSQLServer, $NewSQLServer  
           $dsList[$i] = $newItem  
           $DataSourceChanged = $true # Flip this bit so we know to save this DS collection back to SSRS   
           $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString $newItem.ConnectString  
         }  
       }  
       else  
       {  
         $ReportChanges += Add-ReportChanges -reportPath $item.Path -connString $oldConnString -dataSourceType $dsType -newConnString ""        
       }  
     } #for each datasource    
     # Push the datasource back on the report  
     If($DataSourceChanged)  
     {  
       Write-Host "Updating Datasources for " $Item.Path  
       IF($DoDamage) {$SSRS.SetItemDataSources($Item.Path, $dsList)}  
     }    
   }  
   catch{  
     Write-Host "Error on " $item.Path $Error[0]  
     $Error.Clear();  
   }  
 }  
 #Now get a unique list of the shared data sources and update them  
 Foreach($sh in $SharedDataSources | Sort-Object | Get-Unique)  
 {  
   $ref = $ssrs.GetDataSourceContents($sh);  
   $oldConnString = $ref.ConnectString  
   $ref.ConnectString = $oldConnString -iReplace $OldSQLServer, $NewSQLServer  
   if($DoDamage)   
   {  
     Write-Host "Updating $oldConnString for $sh"  
     $ssrs.SetDataSourceContents($sh,$ref)  
   }  
   else  
   {  
     Write-Host "Would have updated $sh with " $ref.ConnectString  
   }   
 }  
 $ReportChanges | out-gridview  

To close out this post, near my home is a distillery that buys the bulk of their grains from local farmers.  If you are into Vodka, Bourbon or Rye, give http://www.45thparallelspirits.com/ a try.  Here is a picture of my favorite spirit they produce.

.