Menu
Menu
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.
