Powershell has allways been the realm of IT Pros and DBAs for me. Though I’ve done my share of accidental DBA stuff, I haven’t put any effort into learning Powershell.
I know it’s a powerful scripting language, but there’s just so many things to learn and too few hours in which to learn in.
However, after working in closer relation with IT Pros at clients during regular releases at a couple of clients the last few years, and seeing how much time us developers spend every release in writing as detailed release documents as possible so that IT can do the deployment without too many mistakes I’ve come to realise I need to learn Powershell. If developer deliver deployment setup in a way IT is comfortable with (i.e. as powershell scripts) we save time to do development instead.
So, in February I managed to read enough on blogs to create a simple Powershell script for deploying a multi-dimensional cube. And a co-worker found a script to deploy SSIS ispac-files. The SSAS script simply deploys the XMLA deployment script that the SSAS Deployment wizard creates when you deploy to file instead of to the server. Slightly more work for me, but less for IT. Meaning one less point of confusion and/or failure.
We did run into problems with the SSIS script though. And several different attempts at scripts later still result in the same “file not found” error when starting the SSIS package from SQL Server Agent. I’m guessing there’s an encryption problem, though I can’t replicate the problem when I execute the script on my test server. However – in my experience, most of us develop with the encrypt with User Key setting per default. The deployment wizard changes the encryption settings, so this hasn’t come up as a problem before.
Researching this issue had me stumbling across this blog post from WIDBA, on how to copy SSIS projects from one server to another. This seemed to be a great solution to my problem. It’s supposed to a) create a new folder on the destination server if the folder doesn’t exist and then b) loop through the source server catalog and copy each project it finds to the destination server.
However, I could not get the script to work properly. For some reason it struggles with returning the values for the projects in the folder of the SSISDB catalog. So not only did it fail to create a new folder – or see if one allready existed – but it also failed to loop through projects. And as I’m too much of a freshman to find exactly what needed to be fixed in the code to solve the problem. Instead I had to find a around the problem…
As I know that the folder will allways exsist – this is a production server we’re deploying to after all – I don’t need to verify if it exist or attempt to create it if not.
And since I want us developers to simply deploy the projects ready for release I wanted to have a dedicated folder on our dev. server to make sure that only the approved versions where released – I created a separate source- and deployment folder parameter in the script.
The result looks like this:
$SourceServer = “Development”
$DestinationServer = “Production”
$ProjectFolder = “Deployment”
$destProjectFolder = “SSIS”
[Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.Management.IntegrationServices”)
$ISNamespace = “Microsoft.SqlServer.Management.IntegrationServices”
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 $destProjectFolder}
#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”
}
}
As you can see – it’s not a major change – but at east I got it to work.
And this has the bonus of making the pre-release days a much simpler affair for us developers.
No need to write detailed descriptions for each SSIS project that IT shall release.
No need to remind IT to “allways do a get latest” from TFS, especially nice if you run into a bug that you missed in pre-release testing and need to re-deploy.
IT won’t have to relate to more than one single script – and a script language and tools they are familiar with – it doesn’t even have to be downloaded for every release.
I had the first release deployment with this script today, and it worked exactly as well as I had hoped – though today was merely a single-project release.
Meanwhile, I’ve also been playing around with Azure – and the powershell scripts that comes bundled with the service – and I realise how much simple my life will be if I can get a grip on the powershell language.
So, now I’ve invested in books on Powershell – and need to find time to learn more. This has the potential to save a lot of time in a production enviroment also in the future, as well as making my play dates in the cloud that much easier. (And if you attended my session at SQLUG Oslo 14/4 – had I mastered Powershell properly, you would most likely have been able to see the demo I had planned on showing…)