kohera-logo-regular.svg

Triggering SQL agent jobs from Excel

Vector_BG.png

Triggering SQL agent jobs from Excel

Vector_BG.png

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

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.

Photo of successful woman coder hacker web creator sitting armchair comfortable workspace workstation indoors.
The hurdles and pitfalls of moving or migrating a System-versioned temporal table cross database
Maybe you already have your own way of doing this and are wondering about alternative methods, or maybe you are...
Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
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...
kohera-2312-blog-sql-server-level-security-featured-image
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...