WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Monday, February 25, 2013

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.

Thursday, February 14, 2013

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  

Monday, February 11, 2013

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.
.

Tuesday, February 5, 2013

Slightly Intelligent SSAS Processing with Powershell

Recently, I had a story to do some partitioning in one of our cubes to help performance on some data that was loaded once per month and had years of history.  The process is pretty straightforward and part of the convention was to use the word "Current" at the end of the partition name that we would want to process after the ETL was complete.  Once you get into partition specific processing, the XMLA goes from simple to a whole bunch of commands, not complicated but cumbersome.  The very next sprint yielded adding another measure group and even more dimensions, the setup of the XMLA was not hard, but manual and prone to error if you are anything like me.

Quick interruption for a public service announcement: Maple syrup season will hopefully be starting in about one month.  Its a great time to be out in the woods, and the product is pretty good too.



Ok, back to it.  Having worked on some SSAS with Powershell this ended up being a straightforward way to process the dimensions and any partition with the word "Current" in it.  This really is a template for me as I want to utilize a naming convention to make this easier across the board, and if a measure group contains no "Current partitions, I want to process the partitions in that measure group.  Its a nice start to what will likely be many more "management" type scripts in working with SSAS to automate some of the mundane parts.

Here is the base script, free of fluff and error handling.  Remember SQL Agent is really specific about scripts throwing errors....

 $srcSSASName = "MySSASServer"  
  $ssasDb = "Brewery"  
  $partitionSpecificTerm = "Current";  
  [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null  
  $source = New-Object Microsoft.AnalysisServices.Server  
  $source.Connect($srcSSASName)  
 
  #Grab the SSAS Database handle
  $Db = $source.Databases.GetByName($ssasDb)  

  #For the DB, Process any Dimensions that need it  
  foreach($dimension in $Db.Dimensions )  
  {  
    Write-Host "Processing dimension " $dimension.Name  
    #$dimension.Process("ProcessFull");  
  }  
 
  #Loop through each cube in the SSAS database  
  foreach($cube in $Db.Cubes)  
  {  
   foreach($measuregroup in $cube.MeasureGroups)  
   {  
   foreach($partition in $measuregroup.Partitions)  
   {  
    if($partition.Name -like "*$partitionSpecificTerm*")  
    {  
     Write-Host "Processing specific partition " $partition.Name  
     #$partition.Process("ProcessFull");  
    }  
    if($partition.State -ne "Processed")  
    {  
     Write-Host "Processing unprocessed partition " $partition.Name  
     # $partition.Process("ProcessFull");  
    }  
   }  
   if($measuregroup.State -ne "Processed")  
   {  
    Write-Host "Problem with measuregroup " $measuregroup.Name  
   }  
   }  
  }