How to Execute SSIS package using Microsoft Excel macros

0
1840

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

  1. 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.
  2. Right click on desired package in solution explorer and click Properties.
  3. 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.
Identify SSIS package
Identify SSIS package

STEP 2.  Microsoft Excel Developer option.

  1. Do check Developer tab in the Excel’s Menu bar. Developer tab is mandatory to write your Macro code.
  2. If it is present then jump to the next step else take a reference from the below screenshot to enable Developer tab in excel.
  3. You have to Tick the Developer tab option under Customize ribbon options.
Enable Developer option in Microsoft Excel to write Macros
Enable Developer option in Microsoft Excel to write Macros

STEP 3.  Insert Command button.

  1. After enabling the Developer tab option, Go to Insert option.
  2. Choose the Command button as shown in the screenshot below.
Insert Command box in Microsoft Excel
Insert Command box in Microsoft Excel

STEP 4.  Setting properties of Command button.

  1. Place command button to a suitable space on the Excel sheet.
  2. Now, Right click on command button and go to Properties.
  3. Under Caption, set the desired caption for the Button say “Clicking this Button will Execute your SSIS Package“.
Right Click on Command Button to change Properties
Right Click on Command Button to configure Properties

STEP 5.  Macro code to run SSIS package using Microsoft Excel.

  1. 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.
  2. After writing the Excel macro code, close the window.

Excel Macro code to Run SSIS package
Excel Macro code to Run SSIS package

STEP 6.  Saving the Excel file.

  1. Now, save the Excel file in the format (.xlsm) as shown below (i.e. Excel Macro enabled workbook).
Save as Excel macro enabled Workbook
Save it as Excel macro enabled Workbook

STEP 7.  Execute the SSIS package using Microsoft Excel

  1. Now, this Excel can be shared with your client for the SSIS package execution.
  2. Open the saved Excel and Click on button to run the SSIS package using Microsoft Excel.
Execute SSIS package from Microsoft Excel
Execute SSIS package from Microsoft Excel

STEP 8.  Modify the Excel macro code

  1. 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.
Click on View code under Developer tab to edit Excel macro
Click on View code under Developer tab to edit Excel macro

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.

VN:F [1.9.22_1171]
Your Feedback hels to Improve
Rating: 9.9/10 (11 votes cast)
VN:F [1.9.22_1171]
Rating: +5 (from 7 votes)
How to Execute SSIS package using Microsoft Excel macros, 9.9 out of 10 based on 11 ratings