Category: Dev Ops

All about code tracking and management for example, Azure DevOPS branching and merging, Release management, Continuous integration (CI) and Continuous deployment (CD).

SQL Server integration services environment setup

These are required steps;

  1. SQL Server Integration Services

Install Integration Services

  1. Create SSIS DB Catalog using SQL Server Management Studio

Create and Configure SSIS Catalog

  1. Microsoft Access Database Engine 2016 Redistributable (ACE driver for Excel Files)

Install Microsoft Access Database Engine 2016 Redistributable

  1. A domain account that has read/write/delete permission to file system.

This will be used as a proxy account. This is required because SSIS Packages will run from SQL Server Agent and the job will fail because of security context.

Azure DevOps Commit error: Object reference not set to an instance of an object

I am using Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT) to develop SSIS packages. Recently i started getting “Object reference not set to an instance of an object” error on commits to Azure DevOps. This is what i did to resolve my errors;

  1. Close Visual Studio
  2. Navigate to “C:\Users\[User Folder]\AppData\Local\Microsoft\Team Foundation\7.0\Cache” folder. Delete all contents.
  3. Restart Visual Studio and try to commit.

Excel LinkServer not working in SQL Server

I have created Excel linked server in SQL Server;

EXEC sp_addlinkedserver
   @server = 'ExcelServer',
   @srvproduct = 'ACE 12.0',
   @provider = 'Microsoft.ACE.OLEDB.12.0',
   @datasrc = 'C:\TempFolder\Sample1LinkedServer.xlsx',
   @provstr = 'Excel 12.0; HDR=Yes';

When i try to test this linked server, i get this error;

What permissions? SQL Server is running under NT service account by default. To fix this issue, i did this;

Go To Server Objects -> Linked Servers -> Providers.

Open Microsoft.ACE.OLEDB.12.0 by right click and properties. Check “Allow inprocess”.

Do the same for Microsoft.ACE.OLEDB.14.0.

The pain will go away.

Adding a separate email account as an owner subscription

My friend has created the Azure subscription using this email address, foo.inc@outlook.com. Azure has created a domain fooincoutlook.onmicrosoft.com in Azure Active Directory.

Me and my friend share same subscription with same foo.inc@outlook.com email address to provision services Azure. There are occasional disruptions in my sign-in and I see a login pop up window. It asks me to type-in our shared email address to get a code and authenticate in Azure. I contact my friend and solve login issue. This is a waste of time.

To solve this issue, navigate to Active Directory -> Manage -> User and create a new user;

adam@fooincoutlook.onmicrosoft.com

Navigate to Azure subscription -> Access control (IAM) -> Add -> Add role assignment;

By using adam@fooincoutlook.onmicrosoft.com, We can share a single subscription but can use our own email accounts to provision resources.

There are other ways to manage identities but I have found this an easier and quicker fix.

VM Reserved instance

I have configured Azure VM for development environment from an image and running it for the past 3 weeks. Today, Azure advisor recommended that upgrading Pay-As-You-Go to reserved instance will save us 25% yearly that’s estimated 1,031.67 USD.

Here is how you can do that;

Go to Home -> <Your Subscription> -> Buy reserved instances -> Virtual machine

I came here for one year (25%) discount but if commit for three year i can get 48% discounts. Not bad.

The monthly estimated prices for a 3 years commitment is 130.61 USD.