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.


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’"
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.

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.

Your Modernization Journey starts now
Recently Microsoft introduced SQL server 2022. We wrote this post to make sure you won’t be missing out on all the fun and exciting new...
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....
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 are creative and have knowledge about the...
Reading monitoring alerts in Telegram
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...
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 similar functionality in Azure SQL...
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...