Friday, 6 December 2019

Azure DevOps - New Microsoft SSIS Deploy task

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

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.

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.

Azure DevOps - New Microsoft SSIS Build task

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

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

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

Related Posts Plugin for WordPress, Blogger...