Continuous Deployment (CD) with Azure DevOps, SSIS, and SQL Server Database Projects

What are we doing here?

This is the second part of a two-part series covering 1) CI (continuous integration/build) and 2) CD (continuous deployment/release) for Visual Studio SQL Server Database and Integration Services project types using the Azure DevOps) platform.

Some background

The Visual Studio business intelligence (SSIS, SSRS, SSAS) and database projects types have long been the red headed step child of the Visual Studio ecosystem. Huge strides have been made in recent years but there are still some impediments, one of which is creating CI/CD pipelines for these projects in Azure DevOps.

Don't fret

This step-by-step guide will walk you through creating a release pipeline for a solution containing both a SQL Server Database project and an Integration Services project.

Assumptions (you know what they say...)

The following steps assume that you have already have a working Azure DevOps build definition. If not, you can refer to part 1 of this series.

Let's do it

1. Navigate to /Project/Pipelines/Releases and select New Release Pipeline.
   
2. Choose “Start with an Empty job”.
3. Name the Stage. An appropriate name might be the environment you’re deploying to such as “Development”.
4. Click “Add an Artifact”.
5. Select “Build” as the source type and choose the Project and Source (build pipeline) that will be deployed. If you haven’t already created a Build definition, see Continuous Integration (CI) with Visual Studio Team Services (VSTS), SSIS, and SQL Server Database Projects.
   
6. Click the lightning bolt on the newly created Artifact, enable the Continuous deployment trigger and add a branch filter for master or whatever branch will be deployed.
7. Click on the “1 job, 0 task” link in your newly created Stage.
   
8. Click the plus sign to the right of “Agent job” to add a new task.
   
9. Search for “Command Line” and add a new task.
   
10. Click on the new task and add populate the properties as follows.
    
    Keep in mind that this code is using SQL Server 2017. If you’re using another version, you’ll likely have to modify the Tool path.
    
    Argument text: /a:Publish /sf:"replace_with_build_output_root_location\$(Build.DefinitionName)\$(Build.BuildNumber)\replace_with_appropriate_path\replace_with_dacpac_name.dacpac" /tsn:replace_with_server_name /tu:replace_with_user_name /tp:replace_with_password /tdn:replace_with_database_name
11. Add another Command Line task and populate as follows.
    
    Argument text: /S /ModelType:Project /SourcePath:"replace_with_build_output_root_location\$(Build.DefinitionName)\$(Build.BuildNumber)\replace_with_appropriate_path\$(BuildConfiguration)\replace_with_ssis_ispac_name.ispac" /DestinationServer:"localhost" /DestinationPath:"/replace_with_appropriate_ssis_catalog_path"
12. The previous tasks use a variable to tell us which build configuration to use. Add the variable by clicking on the “Variables” tab, then click +Add, enter “BuildConfiguration”, and set a default value that is the name of the build configuration that is appropriate for this Stage of the pipeline. This will depend on the names of the configurations that have been created in your Visual Studio solution.
    
13. Always test! Create a release and make sure it deploys your projects correctly.

As always, if you have any questions feel free to drop me an email at david@elish.net.

Continuous Integration (CI) with Azure DevOps, SSIS, and SQL Server Database Projects

What are we doing here?

This is the first part of a two-part series covering 1) CI (continuous integration/build) and 2) CD (continuous deployment/release) for Visual Studio SQL Server Database and Integration Services project types on the VSTS (Visual Studio Team Services) platform.

Some background

The Visual Studio business intelligence (SSIS, SSRS, SSAS) and database projects types have long been the red headed step child of the Visual Studio ecosystem. Huge strides have been made in recent years but there are still some impediments, one of which is creating CI/CD pipelines in Visual Studio Team Services.

Don't fret

While it's not as straightforward as say a ASP.NET web application, with just a little finessing it's not overly difficult to build and deploy SQL Server Database and Integration Services project types. If you're familiar with creating VSTS build and release definitions it probably wouldn't take you too long to get it figured out. If you are new to this functionality it can definitely be a bit overwhelming. Hence, this step-by-step guide to standing up a basic VSTS build definition that includes both database and SSIS projects.

Assumptions (you know what they say...)

The following steps assume that 1) you have a VSTS account 2) a VSTS collection, 2) a VSTS project within the collection and 3) have a source control repository in VSTS Git, TFVC, GitHub, Subversion, Bitbucket Cloud, or Git (not necessarily hosted by VSTS).

This walk through will use an open source project called SSISProjectWeaver that is hosted on GitHub (using a different repository type requires little, if any changes). It's not necessary to know the details of the SSISProjectWeaver project other than that it contains a SQL Server Database project and two Integration Services projects.

<plug>If you're starting a new SSIS project it's worth checking out SSISProjectWeaver as it's a 100% non-invasive ETL framework that provides metadata-driven parallelism and dependencies between any two packages or groups of packages, extensive logging, reporting and dashboarding, alerting, and a bunch more. It's easy to set up and has been proven in large organizations with hundreds of packages running a day. You don't have to modify your existing code and if you don't like it you just stop using it. Nothing will have to change with your existing packages.</plug>

Let's do it

1. Open you VSTS project and click on the Build and Release tab.
2. Choose your source. For this example we are going to use GitHub.
3. Once you've connected to the repository, select the branch that you want to build and click Continue.
4. In the top right-hand corner under Select a template, click Empty Process. This will start us off with a blank build definition.
   
5. Name your build definition appropriately and then choose the Agent queue. In this example we are using a Hosted VS2017 queue. Depending on your project you might want to use a local/on-premise build agent of VS2015. It all depends on your project types, versions, dependencies, etc.
   
6. Before we define our build tasks let's create some variables. Click on the Variables tab and create the BuildConfiguration and BuildPlatform variables along with their corresponding default values.
   
   
7. Navigate back to the Tasks tab and click the + next to Phase 1. Search for and choose the Visual Studio Build task. This task will use the out-of-the-box msbuild template for building SQL Server database projects with a .dacpac as the build output.
8. Change the Display name to something meaningful.
9. Choose the SQL Server database project or solution to build.
10. Choose the version to build.
11. Use the newly created variables for the Platform and Configuration properties.
12. Save the task.
    
13. Navigate back to the Tasks tab and click the + next to Phase 1. Search for and choose the Command Line task. We will use this task as a workaround to a lack of an msbuild template for SSIS projects by calling invoking devenv to build the projects.
14. Change the version to 2.*(preview). This version allows multi-line commands similar to a .bat file.
15. Change the Display name to something meaningful.
16. Enter the following text into the Script box. The first 8 lines just echo (print line) info that is useful for debugging. The last line is the actual command that is being executed.
    echo BuildShare: $(BuildShare)
    echo Agent.BuildDirectory: $(Agent.BuildDirectory)
    echo Build.SourcesDirectory: $(Build.SourcesDirectory)
    echo Build.ArtifactStagingDirectory: $(Build.ArtifactStagingDirectory)
    echo System.DefaultWorkingDirectory: $(System.DefaultWorkingDirectory)
    echo BuildConfiguration: $(BuildConfiguration)
    echo BuildPlatform: $(BuildPlatform)
    echo Command: "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Devenv.com" "$(Build.SourcesDirectory)\your_project_or_solution_name.(sln|proj)" /build $(BuildConfiguration)
    
    
    "C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Devenv.com" "$(Build.SourcesDirectory)\your_project_or_solution_name.(sln|proj)" /build $(BuildConfiguration
17. Save the task.
18. Click on the Triggers tab and enable continuous integration. This will enable VSTS to execute this build definition each time code is committed to your source control repository.
    
19. Test the build definition by clicking on Queue.
20. Assuming your build was successful, you now have the CI half of your CI/CD pipeline and can auto-notify the world when one of your co-workers breaks the build!

Wrap Up

When it comes to technology, nothing is ever as simple as it appears. The SSISProjectWeaver solution is pretty straightforward in terms of code and dependencies. It's T-SQL and SSIS packages with no external dependencies. Building it is is very straightforward as you can see above. However, many projects are much more complex and require more complex configurations to set up a build definition. For example, the first time I created a build definition with an SSIS project it failed because I had references to 3rd party components. Of course Microsoft doesn't know I am using those components so it's not on their hosted build servers. To make it work I had to give up the convenience of using a hosted build server and stood up my own build server with all the necessary components. Food for thought. Don't be discouraged if you hit a few stumbling blocks on your way to automated build bliss!

Stay tuned for part two which will cover CD (continuous deployment) and will walk you through getting your code deployment automated.

As always, if you have any questions feel free to drop me an email at david@elish.net.

Visual Studio SQL Server Project (SSDT) OPENQUERY Warning

Using OPENQUERY in a stored procedure in a Visual Studio SQL Server project will result in the following warning.

 SQL70558: The column definition from this OPENQUERY function cannot be verified, and the operations involved with it might not be understood correctly.       

Depending on how your project and build is configured, this warning it may or may not fail the build. Regardless, if you have to use OPENQUERY and don't want the warning, you can use the Suppress TSql Warnings property on the stored procedure. To view this property in Visual Studio, click on the .sql file in the Solution Explorer window. You should now see this property in the last row of the Properties window. Enter the 70558 warning number. The warning should now be suppressed and will no longer appear in the Error window or the build output.

The Suppress TSql Warnings property can be used to suppress other warnings as well. To suppress multiple warnings just delimit them with a comma.

Publish SSDT Projects Without Visual Studio Using SqlPackage.exe

If you'd like to publish your SSDT database project but don't have access to your target database from the machine you run Visual Studio (with SSDT) and/or you don't have Visual Studio installed on your target server you can still publish your database by using SqlPackage.exe.

For those familiar with Visual Studio Database Projects (VSDB), SqlPackage.exe is the replacement for VSDBCMD.exe

Microsoft provides a WPI (web platform installer) for the Microsoft SQL Server 2012 Data-Tier Application Framework which lays down everything required to use the SqlPackage command line utility. You can also install the individual components from the Microsoft SQL Server 2012 Feature Pack if you prefer. They consist of the following:

1. dacframework.msi
2. SQLDOM.msi
3. SQLLS.msi
4. SQLSysClrTypes.msi

Once everything is installed you should be able to find SqlPackage.exe in a location similar to "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin."

Below is a basic sample of how to execute SqlPackage.exe.

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe /a:Publish /tsn:TargetServerName /tdn:TargetDatabaseName /sf:c:\YourDacPac.dacpac

For an exhaustive list of available parameters see MSDN.

SSDT: unresolved reference to object [dbo].[sp_executesql].

Procedure: Some_Procedure has an unresolved reference to object [dbo].[sp_executesql].

Look familiar? There's a simple solution. We just need to add a reference to the master database.

1) Right-click the References folder in Solution Explorer.

2) Select the "master" System database.

3) Voila. The reference appears under the folder and the warning goes away.

Creating a CLR Function in SSDT

Creating a CLR function in SSDT (SQL Server Data Tools) is not very difficult but if you've never done it before you might be wondering where to start. Here goes...

Enabling CLR
First things first. While not directly related to SSDT it is necessary to enable CLR on your SQL Server instance since whatever code you end up writing won't be able to execute until this is done. All we have to do is set the clr enabled configuration to 1. See below.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

SSDT Project Properties
It is necessary to tell your SSDT project which version of the .NET Framework to use. If you're running SQL Server 2008 R2 you can use .NET Framework 3.5 or lower. SQL Server 2012 support .NET Framework 4.0. To get to the screen below, right click your project in the Solution Explorer and select Properties and then click the SQLCLR tab on the left-hand size. Set your Target framework appropriately.

Add a Function to the Project
Right-click within the project and select Add > New Item... 

Choose SQL CLR C# User Defined Function to add a function to the project.

Define the Function
Below is a sample function from MSDN. Obviously you can create a function to do whatever you're trying to accomplish.

Publish
Publish your SSDT project as you normally would.

SSDT makes database development and deployment a breeze but sometimes the approach can be less than intuitive. If you're used to creating CLR objects manually you might have been wondering how to create the assembly and the T-SQL reference to the CLR object's method. SSDT is kind enough to do this work for us.

If you look at Object Explorer you'll see the new assembly (DBProj) and the new dbo.validatePhone function.

If you further explore the dbo.validatePhone function you'll see that it is a wrapper for your CLR function.

Use it

Simple as that.

Conclusion
CLR objects can be as complex as the code that you write but SSDT makes their development and deployment easier. Hopefully this post shows you just how easy it is.

The specified program requires a newer version of Windows - SSDT Install

Look familiar?

Unfortunately SSDT is not supported on Windows XP. The following Windows versions are supported.

1. Windows Vista SP2 or higher
2. Windows Server 2008 SP2 or higher
3. Windows 7 RTM or higher
4. Windows Server 2008 R2 RTM or higher

SSDT vs. VSDB

Microsoft strikes again by replacing a technology that was just beginning to gain a foothold with a product that has some nice improvements but does not replace all of the existing functionality. Here is a link to a long awaited feature comparison between the SQL Server Data Tools SQL Server database project (SSDT) and its predecessor Visual Studio Database project (VSDB).

The Future of Visual Studio Database Projects

It is with great sadness that I relay the decision by Microsoft to do away with the Visual Studio Database Project type that I love so much. This project type has given database developers the tools needed to implement high quality Database Development Life Cycle processes. Some highlights can be found here.

However, all is not lost. VSDB will be replaced by what was until yesterday referred to as project Juneau and is now officially named SQL Server Data Tools. It will also be hosted in the Visual Studio environment.

SSDT has many similarities with VSDB but is a complete rewrite from what I understand. Below are some of the features previously unavailable in VSDB.

1. Code navigation (Go to Definition, Find All References)
2. Enhanced code analysis and design time validation
3. Table designer similar to SSMS
4. Connected (live database) and disconnected (declarative) development
5. LocalDB - SQLExpress without the need to explicitly install any SQL Server instances
6. Entity Framework integration for a much more tightly integrated development experience between the data and application layers
7. Support for CLR objects
8. Support for SQL Server 2012 and SQL Azure databases (both support 2005, 2008, and 2008 R2)
9. Integration of SSIS, SSRS, and SSAS projects into the next edition of Visual Studio (currently Visual Studio 2011)

In my opinion, of the biggest shortcomings (as of SSDT CTP3) of SSDT is that there is no longer support for database unit tests. Automated unit tests have a huge impact of quality and I believe are key to robust DDLC.

While I am sad to see VSDB go, I am confident that Microsoft will create an even better tool in SSDT. I look forward to exploring it and sharing my findings in future posts.