I can speak SQL

Fluent SQL speaker

Archive for the ‘Deployment’ Category

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.



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

How to install Git Source Control Provider on SQL Server Data Tools

Posted by kulmam92 on September 5, 2013

Git Source Control Provider

If you decide to use Git as a SCM (Source Control Management) system, you may decide to use Git Source Control Provider since it’s integrated to the Visual Studio (SQL Server Data Tools). This is not available from the Extension Manager’s online gallery if you try to search it from the SQL Server Data Tools. However, you can download Git Source Control Provider from its codeplex page : http://gitscc.codeplex.com/.


If you click the “Installer for VS2010, VS2012”, “GitSccProvider.vsix” will be downloaded. Double clicking that file should install Git Source Control Provider but you will see the following error message.


The message means that SQL Server Data Tools which is a Visual Studio 2010 shell edition is not supported by Git Source Control Provider. Fortunately, there’s a workaround.


What is VSIX

It will be easier for you to understand what I’m doing, if you know what VSIX is. Below is the definition of VSIX quoted from the Quan To’s Visual Studio Extensibility blog.

The VSIX file is the unit of deployment for a Visual Studio 2010 Extension. Visual Studio will recognize the VSIX extension and install the contents of the file to the right location.

A VSIX file is a zip file that uses the Open Packaging Convention. You can rename the .VSIX extension to .ZIP and use any zip browser (including the Windows File Explorer) to browse its contents.In short it’s a deployment format for a Visual Studio Extension.


One of key files in VSIX is “extension.vsixmanifest”.


This file is the manifest that describes the extension. Basically this is a XML file. The details about XML schema can be found from VSIX Extension Schema 2.0 Reference. Element called “<SupportedProducts>” tells compatible versions and editions. As you can see “IntegratedShell” is not listed.

<SupportedProducts> <VisualStudio Version=”10.0″> <Edition>Ultimate</Edition> <Edition>Premium</Edition> <Edition>Pro</Edition> </VisualStudio> <VisualStudio Version=”11.0″> <Edition>Ultimate</Edition> <Edition>Premium</Edition> <Edition>Pro</Edition> </VisualStudio> </SupportedProducts>

So I added “IntegratedShell” to the <SupportedProducts> element like below and saved the changes.


You will see the following message, if you close the notepad.


Now you are ready to install Git Source Control Provider. If you double click the “GitSccProvider.vsix” file, you will the following installation popup.



Even though Git Source Control Provider doesn’t officially support Visual Studio Shell edition (SQL Server Data Tools), it’s working fine after the installation.  I modified the supported version list to fool the installer. I hope the developer of  Git Source Control Provider adds Visual Studio Shell edition to the official supported list.

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


Below shows how the project works.

Jenkins SSIS project

Global Setting


Jenkins –> Scriptler –> Add a new Script

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


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.



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


Environment list


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


Configure SSIS Project

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

Build with Parameters


Project Name

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


Version Number

I’m currently using static version number setting


Deploy Target

Deploy environment selection


Release Note

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


SSIS Protection Password

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


Octopus UserKey

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


Source Code Management

Source code repository URL


Build Environment

Define build name format.



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.


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.


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


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


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.


Octopus Deploy Project

Global setting


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.


Create a SSIS Projects


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



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


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.



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.


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.



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


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


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.


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


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



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 »

SSISDB ERD – A Journey to DB deployment automation

Posted by kulmam92 on June 17, 2013

I decided to reverse engineer the SSISDB to have a better understanding of the SSIS catalog. Reza Rad posted a really good article called “SSIS 2012 Catalog series“. However, I thought it would be better to create my own version of an ERD to have better understanding of it. Here’s what i came up with.



Yon can download the pdf version of this from SSISDB_ERD


Main tables

Object related tables

There are four object tables: folder, environment, project and package. I think you can easily figure out the purpose of each table. Other than these key tables are the tables for relationship (environment_reference), parameters, versions and permissions. SSISDB keeps a project’s previous versions as binary data in the internal.object_versions table. You can specify how many versions that you want to keep by using the properties of an SSIS catalog.



This table contains one row for each operation in the Integration Services catalog. You can track all logical operations that were performed on the server using this table, such as project deployment or package execution. Below are the operation_type values from BOL:

operation_type Value operation_type Description object_id Description object_name Description
1 Integration Services initialization NULL NULL
2 Retention window (SQL Agent job) NULL NULL
3 MaxProjectVersion (SQL Agent job) NULL NULL
101 deploy_project (Stored procedure) Project ID Project name
106 restore_project (Stored procedure) Project ID Project name
200 create_execution and start_execution (Stored procedures) Project ID NULL
202 stop_operation (Stored procedure) Project ID NULL
300 validate_project (Stored procedure) Project ID Project name
301 validate_package (Stored procedure) Project ID Package name
1000 configure_catalog (Stored procedure) NULL NULL


[executions] has a one-to-one relationship with [operations], which means the [execution] table is a subset of the [operations] table. If you look at the ERD closely, the relationship from operation to execution is made only when the operation type is 200 – create_execution and start_execution (Stored procedures). This means only operation_type value 200 will generate a record in the execution table.


If you want to know which package in a project was executed, you can use the following query. This is the creation script of the [catalog].[executions] view.

SELECT     execs.[execution_id],
opers.[operation_guid] as [dump_id],
FROM       [internal].[executions] execs INNER JOIN [internal].[operations] opers
ON execs.[execution_id]= opers.[operation_id]
LEFT JOIN [internal].[operation_os_sys_info] ossysinfos
ON ossysinfos.[operation_id]= execs.[execution_id]
WHERE      opers.[operation_id] in (SELECT id FROM [internal].[current_user_readable_operations])
OR (IS_MEMBER(‘ssis_admin’) = 1)
OR (IS_SRVROLEMEMBER(‘sysadmin’) = 1)

Message related tables

If you want to check the execution log of a package, these are the tables that you need to query. [operation_messages] and [event_messages] have a one-to-one relationship similar to [operation] and [event]. [event_messages] is a subset of [operation_messages]. The below is the source code of the [catalog].[event_messages] view.

Declare @ExecutionID NVarChar(max)
select @ExecutionID=N’132′
SELECT     opmsg.[operation_message_id] as [event_message_id],
message_source_name =
WHEN (opmsg.message_source_type = 10) THEN ‘ISServerExec’
WHEN (opmsg.message_source_type = 20) THEN ‘Transact-SQL stored procedure’
ELSE eventmsg.message_source_name
FROM       [internal].[operation_messages] opmsg LEFT JOIN [internal].[event_messages] eventmsg
ON opmsg.[operation_message_id] = eventmsg.[event_message_id]
WHERE      opmsg.[operation_id] = @ExecutionID

If you execute an SSIS package deployed to a SSIS catalog and it fails, you will see a message similar to the following.

Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  4:09:59 PM  Package execution on IS Server failed. Execution ID: 120092, Execution Status:4.  To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report  Started:  4:09:59 PM  Finished: 4:10:27 PM  Elapsed:  28.641 seconds.  The package execution failed.  The step failed.

You will need to use either a prebuilt standard report or query the SSISDB tables to get a detailed error message. The following screenshot shows where you can locate that report. If you decide to query the SSISDB, you may want to consider using the SSIS Reporting Pack developed by Jamie Thomson.


Table definition

Here I list the tables and its definitions copied from BOL.


Table name


internal catalog_encryption_keys Contains SSISDB encryption key
internal catalog_properties Contains the properties of the Integration Services catalog.
internal data_type_mapping Contains SSIS data type and SQL data type mapping
internal environment_permissions
internal environment_references Contains the environment references for all projects in the Integration Services catalog.
internal environment_variables Contains the environment variable details for all environments in the Integration Services catalog.
internal environments Contains the environment details for all environments in the Integration Services catalog. Environments contain variables that can be referenced by Integration Services projects.
internal event_message_context
internal event_messages Contains information about all messages passed during events like OnWarning, OnError
internal executable_statistics Contains Detailed execution log for each executable
internal executables Contains Information about each executable object in packages
For example : Package itself, Execute SQL Task, ETC.
internal execution_component_phases Contains Detailed execution log for each data flow component.
internal execution_data_statistics Contains Number of rows sent through data flow and few other details
internal execution_data_taps Contains information about data taps
Data Taps are new features in SSIS 2012 which provides ability to log whole data stream in one of data flow’s data paths. and the best part of story is that you can add and remove data taps to a package at the time of execution
internal execution_parameter_values Contains the actual parameter values that are used by Integration Services packages during an instance of execution.
internal execution_property_override_values Contains information about property values that set at the time of execution of package and overrides default values.
internal executions Contains the instances of package execution in the Integration Services catalog. Packages that are executed with the Execute Package task run in the same instance of execution as the parent package. With every execution of package, an Execution_id will be assigned
internal extended_operation_info Contains extended information for all operations in the Integration Services catalog.
internal folder_permissions
internal folders Contains the folders in the Integration Services catalog.
internal object_parameters Contains the parameters for all packages and projects in the Integration Services catalog.
internal object_versions Contains the versions of objects in the Integration Services catalog. In this release, only versions of projects are supported in this view.
internal operation_messages Contains messages that are logged during operations in the Integration Services catalog.
internal operation_os_sys_info Contains system information like cpu count, available physical memory and total physical memory at the time of operation
internal operation_permissions Contains the effective permissions for the current principal for all objects in the Integration Services catalog.
internal operations Contains the details of all operations in the Integration Services catalog. Each operation can be applied to an object and have a status as a result.
internal packages Contains the details for all packages that appear in the Integration Services catalog.
internal project_permissions
internal projects Contains the details for all projects that appear in the Integration Services catalog.
internal validations Contains the details of all project and package validations in the Integration Services catalog.


Code definition tables

Reza Rad shared a script that creates code conversion tables. I created and added a table for permsssion_type. You can download the script from SSISDB_CodeTables.

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

A Journey to DB deployment automation-Planning

Posted by kulmam92 on June 8, 2013

The mind map below shows what I had in mind when planning and implementing automation for DB Deployment. I will cover the details in a later series.


Covered topics

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