Wednesday, 12 June 2019

Azure DevOps - Build SSIS project (CI)

I have my SSIS project in Git (Azure Repos Git) and I want to build my project in Azure DevOps. How do I add Continuous Integration (CI) for my SSIS project?
DevOps - Build pipeline

For this example we start with an SSIS project already in Azure Repos Git and build the project automatically when committing changes. Deployment (CD) of SSIS will be handled in a separate post.

1) New Build Pipeline
Go to Pipelines and then to Builds. In the list op pipelines you will find the + New sign. Click on it and choose New build pipeline. Now you can choose where your code is located. However on the bottom you will find the option "Use the classic editor to create a pipeline without YAML." For this example we will use this option since SSIS is a bit old school. As last step choose the Empty job and give your CI pipeline a useful name.
Creating new CI pipeline in DevOps for SSIS

2) NuGet Tool Installer
Since we will be needing an SSIS building tool that is available as NuGet, we first need to install NuGet itself. Click on the plus icon behind the "Agent job 1" (you need to give that a better name) and search for "NuGet" choose the NuGet Tool Installer and determine which version of NuGet you need. Fot this example I used 5.1.0 but click on the information icon behind version to get a list of of versions.
DevOps - NuGet Tool Installer

Now that we have installed the tool NuGet itself we can install a so called NuGet package. The one we will be needing is called SSISBuild. Add a Task to the agent and search for NuGet again. Choose NuGet and change the command to Custom. Then enter the follow command to install SSISBuild to the Build Server: install SSISBuild -Version 2.3.0
DevOps - NuGet install SSISBuild

4) PowerShell to build SSIS project
Now the most important step: the building of the SSIS project. We will be using PowerShell for this Task. Add a PowerShell Task to run PowerShell on Windows(, macOS, or Linux). Then change the script type to InLine and copy & paste the code below. On the first line of code (excl comments) you have to specify the path of your SSIS project starting from the solution folder untill the extension .dtproj. In the code a Predefined variable called Build.SourcesDirectory will be concatenated to this path to specify the project path on the server.
DevOps - PowerShel task to build the SSIS project

# PowerShell code
# The path of the project starting from the solution folder
$ProjectToBuild = "\SSIS\RM\StandardReportGenerator\StandardReportGenerator.dtproj"

# Join the path of the build server with the path of the project
$ProjectFilePath = Join-Path -Path $env:BUILD_SOURCESDIRECTORY -ChildPath $ProjectToBuild

# Check if the file exists on the build server
if (!(Test-Path $ProjectFilePath)) {
    # Not found, throw error
    throw "Project $($ProjectFilePath) not found!"
    # Call ssisbuild.exe with parameters to build this project
    &"$($env:BUILD_SOURCESDIRECTORY)\SSISBuild.2.3.0\tools\ssisbuild.exe" $ProjectFilePath -Configuration Development -ProtectionLevel DontSaveSensitive

    # Check whether the build was successful
    if ($LASTEXITCODE -ne 0) {
        # Build failed, throw error
        throw "Build of $($ProjectFilePath) failed.";

You can also test this locally on your own computer. Then you have to download and unzip the SSISBuild nuget to your Visual Studio solution folder and add one line of code at the top to fill Predefined variable with the solution path on your local computer.
# PowerShell code
$env:BUILD_SOURCESDIRECTORY = "D:\sources\xxxxxxbi\"

Test locally with PowerShell ISE

The PowerShell is very basic at the moment (KISS), but there are some options to extend the possibilities for this PowerShell. These will be handled in a separate post.

5) Copy Files
Now that we have successfully build the project it is time to copy the result, the .ispac file, to the Artifact Staging Directory. Add a new task called Copy Files. Select *.ispac as content and use the Predefined variable build.ArtifactStagingDirectory as Target Folder.
DevOps - Copy ispac file to stage folder

6) Publish Build Artifacts
The last task is to publish the artifact, the . ispac file, so you can use it in a Release pipeline later on. Add a task called Publish Build Artifacts. By default the same Predefined variable build.ArtifactStagingDirectory is used as path to publish. Rename the artifact so that you can easily find it when building the Release pipeline.
DevOps - Publish Build Artifacts

7) Add Triggers
To trigger this build pipeline automatically when changes are committed to the Master branch we need to add a trigger. Go to the triggers menu and enable continuous integration. Then add a path filter so that this build will only trigger when the specific project changes. Then save the Build pipeline and trigger it by committing a change.
DevOps - Enable trigger to build SSIS project

In this post I showed you how to build an SSIS project in Azure DevOps with the SSISBuild NuGet. It took quite a few steps, but most steps are relative simple and easy to replicate in your own DevOps project.

One downside of this method is that it is a bit more strict when building your SSIS project. It could even happen that you can build your project in Visual Studio successfully and even deploy it to the catalog, but that it will fail building when using the SSISBuild Nuget and throwing you this error: ERROR: An item with the same key has already been added.
ERROR: An item with the same key has already been added

The possible cause of this error is duplicate Connection Manager Guids. This happens when you copy a package (with a package connection manager) in Visual Studio. I solved it when recreated the connection manager manually.
If you have duplicate package connection managers and you convert one of them to a project connection manager then it will hide the duplicate connection manager in the copied package, making it very hard to find.

Tuesday, 2 April 2019

Stopping an Integration runtime that is already stopping

My integration runtime is stopping, but it takes already more than an hour and I see errors in the ADF monitor:

Error 1: Last operation 'Stop' get the status 'Failed'. Error Code: PoolDeleteFailedDueToLockOrReference Error Message: Activity ID: a67615ac-94ff-4547-9244-728d3ae5b25e

Error 2: Error Code: VNetResourceGroupLockedDuringStop Error Message: We are unable to clean up unneeded resources when stopping your IR. Please unlock the resource group containing your VNet and/or remove any reference to the resources created for your IR, and then retry to stop your IR.

Error 3: Error Code: IntegrationRuntimeStoppingLongerThanUsual Error Message: Your integration runtime is taking longer than usual to stop, so please retry to stop it.

It suggests to retry to stop the SSISIR, but the start and stop buttons are greyed out. How do I retry the stop?

Stop and start are greyed out

The Azure portal interface does not support restart at the moment, however you can restart an SSISIR that is already stopping with PowerShell!

#PowerShell code
# Login with your Azure account (popup will open)

# Your subscription name (needed if you have more then one subscription)
$SubscriptionName = "xxxxxxx"

# Change the current and default Azure subscription
Select-AzureRmSubscription -SubscriptionName $SubscriptionName
Get-AzureRmSubscription -SubscriptionName $SubscriptionName

# Force stopping SSISIR
Stop-AzureRmDataFactoryV2IntegrationRuntime -DataFactoryName "MyDataFactoryNae" -Name "SSISxxxxxxxIR" -ResourceGroupName "MyResourceGroup" -Force

Other steps you should take if the PowerShell does not stop the Integration Runtime:
  1. Check if there are lockes on the Resource Group or individual items (like ADF, VNET or SSISDB).
  2. If you are using a VNET, check whether it works. (Create a new SSISIR and then press validate on the VNET step)
  3. If there are no locks and the VNET is valid, but the Integration Runtime is still stopping then contact the Azure help + support desk either via Twitter of via an official support request in the portal.
1) Check for locks

2) Validate VNET

3) Add support request (in worst case)

Update: there is a new "retry stop" option available. Click on the Author (pencil) button on the left and then on connection in the left bottom corner. Now open the Integration Runtimes tab and click on the new "retry stop" button. (I hope they will add this option in the monitor as well)
Retry stopping the SSIS IR

Related Posts Plugin for WordPress, Blogger...