- C# Scripts
- DWH INTERVIEW QUESTIONS
- MS Dynamics AX 2012 R2 Video Tutorial
- Project / Work Support
- SQL SERVER DBA INTERVIEW QUESTIONS
- SQL SERVER DBA Video Tutorial
- SQL Server / TSQL Tutorial
- SQL Server 2016
- SQL Server Scripts
- SSIS INTERVIEW QUESTIONS
- SSIS Video Tutorial
- SSRS INTERVIEW QUESTIONS
- SSRS Video Tutorial
- TSQL INTERVIEW QUESTIONS
- Team Foundation Server 2013 Video Tutorial
- Team Foundation Server 2015 Video Tutorial
- Windows 10
- Windows Server 2012 R2 Installation Videos
SSIS - How To Run SSIS Package By Using Excel Macros [ dtexec ]
After developing SSIS Package, The next step is to schedule this package. We can schedule by using SQL Server agent etc. But how about your company asks you to create Excel Macros with some button and whenever their users want to run the package they click on the button and execute SSIS Package. This might not be requirement most of the time but I have seen these types of requests.
To execute our package by using Excel , we have to create Macros. Follow the steps below to make this work
Open Excel. Go to Developer Tab and then Go to Insert and Insert Command Button as shown in snapshot.
Place the Command Button anywhere you like on Excel sheet. Then Right Click on Command Button and go to Properties and set the Caption for Button as shown below
Double Click on the Button and write code as shown in snapshot
Code written in snapshot:
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:\Users\ashahzad\Documents\Visual Studio 2008\projects\SSISProjects\Package2.dtsx with your package location
Command = "dtexec /f ""C:\Users\ashahzad\Documents\Visual Studio 2008\projects\Load_Database_Scrub\Load_Database_Scrub\Package2.dtsx"""
'Run the Package
Call Shell(Command, 0)
After writing code, save it and close the window.
Save the File as shown in snapshot ( Excel Macro-Enabled Workbook)
Open the Book1.xlsm where you saved and Click on Button as shown in snapshot. It will run your SSIS Package that you have provided in VBA Code.