Showing posts with label SSIS. Show all posts
Showing posts with label SSIS. Show all posts
Case
I want to give my Integration Runtime access to my sources via a firewall rule and block other machines or services. How do I arrange that?
Firewall exceptions for SSIS IR






















Solution
Good news! Microsoft published a list of IP addresses per Azure region for the Integration Runtimes in Azure Data Factory. This means you can narrow down the list of machines accessing your sources. Now only Integration Runtimes from a specific Region (like West Europe) can access it. Perhaps not enough for everybody, but it is better then giving ALL Azure services access to for example your Azure SQL Database.






Note 1: The IP addresses are listed as CIDR. For some firewalls you have to convert those to a IP range. You can use a CIDR to IPv4 calculater to convert them. For example:
40.74.26.0/23 => 40.74.26.0 to 40.74.27.255 (512 hosts in total)

Note 2: Not sure how often this list of IP addresses changes. So you might want to put the URL of the list in your documentation for error handling.

Note 3: Azure Data Factory Data Flows does not use the same IP addresses. A list of all Azure IP addresses can be downloaded as a JSON file. This JSON file gets updated on weekly basis.




Credits Geerten de Kruijf


Case
Until now we needed Third Party tasks or PowerShell to deploy an SSIS project in DevOps. Now Microsoft finally released its own SSIS DevOps tasks. How does it work?
Replace PowerShell code with Microsoft SSIS Deploy task














Solution
Microsoft just released the SSIS Deploy task (public preview) which makes it much easier to deploy an SSIS project. Below you will find the codeless steps to deploy artifacts created by the SSIS Build task.

1) New Release Pipeline - Empty Job
The first step is to create a new Release Pipeline. For this example we will use an empty job to start with and later on add tasks manually.
  • Go to the Pipelines menu on the left
  • Go to Releases
  • Click on + New to create a New Release pipeline
  • Next choose 'Empty job'

Next step is to give the first Stage its name. A Stage is an environment like acceptance or production where you want to deploy your SSIS packages. For this example we will have three stages / environments: Dev/Tst, Acc and Prd. The next two stages will be created by using cloning, but first we will add and configure the new deploy task so that you don't have to repeat yourself.
  • After choosing 'Empty job' a first stage is created. Rename it 'DEV/TST'
  • Close the Stage pane by clicking the cross in the upper right corner
Create Relesae Pipeline

















2) Select Artifact
In the Artifact pane you need to select the Artifact (the ispac file) that you created in the Build Pipeline. So look up the name before you start.
  • In the Artifacts pane click on + Add to add an artifact
  • Choose the project if you have multiple DevOps projects
  • Select the Artifact of the CI pipeline
  • Select Latest version
  • Optionally rename the source alias
  • Click on the Add button

We also need to determine whether we want to use the Continuous deployment trigger or perhaps use an other trigger or scheduling. For this example we will use the CD trigger.
  • Click on the lightning icon
  • Enable Build trigger (to release after each new successful build)
  • Close the CD trigger pane by clicking the cross in the upper right corner
Add Artifact and enable Continuous deployment

















3) Add variables
For this example we need three variables: SsisServer, SsisDBUsername and SsisDBPassword
  • Go to the Variables tab
  • Click on the + Add to add the first variable
  • Enter the name of the variable
  • Enter the value of the variable
  • Hit the padlock icon when it's a sensitive value like a password
  • Select the first stage 'Dev/Tst' as scope

Repeat this for all three variables and later on, after the next step, repeat this for each Stage. For each stage it will add the same three variable, but with a different scope (Acc or Prd), but you do have to change the values of the variables.
Add variables to DevOps

















4) Add task to first stage
Now we need to add a task to do the actual deployment of the SSIS ispac file. This is where the new Microsoft SSIS Deploy DevOps task is used. Note: Click on the Info-icon behind each field title to get more information about the desired value for that field.
  • Within the first Stage pane, click on the '1 job, 0 task' link to go to the job
  • Optionally change the settings of the Agent job (for example its name)
  • Click on the + icon in the Agent Job to add a new task
  • Search for 'SSIS Deploy'
  • Add the task called SSIS Deploy (This is the official Deploy Task for SSIS DevOps from Microsoft). Note: There are various third party tasks with the same name.
  • Click on the new task and rename it to Deploy SSIS [projectname]
  • Use the ellipsis button to browse to the ISPAC file
  • Use SSISDB as Destination Type
  • Use $(DBServer) as Destination Server (this retrieves the value of the variable)
  • Use "/SSISDB/[projectname]" as Destination Path (replace it with the foldername you need)
  • Use SQL Server Authentication as Authentication type
  • Use $(DBUsername) as Username (this retrieves the value of the variable)
  • Use $(DBPassword) as Password (this retrieves the value of the variable)
  • Leave Overwrite on (you want to do this more often)
Add SSIS Deploy task to Release pipeline

















5) Add stages
Now we need to clone the Dev/Tst stage to Acc and to Prd.
  • Click on the Pipeline tab if you don't see your first Stage
  • Hover above the first stage and wait for the clone button to appear and click it
  • Next rename it to Acc
  • Repeat this for Prd

Notice the red exclamation mark before the variables tab after adding a stage: you need to add the same variables for each stage
Adding stages for Acc and Prd

















For each Stage you can determine how that Stage starts. These are the 'Pre-deployment conditions'. For example for Dev/Tst you could do an automatic deployment, for Acc you first need an approval from one person and for Prd you need two approvals.

  • Click on the lightning/person icon on the left side of each Stage to set it
  • Dev/Tst will use the 'After release' trigger and the next stages will use the 'After stage trigger'
  • For Acc and Prd you could add Pre-deployment approvals by selecting one of your teammembers.
  • Close the Pre-deployment conditions pane by clicking the cross in the upper right corner
  • Repeat this for all stages

And don't forget to add the variables when you have finished the Pre-deployment settings.
Adding Pre-deployment conditions for each stage

















6) Test
Now create a new release by activating the build task. After this a new artifact will be created and this will trigger the release pipeline. You can also hit the Create Release button in the upper right corner to use the last created artifact. The artifact will automatically be released to the first stage, but the next step waits for approval (click on the blue ACC button to approve).
Dev/Tst succesful, Acc waiting for approval











Note: You can also check the result of the release by clicking on stage DEV/TST and then click on the SSIS Deployment step. If it failed you can see the error messages per step.


Conclusion
In this post you saw how easy it is to deploy an SSIS project to the Integration Services Catalog with this new task. It is easy to switch between various Authentication types. One small disadvantage is that you don't have any options for the SSIS Catalog Environments. You either have to do that manually or you have to write some PowerShell or TSQL code to do that after the deployment.


Solution
Until now we needed Third Party tasks or PowerShell (with NuGet) to build an SSIS project in DevOps. Now Microsoft finally released its own SSIS DevOps tasks. How does it work?
Four tasks that will be replaced by one new task























Case
Microsoft just released SSIS Build (public preview) which makes it much easier to build an SSIS project. Below you will find the codeless steps to build and publish the artifacts so that they can be deployed.

1) Create new build pipeline
In this step we will create an empty build pipeline which is connected to Azure Repos Git where our SSIS project is located.
  • Go to Pipelines menu in DevOps and then to Builds. In the list of pipelines you will find the + New sign on top. 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.". After clicking on it you must select the source you want to use for building the code (Azure Repos Git in our example).
  • Next step is to create an Empty job/pipeline and give it a useful name.
Create new empy build pipeline

















2) Add SSIS Build task
In this step we will add the new SSIS Build task from Microsoft. Make sure to pick the right one because there are several tasks with the same name. You want to look for "by Microsoft Corporation" or "by BeisiZhou".
  • Click on the plus icon behind the "Agent job 1" (you probably want to give that one a better name) and search for "SSIS Build" choose the SSIS Build from Microsoft (see animated gif)
  • Edit the new SSIS Build task. The only thing you need to change is the Project Path property. You can use the ellipsis button to browse to your SSIS project file.
Add SSIS Build task by Microsoft

















3) Publish Artifact
The previous task will create an IsPac file that we need to publish so that we can use it in a release pipeline later on.
  • Click on the plus icon behind the "Agent job 1" and search for "Publish artifact" choose the Publish artifact from Microsoft (see animated gif)
  • The only thing you need to change is the Artifact name. Don't use the default 'drop', but use you SSIS project name instead.
Publish artifact (IsPac)

















4) Add Trigger
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 or by hitting the trigger options for a manual trigger.
Add trigger

















Conclusion
This new task is much easier to use than the PowerShell code and also easier than most of the third party tasks. With a little practice you can now easily create a build task under two minutes which is probably faster than the build itself.

If your build fails with the following error message then you are probably using a custom task or component (like Blob Storage Download Task). These tasks are not installed on the build agents hosted by Microsoft. You can either install them via PowerShell or use a self hosted agent where you can install all custom components.

System.ArgumentException: Value does not fall within the expected range.
   at Microsoft.SqlServer.Dts.Runtime.Interop.ProjectInterop.ReferencePackage(Package package, String packageLocation)
   at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events)
   at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package()
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow)
   at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow)
ERR:The process 'C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.exe' failed with exit code 1
Build D:\a\1\s\SSIS\STG\STG_MySource\STG_MySource.dtproj failed
ERR:Build failed
##[error]Build failed

Case
Recently you showed how to build an SSIS project in DevOps, but how do you deploy an SSIS project within DevOps.
Release SSIS in DevOps












Solution
There are no out of the box tasks yet, but there are a couple of Third Party DevOps tasks for SSIS which you could try-out. For this example we will use little PowerShell code which we have done before.
Update Dec 6: New Microsoft SSIS Deploy task

1) New Release Pipeline - Empty Job
The first step is to create a new Release Pipeline. For this example we will use an empty job to start with and later on add tasks manually.
  • Go to the Pipelines menu on the left
  • Go to Releases
  • Click on + New to create a New Release pipeline
  • Next choose 'Empty job'
Next step is to give the first Stage its name. A Stage is an environment like acceptance or production where you want to deploy to. For this example we will have three stages / environments: Dev/Tst, Acc and Prd. The next two stages will be created by using cloning, but first we will add tasks so that you don't have to repeat yourself.
  • After choosing 'Empty job' a first stage is created. Rename it 'DEV/TST'
  • Close the Stage pane by clicking the cross in the upper right corner
Create Relesae Pipeline

















2) Select Artifact
In the Artifact pane you need to select the Artifact (the ispac file) you created in the Build Pipeline. So look the name before you start.
  • In the Artifacts pane click on + Add to add an artifact
  • Choose the project if you have multiple DevOps projects
  • Select the Artifact of the CI pipeline
  • Select Latest version
  • Optionally rename the source alias
  • Click on the Add button
We also need to determine whether we want to use the Continuous deployment trigger or perhaps use an other trigger or scheduling. For this example we will use the CD trigger.
  • Click on the lightning icon
  • Enable Build trigger (to release after each new successful build)
  • Close the CD trigger pane by clicking the cross in the upper right corner
Add Artifact and enable Continuous deployment

















3) Add task to first stage
Now we need to add a task to do the actual deployment of the SSIS ispac file. There are a couple of third party SSIS deployment tasks, but the instructions and examples of those are not always that clear. Hopefully Microsoft will create there own SSIS devops tasks soon. For now we will use a little Powershell scripting.
  • Within the first Stage pane, click on the '1 job, 0 task' link to go to the job
  • Optionally change the settings of the Agent job (for example its name)
  • Click on the + icon in the Agent Job to add a new task
  • Search for 'Powershell'
  • Add the task called PowerShell (Run a PowerShell script on Linux, macOS, or Windows)
  • Click on the new task and rename it to Deploy SSIS project
  • Select Inline as Type
  • Paste the script below in the Script textbox
Add PowerShell code to deploy SSIS

















Notice the Params piece in the PowerShell code. You could hardcode each value, but for this example we will use Variables from the Release pipeline to set the value of these parameters in the next step. The string "$(SsisServer)" in the code will be replaced by the value of the DevOps variable and result in: "bitools.database.windows.net" (see next step).

# PowerShell code
# Params
$SsisServer ="$(SsisServer)"
$SSISDBUsername = "$(SsisDBUsername)"
$SSISDBPassword = "$(SsisDBPassword)"
$FolderName = "RM"

# Mask the password to show something on
# screen, but not the actual password
# This is for testing purposes only.
$SSISDBPasswordMask = $SSISDBPassword -replace '.', '*'


Write-Host "========================================================================================================================================================"
Write-Host "== Used parameters =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server : " $SsisServer
Write-Host "SQL Username : " $SSISDBUsername
Write-Host "SQL Password : " $SSISDBPasswordMask
Write-Host "========================================================================================================================================================"

############## ASSEMBLY ###############
# Add SSIS assembly so you can do SSIS stuff in PowerShell
Write-Host "Referencing SSIS assembly"
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;


############## SQL SERVER ###############
Write-Host "Connecting to Azure SQL DB server $($SsisServer)"

# Create a connectionstring for the Azure DB Server
# Make sure you use SSISDB as the Initial Catalog!
$SqlConnectionstring = "Data Source=$($SsisServer);User ID=$($SSISDBUsername);Password=$($SSISDBPassword);Initial Catalog=SSISDB;"

# Create a connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring

# Check if the connection works
Try
{
$SqlConnection.Open();
Write-Host "Connected to Azure SQL DB server $($SsisServer)"
}
Catch [System.Data.SqlClient.SqlException]
{
Throw [System.Exception] "Failed to connect to Azure SQL DB server $($SsisServer), exception: $($_)"
}


############## SSISDB ###############
# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection

# Check if SSISDB connection succeeded
if (-not $IntegrationServices)
{
Throw [System.Exception] "Failed to connect to SSISDB on $($SsisServer)"
}
else
{
Write-Host "Connected to SSISDB on $($SsisServer)"
}

# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]

# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
# Catalog doesn't exists. The user should create it manually.
# It is possible to create it, but that shouldn't be part of
# deployment of packages.
# Also make sure the catalog is SSISDB and not master or any
# other database.
Throw [System.Exception] "SSISDB catalog doesn't exist. Create it manually!"
}
else
{
Write-Host "Catalog SSISDB found"
}

############## CATALOG FOLDER ###############
# Create object to the (new) folder
$Folder = $Catalog.Folders[$FolderName]

# Check if folder already exists
if (-not $Folder)
{
# Folder doesn't exists, so create the new folder.
Write-Host "Creating new folder" $FolderName
$Folder = New-Object $SsisNamespace".CatalogFolder" ($Catalog, $FolderName, $FolderName)
$Folder.Create()
}
else
{
Write-Host "Folder" $FolderName "found"
}

############## LOOP ISPACS ###############
Get-ChildItem -Filter "StandardReportGenerator.ispac" -Recurse | Where-Object { -Not ($_.FullName -match "obj") } | ForEach-Object {

############## PROJECT ###############
$IspacFilePath = $_.FullName

# Check if ispac file exists
if (-Not (Test-Path $IspacFilePath))
{
     Throw  [System.IO.FileNotFoundException] "Ispac file $IspacFilePath doesn't exists!"
}
else
{
     $IspacFileName = split-path $IspacFilePath -leaf
     Write-Host "Ispac file" $IspacFileName "found"
}

# Get project name from ispac file
$ProjectName = [system.io.path]::GetFileNameWithoutExtension($IspacFilePath)

# Reading ispac file as binary
[byte[]] $IspacFile = [System.IO.File]::ReadAllBytes($IspacFilePath)
$Folder.DeployProject($ProjectName, $IspacFile) | Out-Null
$Project = $Folder.Projects[$ProjectName]
if (-not $Project)
{
# Something went wrong with the deployment
# Don't continue with the rest of the script
return ""
}

Return "Ready deploying $IspacFileName "
}

Note: this script uses a DB user to connect to the SSIS DB. For other authorisations you need to change the code block starting on line 30.

4) Add variables
For this example we need three variables: SsisServer, SsisDBUsername and SsisDBPassword
  • Go to the Variables tab
  • Click on the + Add to add the first variable
  • Enter the name of the variable
  • Enter the value of the variable
  • Hit the padlock icon when it's a sensitive value like a password
  • Select the first stage 'Dev/Tst' as scope
Repeat this for all three variables and later on, after the next step, repeat this for each Stage. Just add the same variables but change the scope to Acc or Prd.
Add variables to DevOps

















5) Add stages
Now we need to clone the Dev/Tst stage to Acc and to Prd.
  • Click on the Pipeline tab if you don't see your first Stage
  • Hover above the first stage and wait for the clone button to appear and click it
  • Next rename it to Acc
  • Repeat this for Prd
Notice the red exclamation mark before the variables tab after adding a stage: you need to add the same variables for each stage.
Adding stages for Acc and Prd

















For each Stage you can determine how that Stage starts. These are the 'Pre-deployment conditions'. For example for Dev/Tst you could do an automatic deployment, for Acc you first need an approval from one person and for Prd you need two approvals.
  • Click on the lightning/person icon on the left side of each Stage to set it
  • Dev/Tst will use the 'After release' trigger and the next stages will use the 'After stage trigger'
  • For Acc and Prd you could add Pre-deployment approvals by selecting one of your teammembers.
  • Close the Pre-deployment conditions pane by clicking the cross in the upper right corner
  • Repeat this for all stages
And don't forget to add the variables when you have finished the Pre-deployment settings.
Adding Pre-deployment conditions for each stage

















6) Test
Now create a new release by activating the build task. After this a new artifact will be created and this will trigger the release pipeline. You can also hit the Create Release button in the upper right corner to use the last created artifact. The artifact will automatically be released to the first stage, but the next step waits for approval (click on the blue ACC button to approve).
Dev/Tst succesful, Acc waiting for approval











You can also check the result of the release by clicking on Dev/Tst and then click on the PowerShell step. If it failed you can see the error messages per step.
See result of deployment













Conclusions
In this step you saw how to create a Release pipeline with several Stages and learned how these deployment stages are activated with the Pre-deployment conditions. Unfortunately there are no out-of-the-box Microsoft SSIS devops tasks which force you to either use Third Party tasks or PowerShell. Also take a look around and see all the other options in the build pipeline.




Case
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




















Solution
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.
Update Dec 6: New Microsoft SSIS Build task

1) New Build Pipeline
Go to Pipelines and then to Builds. In the list of 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
















3) NuGet SSISBUILD
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!"
}
else
{
# 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
















Conclusion
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.

Next step: Deploy your SSIS project








Case
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
















Solution
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)
Login-AzureRmAccount

# Your subscription name (needed if you have more than 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

















Case
Yes new SSIS functionality! In a previous post I had almost written it off, but Microsoft just introduced the SSIS source that we where all waiting for: The PowerQuery source. How does it work?
SSIS - Power Query Source















Solution
First you need to download and install the latest SSDT (Version 15.9.0) and since it is still in preview, you can only use it within SSDT or on a Azure Integration Runtime in Azure Data Factory.

If you drag the new Power Query Source to your Data Flow canvas and edit it, you can paste your Power Query script from Power BI (or Excel) in the query textbox. So there is not yet(?) an editor, but this should simplify a lot of tasks that could were previously could only be solved with .Net scripting.
Paste your Power Query script in the editor




After pasting the Power Query script go to the Connection Managers pane and create a Power Query Connection Manager by first clicking on Detect Data Source and then by adding the new Connection Manager via the drop down list.
Power Query Connection Manager

























Preview notes:
  • Only working in SSDT or on a Azure-SSIS IR in ADF
  • Web source does not work on an Azure-SSIS IR with custom setup
  • Oracle source only works via Oracle ODBC driver on an Azure-SSIS IR


For more details read Power Query Source (Preview)

Conclusion
This new source was announced (and canceled) a long time ago, but it is finally available. This preview version is still very basic and still has some limitations. I'm not sure whether they will integrate an editor like in Excel and Power BI, but  lets hope they will.


Case
In April 2018 Microsoft added the SSIS pipeline activity to ADF. A couple of days ago the released a new version. What are the changes?
ADF - Execute SSIS Package activity




















Solution
The big difference is that you can now select your packages and environment instead of typing the full paths manually, but there is also an new "Manual entries" option which allows you to set parameters (and connection managers) directly within the pipeline.
Selecting package and environment














See below the old version of the SSIS activity where you had to type the full paths manually.
The old manual entry

















Conclusion
Again much easier then before, but in case of package errors still not very helpful. It still forces you to search for errors in the SSIS catalog. If you are using the ADF monitor to the check for errors I would probably still prefer the Stores Procedure activity.
Case
My Script Tasks are running fine in Visual Studio 2017, but when deployed to the catalog I get an error in all Script Tasks: Cannot load script for execution
Error: Cannot load script for execution










Solution
There is a bug in SSDT for Visual Studio 2017 (15.8.1). The (temporary) workaround is to NOT use SSDT 2017 for deployment. Instead you could use SSMS to deploy your SSIS projects.
Deploy packages with SSMS



















Or use ISDeploymentWizard.exe in the folder C:\Program Files (x86)\Microsoft Visual Studio\2017\SQL\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\  to deploy your projects.

Expect an update soon!


UPDATE: SSDT 15.8.2 is available
Fixed an issue that deploying SSIS project which contains packages containing Script Task/Flat file destination to Azure-SSIS will result in the packages failing to execute in Azure-SSIS






Case
I'm running SSIS in azure for couple of months now, but I'm getting an error: The database 'SSISDB' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions. The SQL Server Agent jobs that are scheduled to clean up the log and project versions are not available in Azure.

The first time it occurred I scaled up the database tier and that solved the problem, but now it reoccurs and I don't want to keep scaling up the database tier. How do I solve this?


Solution
Since there is no SQL Server agent there are no jobs available, but there will be a solution within a couple of weeks. If you can't wait there is a workaround. First make sure the retention period and number of project versions are set to an acceptable level. If you are running and updating packages frequently then the default settings are probably a bit to high.
The default settings (right click SSISDB to see properties)





















Project versions
I set the Maximom Number of Versions per Project to 3. The clean up the old project version locate the Stored Procedure (SSISDB) [internal].[cleanup_server_project_version] and execute it. There are no parameters.
Before and after running the Stored Procedure



















Log retention
The same can be done for the Log retention with the stored procedure [internal].[cleanup_server_retention_window]. Again no parameters. The Stored Procedure is working with a T-SQL cursor so if your log is massive and your server very busy it could take a while.


You could even schedule these Stored Procedures with for example Azure Data Factory to clean up regularly, but keep up the SSIS announcements to see when they implement a out-of-the box solution.





Recently I presented an SSIS in the Cloud session at a User Group evening in Amsterdam, The Netherlands. Thank you QNH for hosting that evening. You can download the PowerPoint and in the comments you will find the blogposts that I used in my demo's.