Once you develop an SSIS package, what is the next step that you will perform? The next step definitely will be to schedule or execute that package. We all are smart and know very well that we can execute the package directly from SSDT (SQL Server Data Tools). But, today I will show you How to Execute an SSIS package using Microsoft Excel. It is quite useful in some project scenarios and also fun to learn (if not required in your project) a new way for SSIS package execution.
Scenario – Suppose, client is interested to run an SSIS package as per his convenience. So, what possible ways you can suggest him for executing an SSIS package.
Solution – I must tell you one thing, client is least bothered about is the- developer tools. He is not at all interested to open a developer interface to run a package. So our option of executing an SSIS package from SSDT interface will be ruled out. Now, another option is to schedule SSIS package by using SQL Server agent. But, Client will still not be happy as he needs to figure it out from where he can run the package to get the output. So, this option is also not a good option to suggest to him.
So, the best solution to offer to client will be executing the SSIS package using Microsoft Excel as they all are aware about Excel interface. Now, we need to provide an interface as easy as possible for the execution of an SSIS package. Now, this can be achieved by writing an Excel Macros. We will write one macro code behind the button to execute the package using Microsoft Excel. In other words, on simply clicking the button, User will be able to execute the SSIS package directly. Let us see it practically to understand it better.
Execute SSIS package using Microsoft Excel
STEP 1. Identify SSIS package
- Suppose, in our case below is the SSIS package which Client wish to run by its own. It can be any SSIS package as per your requirement.
- Right click on desired package in solution explorer and click Properties.
- Go to Full path and copy your SSIS package’s location (“C:\Ck\SSIS\SSIS Learning\SSIS Learning\Package.dtsx”). This location will be used when we will write Excel macro.
STEP 2. Microsoft Excel Developer option.
- Do check Developer tab in the Excel’s Menu bar. Developer tab is mandatory to write your Macro code.
- If it is present then jump to the next step else take a reference from the below screenshot to enable Developer tab in excel.
- You have to Tick the Developer tab option under Customize ribbon options.
STEP 3. Insert Command button.
- After enabling the Developer tab option, Go to Insert option.
- Choose the Command button as shown in the screenshot below.
STEP 4. Setting properties of Command button.
- Place command button to a suitable space on the Excel sheet.
- Now, Right click on command button and go to Properties.
- Under Caption, set the desired caption for the Button say “Clicking this Button will Execute your SSIS Package“.
STEP 5. Macro code to run SSIS package using Microsoft Excel.
- Double click on the Command button and write the code as shown in the screenshot below. You can copy the code from below and paste it in your Excel macro directly.
- After writing the Excel macro code, close the window.
Private Sub CommandButton1_Click()
'Below Three Lines are used to run SSIS Package
'Declare Command as String
Dim Command As String
'Set the value of Command, that includes dtexec and SSIS Package Location
'Replace C:\Ck\SSIS\SSIS Learning\SSIS Learning\Package.dtsx with your package location
Command = "dtexec /f ""C:\Ck\SSIS\SSIS Learning\SSIS Learning\Package.dtsx"""
'Run the Package
Call Shell(Command, 0)
STEP 6. Saving the Excel file.
- Now, save the Excel file in the format (.xlsm) as shown below (i.e. Excel Macro enabled workbook).
STEP 7. Execute the SSIS package using Microsoft Excel
- Now, this Excel can be shared with your client for the SSIS package execution.
- Open the saved Excel and Click on button to run the SSIS package using Microsoft Excel.
STEP 8. Modify the Excel macro code
- You can also modify the code for further enhancements by clicking on View code as shown in the picture below. It all depends upon your Imagination, Coding power and Business requirement.
With this we come to an end of this post as we successfully implemented the scenario of Executing SSIS package using Microsoft Excel. We hope you must have enjoyed this article. We always try to make things simple, easy and fun to Learn. If our article on “How to Execute SSIS package using Microsoft Excel macro” helped you or any query regarding this then Do share your valuable suggestions and feedback as comment below.