I can speak SQL

Fluent SQL speaker

Posts Tagged ‘DEPLOYMENT’

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 »

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 »

Building SSIS 2012 using MSBuild – A Journey to DB deployment automation

Posted by kulmam92 on June 7, 2013

This post is the first in a series of entries I am planning to write detailing my experience while attempting to create a process in deploying a DB schema, SSIS and SSRS.

SQL Server Data Tools – Visual Studio Shell

SQL Server Data Tools (SSDT) is an extension of the environment provided by Visual Studio 2010 Shell.


Wikipedia describes Visual Studio Shell

Visual Studio 2008 introduced the Visual Studio Shell that allows for development of a customized version of the IDE. The Visual Studio Shell defines a set of VSPackages that provide the functionality required in any IDE. On top of that, other packages can be added to customize the installation. The Isolated mode of the shell creates a new AppId where the packages are installed. These are to be started with a different executable. It is aimed for development of custom development environments, either for a specific language or a specific scenario. The Integrated mode installs the packages into the AppId of the Professional/Standard/Team System editions, so that the tools integrate into these editions.

The binary of the SQL Server Data Tools is devenv.exe typically found in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE.



SSIS Deployment model in 2012

To give an explanation as to why I decided to use the Project Deployment model with EncryptSensitiveWithPassword as the protection level, I will briefly describe it here. I will cover the topic at a greater depth in a later series.

Project Deployment Model

The 2012 version of Integration Services supports two deployment models: the Project Deployment model and the Package Deployment model. The Project Deployment model enables you to deploy your projects to the Integration Services server. The Project Deployment model has many benefits compared to the Package Deployment model. You can read Deployment of Projects and Packages and SSIS Package Deployment Model in SQL Server 2012 to get more information about the differences between the two deployment methods.


If you build an SSIS project file using the project deployment model, a project deployment file will be created (.ispac extension). The project deployment file is a self-contained package that includes only essential information about the packages and parameters in the project. You can check it contents using a file archiver like 7zip.


If you double click the created (.ispac) file, the Integration Service Deployment Wizard will be launched. The Integration Service Deployment Wizard has a command line interface (ISDeploymentWizard.exe).  You can get the created command in the summary screen.


SSIS Catalog

The SSIS catalog is a new type of storage for SSIS. It’s a user database so you can query tables and easily backup/restore the whole repository. You may refer to SSIS Catalog and SSIS 2012 Configuration Guide – Part 1: Introduction for more information.

The SSISDB catalog is central to working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server.In an example case, you would need to set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.


Security for SSIS Packages

To protect the data in an Integration Services package, you can set the protection level.


Access Control for Sensitive Data in Packages give us an example of protection level settings based on the package life cycle.

Typically, you change the protection level as listed through the following steps as requirements change:

  1. During development, leave the protection level of packages set to the default value of EncryptSensitiveWithUserKey. This setting helps ensure that only the developer is able to see sensitive values in the package. You can also consider using EncryptAllWithUserKey, or DontSaveSensitive.
  2. When it is time to deploy your packages, the protection level should be changed to one that does not depend on the developer’s user key. Therefore you would typically have to select EncryptSensitiveWithPassword, or EncryptAllWithPassword. Encrypt the packages by assigning a temporary strong password that is also known to the operations team for the production environment.
  3. After the packages have been deployed to the production environment, the operations team can re-encrypt the deployed packages by assigning a strong password that is known only to them. Alternatively, they can encrypt the deployed packages by selecting EncryptSensitiveWithUserKey orEncryptAllWithUserKey and using the local credentials of the account that will run the packages.

Command line build of SSIS packages

You may assume that MSBuild will be able to support an SSIS project file (..dtproj extension) by default, though this is not the case. You may see the following error message when attempting to build.



As I have mentioned earlier, SSDT functionality can be utilized by running devenv.exe, which also provides access to a command line interface. You may refer to Devenv Command Line Switches for the available options. Building using devenv.exe works fine as long as your project’s protection level is not EncryptSensitiveWithPassword, or EncryptAllWithPassword. You can get devenv.exe by installing SQL Data Tools from the SQL2012.ISO. Just a note, The free SSDT tools from the web don’t have BI functionality. You can read Richard Fennell’s Getting SQL 2012 SSIS packages built on TFS 2012.2 for additional information.

D:\App_temp\SSIS\DeployDemo>”C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\devenv” DeployDemo.sln /build “Development|Default”

Microsoft (R) Visual Studio Version 10.0.40219.1.
Copyright (C) Microsoft Corp. All rights reserved.
—— Build started: Project: DeployDemo, Configuration: Development ——
Build started: SQL Server Integration Services project: Incremental …
Starting project consistency check …
Project consistency check completed. The project is consistent.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\DeployDemo.dtproj’get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\Project.params’ get updated.
File ‘D:\App_temp\SSIS\DeployDemo\DeployDemo\obj\Development\LEN-062_JY2012.nxa_repository.conmgr’ get updated.
Applied active configuration to ‘Project.params’.
Applied active configuration to ‘Package.dtsx’.
DeployDemo -> D:\App_temp\SSIS\DeployDemo\DeployDemo\bin\Development\DeployDemo.ispac
Build complete — 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

However, if you change the protection level to either EncryptSensitiveWithPassword or EncryptAllWithPassword, a window will pop up and ask you to enter the protection password. There’s no switch that allows you to provide a password from the command line.


It was due to this issue that I explored using MSBuild for the build process.


It’s important to know what MSBuild is in order to understand how the new solution works. Let’s read the description from MSDN’s MSBuild page:

The Microsoft Build Engine is a platform for building applications. This engine, which is also known as MSBuild, provides an XML schema for a project file that controls how the build platform processes and builds software. Visual Studio uses MSBuild, but it doesn’t depend on Visual Studio. By invoking msbuild.exe on your project or solution file, you can orchestrate and build products in environments where Visual Studio isn’t installed.

Visual Studio uses MSBuild to load and build managed projects. The project files in Visual Studio (.csproj, .vbproj, .vcxproj, and others) contain MSBuild XML code that executes when you build a project by using the IDE. Visual Studio projects import all the necessary settings and build processes to do typical development work, but you can extend or modify them from within Visual Studio or by using an XML editor.

In short MSBuild uses an XML-based project file format to retrieve the necessary configuration:

MSBuild.exe MyProj.proj /property:Configuration=Debug

You may consider reading Walkthrough: Creating an MSBuild Project File from Scratch to understand more about the MSBuild Project file (AKA MSBuild script). The error message I got when I tried to build an SSIS project file (.dtproj extension) earlier indicated that the XML format of an SSIS project file is not recognized by MSBuild.

SSISMSBuild and MSBuild script

There’s a codeplex project (Microsoft SQL Server Community Samples: Integration Services) where you can create Microsoft.SqlServer.IntegrationServices.Build.dll. You can also get an MSBuild script that links Microsoft.SqlServer.IntegrationServices.Build.dll from here: msbuild script for building and deploying a SSIS .ispac file. Below is the MSBuild script from that link.

<?xml version="1.0" encoding="Windows-1252"?>
<Project  xmlns="http://schemas.microsoft.com/developer/msbuild/2003"
  <!--Requires a property called $(SSISProj) to be defined when this script is called-->
  <UsingTask TaskName="DeploymentFileCompilerTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISBuild" Condition="'$(SSISProj)' != ''">
    <Message Text="**************Building SSIS project: $(SSISProjPath) for configuration: $(CONFIGURATION)**************" />

  <UsingTask TaskName="DeployProjectToCatalogTask" AssemblyFile="C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.IntegrationServices.Build.dll" />
  <Target Name="SSISDeploy" Condition="'$(SSISProj)' != ''">
    <Message Text="**************Publishing SSIS project: $(SSISProj) to: $(SSISServer) to folder: $(PROJECTNAME)**************" />

This MSBuild script performs two functions: SSISBuild and SSISDeploy. I saved the code to a file called “SSIS.MSBuild.proj” and executed MSBuild.exe illustrated below.

D:\App_temp\SSIS\DeployDemo\DeployDemo>MSBuild SSIS.MSBuild.proj /t:SSISBuild,SSISDeploy /p:SSISProj=”DeployDemo”,Configuration=”Development”,ProtectionLevel=”EncryptSensitiveWithPassword”,ProjectPassword=”1234″,SSISServer=”LEN-062\JY2012″,ProjectName=”DeployDemo”


How to build Microsoft.SqlServer.IntegrationServices.Build.dll

I will explain how to compile Microsoft.SqlServer.IntegrationServices.Build.dll using the source code from the codeplex project (Microsoft SQL Server Community Samples: Integration Services). I’d like to mention the small challenges that I faced while trying to compile the source code.

  1. Prerequisites: The following should be installed on a machine that you are going to use.
    1. MS SQL Server 2012
    2. Visual studio 2010
  2. Download source code
  3. Unzip downloaded file
  4. Go to SSISMSBuild project folder (sqlsrvintegrationsrv-100610\main\SSISMSBuild).
    There are two projects under SSISMDBuild, 2008 and Project. Project is for 2012 is C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project
    SSIS Junkie mentioned SSISMSBuild is included in MSBuild extension pack. However, that integration is done only for a 2008 project.
  5. Reference hint path modification
    Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj from the text editor.image_thumb26Change the highlighted part to full path.image_thumb29
  6. Open C:\sqlsrvintegrationsrv-100610\main\SSISMSBuild\Project\Microsoft.SqlServer.IntegrationServices.Build.csproj using visual studio.
    If you try to compile it, it will generate an error due to a missing key file. You can see the following line from the project file.
  7. Sign an AssemblyWith the project node selected in Solution Explorer, from the Project menu, click Properties (or right-click the project node in Solution Explorer, and click Properties). In the Project Designer, click the Signing tab. Then you will see the following screen.
  8. Choose new and provide file name and uncheck Protect my key file with a password.

    Check Signing an Assembly in Visual Studio for more details
  9. Compile
  10. Copy created Microsoft.SqlServer.IntegrationServices.Build.dll file to the following folder
    C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies


It took me quite a while to figure this out and I hope this can save you time when setting up an automated build process using MSBuild. I think it would be great if MSBuild adds support for SSIS projects by default in the future.

Posted in SQL SERVER, SSIS | Tagged: , , , , , , , , , | 53 Comments »