kohera-logo-regular.svg

Triggering SQL agent jobs from Excel

A client requested whether it was possible to launch an SSIS package flow from Excel. The reason behind it was that the load of data in question was only used once a year and just over a period of one or two weeks. The actual code used to do this was no longer than four lines of VBA code in Excel.

This is how I’ve implemented the functionality. Let’s start by opening Excel. The part where we can put VBA code in Excel is located in the tab Developer.If this tab is not visible to you, you’ll need to add it. Go to File > Options > Customize Ribbon, and check Developer. A good step by step guide is available here.

160920_1

Our user wanted to start the job via a button inside Excel. So once you can access the developer tab, add a button by going to the ribbon Insert and select the button icon. When the button is placed on your sheet the Assign Macro screen pops up. Normally, your Macro name by default is ‘Button1_Click. For this exercise we’re keeping the default name, so click New.
After clicking New, a VBA coding screen pops up. Inside the module you can add following code (Italic capitals are to be replaced by your own information):


Sub Button1_Click()
Dim con As Object
   Set con = CreateObject("ADODB.Connection")
   con.Open = "Provider=sqloledb; Data Source=SERVERNAME; Initial Catalog=DATABASENAME; User Id=USERNAME; Password=PASSWORD;"
   
con.Execute "exec msdb.dbo.sp_start_job ‘AGENTJOBNAME’"
Workbooks("WORKBOOKNAME.xlsx").Close
End Sub

In my opinion it’s better to use a dedicated user to log into the database and call the agent. If not, the password user and password are available for everyone… This dedicated user will be granted only the permissions needed to call the agent. That’s why the job is executed via the SQL Agent account. I’ve provided the user with following permissions on msdb.Some of the granted permissions are not necessarily required. Minimal permissions are RSExecRole and SQLAgentOperatorRole. Also, I’ve provide the user public permissions on the DB used in the Excel connection.
160920_2

Attention: calling the SQL Agent will take the bit version of your operating system. Take this into account when using Excel as source. Best case is to include an additional line in your code to save the Excel as CSV and use that file in your SSIS flow.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...