Executing SSIS package using Excel Macros

There are multiple ways to run the SSIS package. In this demo, we will see how to run the SSIS package using Excel Macros.

You may have a question in your mind that we have n number of way to execute the SSIS package like SQL server agent, SP, bat file, command prompt, C#  or some other scheduler, then why we are using excel macro to execute a package.

Here I have a scenario:

I have developed a package. This package I used to read the data from an excel file and send the mail to the respective user. This package is used to run by the end user who does not have knowledge of SQL server or SSIS any other tool. Also, he is not export in comment prompt. In his machine, there is no SQL server agent.

In this case, we have limited scope to run the package. There are below option.

Ø  Execute the package with help of bat file

https://bageshkumarbagi-msbi.blogspot.com/2019/02/executing-ssis-package-from-batch-file.html

Ø  Execute the package using Excel Macro 

In this demo, we will see how to call the SSIS package using excel macro.

I am creating a simple package. This package I so simple, in this package I am inserting a record into a table.     

 

Inserting a table into this package

Now I am running this package

  

Package executed successfully.

See the record into the table

  

Now I am opening the excel file

 

     

And go the developer tab

OMG, I am not able to see the Developer tab.

Go to the File and select option

   

Click on Option

 

  

Select Customize Ribbon and select Developer and click OK.

Select the Developer tab

  

Click the insert and select Button and drag in the cell.

   

Now click on New button, A macro will be open.

 

Here we need to write the VB script.

Sub Button1_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     Command = "dtexec /f ""H:\Package.dtsx""" 'Run the Package     Call Shell(Command, 0)     MsgBox "Package is completed successfully" End Sub

  

 
 

Now we need to save with Excel Macro-Enabled Workbook

    

Now macro is saved.

  

Open the file

Click the button.

Before executing the package see the records in the table.

  

Now running this package.

 

See the record in the table.

  

Hope this will be helpful!