Using a self-hosted runner with GitHub Actions

By Tomica Kaniski in Other 0 Comment

As I was going through the excellent short course called Azure Infrastructure as Code with GitHub (by fellow MVP, Barbara Forbes), a thought appeared – what do I need to do to use my custom runner machine inside a pipeline for… I don’t know… security/privacy concerns, isolation, special requirements, different OS, control, price… or just to complicate things a bit?

Of course, GitHub supports this and it’s called a self-hosted runner.

So, what do I need to do to use this self-hosted runner with my GitHub Actions?

It’s relatively simple – there is an application package, which will be installed on your runner machine, and which will listen for and eventually do all the work defined in your workflow!

But first, let’s introduce my environment.

I have a simple GitHub Action (workflow), which creates a simple storage account on my Azure environment (there is actually no need to convert Bicep to ARM before deployment, but it seemed cool 😀). It’s currently using the „ubuntu-latest“ runner, provided by GitHub… which has also all the needed components inside (like Azure CLI, Azure PowerShell, …).

And it works fine. When there is a push to my GitHub repository, GitHub Actions starts and does what is needed on my Azure environment via this workflow:

And the mighty Bicep file (😀) it’s using for the deployment is:

Of course, this runs just fine on a standard (hosted) runner:

To run this workflow (successfully) not that much is needed.

First, I’ve created a new virtual machine (I’ll use a simple Ubuntu Hyper-V VM, no autoscaling, no… nothing) called hermes (god of speed 😀), with freshly installed Ubuntu 22.04.1-LTS (minimized).

After that, I went to the Settings of my GitHub repository and got the download and install scripts for the x64 Linux runner:

As you can see, I’ll be using crontab later to automatically (re)start my self-hosted runner.

If everything went well, you should see your runner “up and running” (😀) in the GitHub portal:

Next, I’ll use the following script to install all prerequisites for my workflow (like Azure CLI, Azure PowerShell, etc. – it really depends on your workflow and things you use):

Once this is done, my self-hosted runner hermes should be ready to run the workflow.

To try this, I need to make a slight update to my workflow file – line 12 inside the job configuration should be updated from “runs-on: ubuntu-latest” to “runs-on: self-hosted“.

So, my workflow YAML file now looks like this:

And once I push the configuration to my GitHub, my workflow automatically starts and runs on hermes, my self-hosted runner:

If we prepared our runner right, all is good! 😊

Of course, our resources are deployed successfully:

So, this is how you can use your own, self-hosted runner, to execute your GitHub Actions (workflows).

Cheers!

What about this Bicep?

By Tomica Kaniski in Other 0 Comment

You’ve probably heard about Azure Resource Manager (ARM) – the deployment and management service/layer of Azure, which enables you to manage (create, configure, delete) your Azure resources. Also, you are probably aware that ARM uses so called ARM templates – basically, JSON files that actually define the infrastructure and configuration you want to deploy to Azure (think Infrastructure as Code, IaC).

So, if you have dealt with ARM/JSON in the past, you may have been finding it difficult to start with, and somewhat complex.

Bicep is here to help.

Here is a short overview of Bicep – basically, it’s a language which enables you easier deployment of Azure resources, without messing around (too much) with JSON. To be frank, it somehow reminds of Terraform, but it’s also different. It has many cool features, immediately supports all new Azure features and APIs, can be built (converted) into .json and deployed as such or it can be deployed straight away as .bicep, doesn’t require state file, it’s open and free, has great support in Visual Studio Code and much more. And it’s still in active development!

If you’re dealing with IaC and Azure, try it.

To show you the power (and simplicity) of Bicep, here is a short example of deploying Linux virtual machine in Azure (together with a resource group, virtual network, virtual network subnet, virtual NIC and network security group), done “the old way” (in JSON, which was actually converted from Bicep… it’s easier than writing JSON from the scratch) and then done via Bicep (“the right way”? 😀).

Additionally, you’ll see that I’ve tried to break stuff into modules – with more or less sucess. 😀

The ARM/JSON way (could be done nicer/shorter, with parameters inside .parameters.json… if you know what you’re doing – this is converted from Bicep and serves just for illustrative purposes):

 

The Bicep way:



Bicep seems a bit easier to read and shorter, right (while still doing basically the same thing)? 😀

If we deploy the .bicep files above (note that I’m deploying the “raw” .bicep file directly – which is cool!):


We finally get our resources:

So, where should you start if you’re new to Bicep?

I would certainly recommend starting with free and official Deploy and manage resources in Azure by using Bicep learning path on Microsoft Learn.

After that, you can probably pick up Freek Berson’s book Getting started with Bicep: Infrastructure as Code on Azure (first and only book on Bicep that I know of – really liked it because of the simple (yet effective) examples with storage accounts, it connects everything and flows naturally – building up “brick by brick” and not “jumping around”, just to show off what Bicep can do).

Another great resource are also the Bicep examples – there’s plenty to learn from them too!

Of course, you’ll also need to practice – install the Azure CLI or Azure PowerShell module, add Bicep and use Visual Studio Code for your first steps with creating, deleting, configuring and breaking stuff… powered by Bicep! 😀

Cheers!

Bad Request for url (error 400) in AKS

By Tomica Kaniski in Other 2 Comments

I’ve decided to go through the **awesome** AKS Workshop on Microsoft Learn and had some issues (with my setup), which I wanted to share, in case someone else hits them.

It was all good until I got to the part of creating the AKS cluster with Azure CLI – I was using Windows Terminal with WSL (Ubuntu 20.04) instead of using Azure Cloud Shell as suggested. I’ve gone through the steps of preparing variables needed for creating the cluster as it says, and when I tried to finally create the cluster by using “az aks create” command, I’ve got an error:

Error states that something is wrong with our request and neither –verbose or –debug options were giving me any useful details (actually, it was in front of me all the time, but I didn’t see it 😊). I’ve rechecked/reset the variables, tried once more and once more… it was all the same. As Google was conveniently down at the time (who would say, right?!), I’ve had to try and figure it out by myself. So, I’ve looked at the error once again:

Operation failed with status: ‘Bad Request’. Details: 400 Client Error: Bad Request for url: https://management.azure.com/subscriptions/<subscription_id>/resourceGroups/aks-workshop/providers/Microsoft.Network/virtualNetworks/aks-vnet/subnets/aks-subnet%0D/providers/Microsoft.Authorization/roleAssignments?$filter=atScope%28%29&api-version=2018-09-01-preview

… and then it struck me!

There’s some trash in the URL (more precisely – my AKS subnet ID was having “%0D” added to the end)!

And if we check what “%0D” exactly stands for, it says “carriage return” (which I’ve obviously didn’t want to be a part of my subnet ID) – so, even it all seemed fine when looking at the variable content, now I know it wasn’t.

Easy-peasy, we can fix the part where we’re extracting this subnet ID or we can just replace the variable’s value with the right one (without the %0D at its end, that is).

That got me going… towards the next error. This one was actually more descriptive (yes, and the first one is descriptive enough, if you read it carefully 😊) – it said that I’ve got additional content inside my Kubernetes version variable:

Operation failed with status: ‘Bad Request’. Details: Error to parse agent pool version “1.19.3\r”: Invalid character(s) found in patch number “3\r”

You can see the extra “\r“, which again, is here because of bad value assigned to the variable $VERSION.

Which can also be easily fixed.

One other funny thing I’ve observed was, when getting my Kubernetes cluster credentials, as you can see below, they were actually merged to C:\Users\tomica\.kube\config:

This was funny because I’m inside WSL… which doesn’t actually have C:\Users\tomica\.kube\config, right? (and no, credentials weren’t merged to /home/tomica/.kube/config, which kubectl there uses by default, so… they are actually at /mnt/c/Users/tomica/.kube/config – funny, will check with the MS folks) 😊

Fair enough – we can merge them manually or just select the right file and we’re good to go:

There you go – if you get stuck on similar things, maybe this can help you. 😊

Cheers!

Fixing things with… terraform destroy

By Tomica Kaniski in Other 0 Comment

I like Terraform, because it’s so clean, fast and elegant; OK, I also suck at it, but hey – I’m trying! 🙂

The long story

Usually, Terraform and its providers are very good at doing things in the order they should be done. But sometimes people do come up with silly ideas, and mine was such (of course) – I’ve decided to rename something and it broke things. Just a little.

I have a simple lab in Azure, with a couple of virtual machines behind the Azure Load Balancer, no big deal. All this is being deployed (and redeployed) via Terraform, using the official azurerm provider. It’s actually a Standard SKU Azure Load Balancer (don’t ask why), with a single backend pool and a few probes and rules. Nothing special.

I’ve deployed this lab a few days ago (thanks to good people at Microsoft, I have some free credits to spare), everything worked just fine, but today I’ve got the wild idea – I’ve decided to rename my backend pool.

With all the automation in place, this shouldn’t be a problem… one would think. 🙂

So, as I’ve updated my code and during the make it so phase (terraform apply), I’ve got some errors (truncated, with only the useful stuff):

Issue

After going through these errors, I’ve realized that my resources are indeed in the same region, but existing rules are referencing the current backend pool by name and actually blocking Terraform in renaming the backend pool.

There are a couple of options at this stage – you can destroy your deployment and run it again (as I normally would) and it should all be fine. Or you can try to fix only the dependent resources and make it work as part of the existing deployment.

With some spare time at my hands, I’ve tried to fix it using the second one and it actually worked.

Resolution

Terraform has a nice option for destroying just some parts of the deployment.

If you look at help for the terraform destroy command, you can see the target option:

And if you run it to fix your issues, you’ll get a nice red warning saying that this is only for exceptional situations (and they mean it!):

 

So… BE CAREFUL! (can’t stress this enough!)

 

Anyhow, I’ve destroyed rules which were preventing my rename operation:

And then another terraform apply –auto-approve recreated everything that was needed, and finally – my backend pool got renamed:

Another idea I’ve had was to taint the resources (terraform taint -help), which would probably be a lot nicer. Oh, well… maybe next time. 🙂

As things are constantly improving, it shouldn’t be long until this is fixed (should it even be fixed?!). Until then, hope this will help you with similar issues!

Cheers!

Backing up Office 365 to S3 storage (Exoscale SOS) with Veeam

By Tomica Kaniski in Other 0 Comment

Are you backing up your Office 365? And… why not? 🙂

I’m not going into the lengthy and exhausting discussion of why you should take care of your data, even if it’s stored in something unbreakable like “the cloud”, at least not in this post. I would like to focus on one of the features of the new Veeam Backup for Office 365 v4, which was released just the other day. This feature is “object storage support“, as you may have guessed it already from the title of this fine post!

So, this means that you can take Amazon S3, Microsoft Azure Blob Storage or even IBM Cloud Object Storage and use it for your Veeam Backup for Office 365. And even better – you can use any S3-compatible storage to do the same! How cool is that?!

To test this, I decided to use the Exoscale SOS (also S3-compatible) storage for backups of my personal Office 365 via Veeam Backup for Office 365.

I’ve created a small environment to support this test (and later production, if it works as it should) and basically done the following:

  • created a standard Windows Server 2019 VM on top of Microsoft Azure, to hold my Veeam Backup for Office 365 installation
    (good people at Microsoft provided me Azure credits, so… why not?!)
  • downloaded Veeam Backup for Office 365
    (good people at Veeam provided me NFR license for it, so I’ve used it instead of Community Edition)
  • created an Exoscale SOS bucket for my backups
    (good people at Exoscale/A1TAG/A1.digital/A1HR provided me credits, so… why not?!)
  • installed Veeam Backup for Office 365
    (it’s a “Next-Next-Finish” type of installation, hard to get it wrong)
  • configured Veeam Backup for Office 365 (not so hard, if you know what you are doing and you’ve read the official docs)
    • added a new Object Storage Repository
    • added a new Backup Repository which offloads the backup data to the previously created Object Storage Repository
    • configured a custom AAD app (with the right permissions)
    • added a new Office 365 organization with AAD app and Global Admin account credentials (docs)
    • created a backup job for this Office 365 organization
    • started backing it all up

Now, a few tips on the “configuration part”:

  • Microsoft Azure:
    • no real prerequisites and tips here – simple Windows VM, on which I’m installing the downloaded software (there is a list of system requirements if want to make sure it’s all “by the book”)
  • Exoscale:
    • creating the Exoscale SOS bucket is relatively easy, once you have your account (you can request a trial here) – you choose the bucket name and zone in which data will be stored and… voilà:

    • if you need to make adjustments to the ACL of the bucket, you can (quick ACL with private setting is just fine for this one):

    • to access your bucket from Veeam, you’ll need your API keys, which you can find in the Account – Profile – API keys section:

    • one other thing you’ll need from this section is the Storage API Endpoint, which depends on the zone you’ve created your bucket in (mine was created inside AT-VIE-1 zone, so my endpoint is https://sos-at-vie-1.exo.io):

  • Office 365:
    • note: I’m using the Modern authentication option because of MFA on my tenant and… it’s the right way to do it!
    • for this, I created a custom application in Azure Active Directory (AAD) (under App registrations – New registration) (take a note of the Application (client) ID, as you will need it when configuring Veeam):

    • I’ve added a secret (which you should also take a note of, because you’ll need it later) to this app:

    • then, I’ve added the minimal required API permissions to this app (as per the official docs) – but note that the official docs have an error (at this time), which I reported to Veeam – you’ll need the SharePoint Online API access permissions even if you don’t use the certificate based authentication(!) – so, the permissions which work for me are:

    • UPDATE: Got back the word from Veeam development – additional SharePoint permissions may not be necessary after all, maybe I needed to wait a bit longer… will retry next time without those permissions. 🙂
    • after that, I’ve enabled the “legacy authentication protocols”, which is still a requirement (you can do it in Office 365 admin center – SharePoint admin center – Access Control – Apps that don’t use modern authentication – Allow access or via PowerShell command “Set-SPOTenant -LegacyAuthProtocolsEnabled $True”):

    • lastly, I’ve created an app password for my (global admin) account (which will also be required for Veeam configuration):

  • Veeam Backup for Office 365:
    • add a new Object Storage Repository:

    • add a new Backup Repository (connected to the created Object Storage Repository; this local repository will only store metadata – backup data will be offloaded to the object storage and can be encrypted, if needed):

    • add a new Office 365 organization:

    • create a backup job:

    • start backing up your Office 365 data:

Any questions/difficulties with your setup?
Leave them in the comments section, I’ll be happy to help (if I can).

Cheers!

Deploying Kubernetes on top of Azure Stack (Development Kit)

By Tomica Kaniski in Other 0 Comment

If you had a chance to deploy Azure Stack or Azure Stack Development Kit (ASDK) in your environment, maybe you’ve asked yourself “OK, but what should I do with it now?“.

Well, one of many things you “can do with it” is offer your users to deploy Kubernetes clusters on top of it (at least, that was what I did the other day… on my ASDK deployment) – in short, official documentation has you pretty much covered. I know, Azure enables it as well… and the process here is similar, or – the same.

The main thing you have to decide at the beginning, is if you’ll use Azure AD or ADFS for identity management (the same as with Azure Stack deployment, if you remember, from my previous posts). Why – because the installation steps differ a bit.

Once you decide it (or you ask your Azure Stack administrator how it’s done in your case), you can proceed with the installation – I assume you have your Azure Stack/ASDK up and running.

Next, in the admin portal (https://adminportal.local.azurestack.external/), you’ll need to add the prerequisites from Azure Marketplace (for this, if you remember, your Azure Stack/ASDK has to be registered):

Once done, you’re ready to set up the service principal, to which you’ll then assign the required permissions on both – the Azure side and on the Azure Stack side! (don’t forget this detail… it is well documented, but easy to overlook)

In case you don’t give your service principal the required permissions on both “sides”, you’ll probably get the “error 12” and your deployment will fail:

And you can see details in the log:

So… be careful with service principal and permissions! 🙂

Next thing you’ll need to make sure of is that you create a plan and an offer, but set your quotas right! It depends on your Kubernetes cluster deployment settings, but if you’ll go with the defaults, the default quotas (disk, in particular) need to be expanded!

If not, you’ll probably get this error:

If you were careful while reading the official docs (with a few “lessons learned” in this post), and you’ve made it to here… you’re probably set to deploy your first Kubernetes cluster on top of your Azure Stack/ASDK.

In the user portal (https://portal.local.azurestack.external/), you now have the option to deploy something called Kubernetes Cluster (preview):

Here you really can’t miss much – you’ll give your deployment a brand new (or empty) resource group, user details (together with your public SSH key, of course), DNS prefix, number and size of nodes and service principal details:

After that, your deployment starts and runs for some time (it, again, depends on your hardware and settings you’ve chosen for your cluster). Hopefully, it will end with this message:

If all is good, you can SSH into one of your master nodes and see the details of your cluster:

One other thing that would be nice to have is the Kubernetes dashboard – the process of enabling it is well documented here:

And – you’re done!

You now have your own Kubernetes cluster deployment on top of your Azure Stack/ASDK! How cool is that?! 🙂

One last thing to note – currently, this is in preview (as it says on the template), but… it works. 🙂

Cheers!

Microsoft AZ-500 down, more to go

By Tomica Kaniski in Other 0 Comment

Another month, another Azure cert! 🙂

So, for the last couple of weeks, I was reading about, learning and playing around with Azure security technologies, mainly as a preparation for AZ-500 (Microsoft Azure Security Technologies) exam.

And then… today I took the exam and… PASSED!

I must say, with a few certificates under my sleeve, this exam was not the easiest I took. I was feeling prepared and still – passing it demanded concentration on the details and a bit of thinking! Nonetheless, it’s over now – one down, more to go!

Note that… by passing this exam, I’m not automatically an Azure security guru (!) – it just means that I know a thing or two about what Azure offers in terms of security and how it works. 🙂

What did I use to prepare?

There is a great book about Azure governance called Pro Azure Governance and Security, written by my MVP colleagues Peter De Tender, David Rendon and Samuel Erskine. It’s purpose is not to be an exam prep guide, but to tackle into the world of governance and security features available within Microsoft Azure (which are part of the exam, who would know).

There is also a great post, containing a bunch of helpful AZ-500 material from Stanislas Quastana, located here, and Thomas provided some useful links in his post here and even did a webinar on Azure Security Center (hosted by Altaro) the other day – you can find the recording here.

Of course, there is also the official exam page with skills measured and docs.com.

And… don’t forget to try things out yourself! There is also a free Azure subscription, you know?! 🙂

If you’ll be taking this exam – good luck, hope this resources help you!

Cheers!

Creating some virtual machines in Azure with PowerShell

By Tomica Kaniski in Other 0 Comment

The other day I was creating some Linux virtual machines (I know, I know…) and, with Azure being my preferred hosting platform, I’ve decided to create this machines by using a simple PowerShell script. Not because I’m so good at PowerShell, but because I like it… and sometimes I really don’t like clicking through the wizard to create multiple machines.

I wanted to create multiple machines with ease, each with “static” IP address from the provided subnet, accessible via the Internet (SSH, HTTP) and running the latest Ubuntu Linux, of course.

So, I was browsing through the official documentation (a.k.a. docs.com, more specifically https://docs.microsoft.com/en-us/azure/virtual-machines/linux/quick-create-powershell), and I’ve come up with this (my version of the official docs):

If this helps you with similar task – you’re welcome.

Cheers!

How to Work with Aggregate Functions in Cosmos DB SQL (Packt)

By Tomica Kaniski in Other 0 Comment

Learn how to work with aggregate functions in this article by Gastón C. Hillar, an independent consultant, a freelance author, and a speaker who has been working with computers since he was 8 years old and Daron Yöndem, a Microsoft Regional Director and a Microsoft MVP for 11 years.

— post by Gastón C. Hillar and Daron Yöndem, provided by Packt —

Working with aggregate functions

Cosmos DB SQL provides support for aggregations in the SELECT clause. For example, the following query will use the SUM aggregate function to sum all the values in the expression and calculate the total number of levels in the filtered games. The query uses the ARRAY_LENGTH built-in function to calculate the length of the levels array for each game and use it as an argument for the SUM aggregate function.

The code file for this article can be found at https://bit.ly/2FB9DDg. The sample is included in the sql_queries/videogame_1_17.sql file:

The following lines show the results of the query. Notice that the element of the array includes a key named $1:

Whenever you use an expression in the SELECT clause that is not a property name and you don’t specify the desired alias name, Cosmos DB generates a key that starts with the $ prefix and continues with a number that starts in 1. Hence, if you have three expressions that aren’t property names and don’t include their desired aliases, Cosmos DB will use $1, $2 and $3 for the properties in the output results.

If you only want to generate the value without a key in the result, you can use the VALUE keyword. The following query uses this keyword. The code file for the sample is included in sql_queries/videogame_1_18.sql file:

The following lines show the results of the query. Notice that the element of the array doesn’t include the key:

It is also possible to achieve the same goal using the COUNT aggregate function combined with the In keyword. The following query uses the COUNT aggregate function to count the number of items in the expression and calculate the total number of levels in the iterated levels for all the games. The code file for the sample is included in the sql_queries/videogame_1_19.sql file:

The following lines show the results of the query. Notice that the query specified the desired alias:

Now you want to calculate the average tower power for the levels defined in the video games. The towerPower property is not defined for all the levels and it is only available for the levels of the game whose id is equal to 1. Whenever you use the AVG aggregate function to calculate an average for an expression, only the documents that have the property will be a part of the average calculation. Hence, the levels that don’t have the towerPower property won’t generate an impact on the average.

The following query uses the AVG aggregate function combined with the IN keyword to iterate all the levels of the games that have the towerPower property and compute its average value. The code file for the sample is included in the sql_queries/videogame_1_20.sql file:

The following lines show the results of the query. Notice that the query specified the desired alias:

If you found this article interesting, you can explore Guide to NoSQL with Azure Cosmos DB to create scalable applications by taking advantage of NoSQL document databases on the cloud with .NET Core. Guide to NoSQL with Azure Cosmos DB will help build an application that works with a Cosmos DB NoSQL document database with C#, the .NET Core SDK, LINQ, and JSON.

Cheers!

How to Leverage SSIS in ADF (Packt)

By Tomica Kaniski in Other 0 Comment

Learn how to leverage SSIS on premises to interact with cloud data in ADF v2 in this tutorial by Christian Cote, Michelle Gutzait, and Giuseppe Ciaburro, the authors of Hands-On Data Warehousing with Azure Data Factory.

SQL Server Integration Services (SSIS) has been the Microsoft ETL predilection tool for more than a decade. A lot of enterprises have used SSIS to load their on-premise data warehouses since its inception in SQL Server 2005.

In the last couple of years, IT departments have had to deal with different kinds of data and specific toolsets to process them. SSIS has successfully been able to access cloud data from on-premise ETL servers since 2015 with the Azure Feature Pack (https://docs.microsoft.com/en-us/sql/integration-services/azure-feature-pack-for-integration-services-ssis?view=sql-server-2017). However, issues occur when most of the ETL is in the cloud and SSIS is in a small part of the chain. And, up until now, it was very complex to use ADF v1 as the orchestrator in the cloud, with some SSIS package calls in the pipeline.

The following sections will describe how SSIS on-premises can be successfully leveraged to interact with cloud data in ADF v2.

Sample setup

The first thing that you need to do is set up an SSIS solution. You can use Microsoft wide world importers. The samples can be found at https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0.

From the list, you can download the databases and the SSIS solution:

  • Daily.ETL.ispac: This is the SSIS solution that takes data from WideWorldImporters to the WideWorldImportersDW database
  • WideWorldImporters-Full.bacpac: This is the transactional database that is used as a source for the data warehouse
  • WideWorldImportersDW-Full.bacpac: This is the sample data warehouse database that you’ll use in this article

Sample databases

The first step is to set up sample databases. For this, you’ll need to install SQL Server Management Studio (SSMS). This program is freely available from Microsoft at https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.

Start SSMS to connect to your server and right-click on the Databases folder in Object Explorer. Select Import Data-tier Application… from the contextual menu as shown in the following screenshot:

The Import Data-tier Application wizard will open. Now click on Next.

In the Import Settings step, make sure that the Import from local disk option is selected and click on Browse…. From the Open window, navigate to the location from where you previously downloaded the .bacpac files and click on Open, as shown in the following screenshot:

Click on Next on the Import Settings screen.

You can now proceed to the Database Settings step. Make sure that the entry in the New database name textbox is WideWorldImporters and click on Next.

The Summary will be displayed as shown in the following screenshot. Click on Finish to start the BACPAC import process:

The database is created as well as all the objects in it. This process may take a few minutes. Click on Close to terminate the process:

Repeat the same process for the other BACPAC WideWorldImportersDW-Full.bacpac. Make sure that you specify WideWorldImportersDW as the database name.

SSIS components

The SSIS setup usually requires an existing installation of SQL Server, with the Integration Services feature selected. With ADF v2, this step is less mandatory since you can deploy the packages in an Azure database.

That being said, it’s always better to test your SSIS solution on premise before deploying it in Azure. The next section will describe how to set up a sample SSIS solution.

Integration Services Catalog setup

This step is necessary for brand new SQL Server installations where you may want to deploy SSIS packages. When logged in to the server with SSMS, simply right-click on the Integration Services Catalogs folder and select Create Catalog…

The Create Catalog window will open up. You’ll need to fill in the properties and click on OK to create the integration services catalog:

Next, you need to create a folder for your project. In Object Explorer, expand the Integration Services Catalogs node, right-click on SSISDB, and select Create Folder… from the contextual menu:

The Create Folder window will open up. Set the properties as shown in the following screenshot and click on OK:

You’re now ready to open the solution in Visual Studio. Here, you can deploy it to your newly created on-premises Integration Services Catalogs folder.

Sample solution in Visual Studio

For this section, you’ll need an existing installation of Visual Studio 2015 or 2017 and SQL Server Data Tools (SSDT) installed. Visual Studio is available at https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017.

Once SSDT is opened, click on FILE | New | Project…:

The New Project window will open up. Select Integration Services Import Project Wizard, type WWImportersDaily in the textbox beside Name, and click on OK:

The Integration Services Import Project Wizard window will open; click on Next to proceed to the next step:

In the Select Source window, select the Project deployment file radio button and click on Browse…. This will open up the Open file window. Navigate to the folder where .ispac, the SSIS sample file, has been downloaded and click on Open:

Once back to the wizard, click on Next to proceed to the next step.

In the Review window, click on the Import button.

Once the project has been imported, you can see it in Solution Explorer:

You’ll have to modify the two connection managers if your server is not the default instance. To do so, double-click on WWI_DW_Destination_DW.conmgr and WWI_Source_DB.conmgr, and change their server properties.

If the connections are online, you can run the package and see if everything is working fine.

Deploying the project on premises

You’re now ready to deploy and test your project on your local server:

1. From the Solution Explorer, right-click on the WWImportersDaily project and select Deploy. Integration Services Deployment Wizard will open up as shown in the following screenshot. Click on Next to proceed to the next step:

2. Connect to your SQL Server where the integration services catalog was created before and click on Browse…. The Browse for Folder or Project window will open.

3. Expand SSISDB and select the DWDailyRefresh

4. Click on OK to close the window:

5. On the Select Destination window, click on Next.

6. The next step is to validate. However, the on-premise deployments don’t use it.

7. Click on Next to proceed to the Review window. Here, you can review whether you have selected the right .ispac file and confirm that you’re going to deploy it to the right folder in SSIS:

8. Now click on Deploy to start the deployment of the .ispac file.

9. The last step, Results, shows the progress of your deployment. If everything is successful (as it should be), click on the Close button:

You’re now done with the deployment.

10. The next step is to execute the package via the catalog. You can switch to SQL Server Management Studio for this.

11. In the Object Explorer, expand Integration Services Catalogs and navigate through the DailyETLMain.dtsx package.

12. Right-click on it and select Execute… from the contextual menu to open up the execution window:

13. Go to the Connection Managers tab, check the Connection String property for both connectors, and adjust them if necessary. If everything is okay, click on OK to start the package execution:

14. Click on Yes to see the report. Once the report has opened, you might notice that the package is still running. When you refresh it, though, you’ll see that the package has been successfully executed:

You have successfully leveraged SSIS on-premises. If you found this article to be an interesting read, you can explore Hands-On Data Warehousing with Azure Data Factory to learn how to build your own ETL solutions and address the key challenges faced while building them.

Cheers!

P.S. This post was provided by good people at Packt Publishing. Thank you, Ron!