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.

Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...
Power BI Desktop is een prachtige tool om snel data-analyses te kunnen uitvoeren. Je connecteert op een databron, importeert en...
dba image
DBA is not that scary
Often when talking to people who are looking for a career path in the data world I feel like there...
How do you link an SCD Type 2 table in Power Query?
This article uses a simple example to demonstrate how to link an SCD Type 2 table in Power Query to...