I can speak SQL

Fluent SQL speaker

Posts Tagged ‘Automation’

SQLSaturday presentation – CI for DB

Posted by kulmam92 on September 22, 2014

I presented “CI for DB” at SQLSaturday #340.(http://www.sqlsaturday.com/340/schedule.aspx)
Here’s the slides.

Best,
Yong

Advertisements

Posted in Deployment, SQL SERVER, SSIS | Tagged: , , , , , , , | Leave a Comment »

SSIS 2012 Continuous Integration using Jenkins and Octopus – A Journey to DB deployment automation

Posted by kulmam92 on September 4, 2013

Finally I was able to putting each component together. As I implement CI and Deployment system for the DB, I could understand why it is hard to find a novice friendly guide which explains all the basics and also provides step by step instructions. There are way too many options that you need to pick and choose. Furthermore, it’s not that simple to build one. My implementation isn’t even close to the ideal system. However, I’m sharing this hoping this lightens up those who are planning to create a DB CI system by looking at the working system.

Big Picture

DB CI Process

This diagram shows how I set up deployment system for SSIS. I’m using the same structure for SSRS and DB schema too.

Each Component

Component Tool Comment
SCM Visual SVN There are many SCM (Source Control Management) tools and you may choose different solutions. I used visual SVN because of the following reasons. Frist, SVN has better integration with the red-gate tools that I’m using. Second, Visual SVN is free. Having everything checked in is the one of the basic starting point for CI (Continuous Integration) and deployment. I used AnkhSVN as a SVN client since it’s integrated with visual studio. Key benefit of this is that it allows you to perform most of the version control operation directly from the visual studio.
CI Jenkins Jenkins is an open source continuous integration tool. If you are not accustomed to CI, I highly recommend you to read David Atkinson’s article Continuous integration for databases using Red Gate tools. This will give you a good overview of how it works and what’s involved in it.
Build MSBuildx32 As I mentioned in my previous post (Building SSIS 2012 using MSBuild), MSBuild doesn’t build SSIS by default. So you need to install SQL Data Tools from the SQL2012.ISO and copy Microsoft.SqlServer.IntegrationServices.Build.dll from the CodePlex project (Microsoft SQL Server Community Samples: Integration Services).
Testing X I searched for SSIS testing solution but wasn’t able to find good one.
Packaging Nuget NuGet is the package manager for the Microsoft development platform including .NET. You may consider a NuGet package (a .nupkg file) as zip file containing files to deploy and a manifest file. A manifest file describes the contents of the package and what needs to be done to add or remove the library.
You can get more details from the presentation of Damian Edwards “NDC 2011: NuGet in a caramel coated nutshell
Artifact Repository Nuget I used local NuGet server to keep packages. I’m currently reviewing Artifactory and Nexus.
Deployment OctopusDeploy Octopus is an automated release management system for .net developers. I chose this because, first, web developers were using this for their deployment, second, it’s easy to run command on a remote machine due to its agent, third, it keeps release on the target machines to make rollback easier.

CI Server

Below is the list of installed applications and files on the CI server.

  • Jenkins
  • Jenkins plug-ins
    • build-name-setter
    • Email-ext plugin
    • Extensible Choice Parameter plugin
    • Hudson PowerShell plugin
    • Jenkins Dynamic Parameter Plug-in
    • Jenkins Mailer Plugin
    • Jenkins Subversion Plug-in
    • MSBuild Plugin
    • Scriptler
    • Role-based Authorization Strategy
  • Build
    • MSBuild
    • Microsoft.SqlServer.IntegrationServices.Build.dll
    • SQL Data Tools from the SQL2012.ISO
    • SSIS.MSBuild.proj
  • Nuget Server
  • Octopus depoy

I’m not going to cover how to install Jenkins and other applications in the post. I may cover that in later post though. The main focus of this post will be how to create Jenkins and Octopus project to deploy SSIS.

Jenkins Project

Overview

Below shows how the project works.

Jenkins SSIS project

Global Setting

Scriptler

Jenkins –> Scriptler –> Add a new Script

Scriptler can be used if you want to create value dynamically or handle some complex logics.

DynamicChoice_OctopusUserKey.groovy

I need to pass userkey value to the Octopus using API. API is executed under the user’s privilege tied with userkey. This groovy script will convert current Jenkins user to a proper Octopus userkey.

Jekins.Global.DynamicChoiceOctopusUserKey

Script

def result = ["-------"]

def auth=jenkins.model.Jenkins.instance.getAuthentication()

if ( auth == null ) return result

def userId=auth.getName()

if ( userId== "admin_speaksql" ) return "JNN2005CGJVGTVJZR3CH1GTKZ9"

if ( userId== "admin_01" ) return "KUE2489CGJVGTVJYE3CH1GKHZ8"

Global Choice Parameter

Jenkins –> Manage Jenkins –> Configure System

deploy_target

Environment list

Jekins.Global.GlobalChoiceParameterSSIS

Extended Email Notification

Notification email format setting

Jekins.Global.Extended E-mail Notification

Project Setting

Create a new Project(Job)

You can create a Project by selecting “Build a free-style software project” or choose “copy existing job” if you are going to create a similar job.

Jenkins –> New Job

Jekins.Project.NewJob

Configure SSIS Project

Jekins –> Project name(DW.SSIS.Template) –> Configure

Build with Parameters

Jekins.Project.Parameter00

Project Name

I declared Project Name parameter since Jenkins Job name is different from the SSIS project name.

Jekins.Project.Parameter01_ProjectName

Version Number

I’m currently using static version number setting

Jekins.Project.Parameter02_VersionNumber

Deploy Target

Deploy environment selection

Jekins.Project.Parameter03_DeployTarget

Release Note

Release note which will be passed to the Octopus and included in the deployment success notification email

Jekins.Project.Parameter04_ReleaseNote

SSIS Protection Password

I use EncryptSensitiveWithPassword as a protection level of SSIS project. So I need to provide protection password.

Jekins.Project.Parameter05_SSISProtectionPassword

Octopus UserKey

This will pick up UserKey of Octopus using “DynamicChoice_OctopusUserKey.groovy” that we set globally from the Scriptler menu.

Jekins.Project.Parameter06_OctopusUserKey

Source Code Management

Source code repository URL

Jekins.Project.Parameter07_SourceCodeManagement

Build Environment

Define build name format.

Jekins.Project.Parameter071_BuildName

Build

Copy MSBuild script for SSIS(SSIS.MSBuild.proj)

Since the same build script will be used for all SSIS project build. I keep that file in a folder and copy it to the working directory of that project.

Jekins.Project.Parameter08_Build01.CopySSISMSBuild.proj

You can get SSIS.MSBuild.proj file from this link : https://gist.github.com/kulmam92/6433329

Prepare to create a Nuget package using MSBuild

You need two things to be able to create a Nuget package using MSBuild. First, spec file that describes package name, dependency and etc. Second, Build script for MSBuild. ImportTargetsForDB.ps1 file does that two things.

Jekins.Project.Parameter08_Build02.CopyNuspecFileImportNugetbuildscript

You can get related scripts from this link : https://gist.github.com/kulmam92/6433645

Build using MSBUild

This will build project and create a Nuget package using build output

Jekins.Project.Parameter08_Build03.MSBuild2

Command Line Arguments

/t:SSISBuild,DeployNugetForDB /p:SSISProj=%SSIS_project_name%,Configuration=Development,ProtectionLevel=EncryptSensitiveWithPassword,ProjectPassword=%SSIS_protection_password%,DeployPackageName=%JOB_NAME%

Call Octopus deploy API to deploy Nuget package

Jekins.Project.Parameter08_Build05.CallOctopusAPI

c:\octopus\octo create-release --server=http://dbdeploy.XXXX.XXX:8880/api --project="%JOB_NAME%" --deployto="%deploy_target%" --waitfordeployment --apiKey=%UserKey% --releasenotes="<ul><li>[Updates] : %release_note_update%</li><li>[Impact] : %release_note_impact%</li><li>[Jira Tasks] : %release_note_jira%</li></ul>"

Post Build Action

Send notification Email

Success notification will be sent out from the Octopus deploy. The will send notification when the job fails.

Jekins.Project.Parameter09_NotificationEmail

Octopus Deploy Project

Global setting

Environment

Create necessary environments and add servers. You can add tags to the server and that tag will be used to determine deployment target. Since I set up single centralized ETLDB for all environment, the same server is appearing in multiple environments.

Octopus.Environment

Create a SSIS Projects

Variables

Below is the list of the variables that I declared on each project.

Octopus.Project.Variables

Steps

This is the actual actions that the Octopus does. SSIS deploy project is consist of three projects.

Octopus.Project.Steps

File deploy

This step will grab Nuget package from the Nuget repository and deploy it to the target. Deploy role is defined using tag that you chose for the server.

Octopus.SSRS.Project.Step01.FileDeploy

Octopus.SSRS.Project.Step01.FileDeploy2

PS Script to load SSIS package to SSIS catalog DB

Powershell script in this step will deploy SSIS project to the SSIS catalog DB. SSIS project’s deployment model should be project based model since SSIS catalog DB only accept that model.

Octopus.Project.Step02.PSScript

powershell.exe -command e:\project_ssis\Install-ISProject.ps1 -IspacFullName "$LocalPackageDirectoryPath\$LocalProjectName.ispac" -ServerInstance "ETLDB1" -CatalogFolderName "$LocalEnvironmentName" -ISProjectName "$LocalProjectName"

You can get powershell scripts from this link : https://gist.github.com/kulmam92/5939944

Send Email Notification

Octopus will send out notification if all steps are finished successfully.

Octopus.Project.Step03.emailNotification

Subject

[CI] #{OctopusProjectName} – #{Octopus.Release.Number} deployed to [#{Octopus.Environment.Name}]

Body

<h2>[Notes]</h2>
<p>#{Octopus.Release.Notes}</p>
<h2>[ReleaseLink]</h2>
<p>Check console output at https://dbdeploy.AAAA.CCC/#{Octopus.Web.ReleaseLink} to view the results</p>

Deployment

You are now ready to deploy a project using Jenkins and Octopus. You can start deploy from the following location.

Jenkins –> Project name(DW.SSIS.Template) –> Build with Parameters

Below is the screenshot of the page.

Jekins.Project.BuildwithParameters

Clicking the build history will take you to the build status page.

Jenkins.BuildwithParameters.Status

If you want to check the detail log, you can check “Colsole Output”

Jenkins.BuildwithParameters.ConsoleOutput

Summary

I covered how I implemented SSIS project deployment system using Jenkins and Octopus deploy. This is not a continuous integration system and still has many rooms to improve. However, I hope you can get some tangible idea about the working SSIS deployment system.

Posted in Deployment, SQL SERVER, SSIS | Tagged: , , , , , | 6 Comments »