SSIS - How To Run SSIS Package By Using Excel Macros [ dtexec ]

Scenario:

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.

Solution:

To execute our package by using Excel , we have to create Macros.  Follow the steps below to make this work
Step 1:

Open Excel. Go to Developer Tab and then Go to Insert and Insert Command Button as shown in snapshot.

Step 2:
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

Step 3: 
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)

End Sub

After writing code, save it and close the window.

Step 4:
Save the File as shown in snapshot ( Excel Macro-Enabled Workbook)

Step 5:
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.

10 comments:

  1. I think SSIS is actually the best tool to provide solutions to some very simpler complex IT problems.

    SSIS Postgresql Write

    ReplyDelete
  2. Great Article! This blog is useful for everyone. Turnao is a web-based application used to Convert excel spreadsheet to online database.

    ReplyDelete
  3. HI Thanks for this. I have followed the steps but when I press the button nothing happens. Any ideas?

    ReplyDelete
  4. Hi, I think that command dtexec is supposed to work on the same machine where the SQL is located. Is it possible to run it from a different PC? If I try, it does not find dtexec command

    ReplyDelete

  5. I love your article thanks for share the article
    mnsud2l
    Tiktok 18

    ReplyDelete
  6. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me

    inside bc
    Allen Student Login
    mnsud2l

    ReplyDelete
  7. DHSE Kerala Department of Higher Secondary Education DHSE Kerala, was established on 1990. The Board is to provide affiliation to new schools for secondary education and to promote better quality of higher secondary education DHSE Kerala +2 Book 2023 in all over the state. Kerala Board is the regulatory body of Higher Secondary Schools situated in Kerala. The Department of Higher Secondary Education is also know as DHSE. The students are advised to visit DHSE’s

    ReplyDelete
  8. The transformative potential of Visual Basic for Applications (VBA) is undeniable. It has revolutionized how businesses approach data and automation. This platform, rich in content and expert guidance, offers a structured pathway for those keen to tap into this potential. Explore a curated collection of resources that span the gamut from beginner guides to advanced excel vba developer techniques. The emphasis is not just on acquiring knowledge but on practical application, ensuring that users can seamlessly integrate VBA solutions into their day-to-day operations.

    ReplyDelete
  9. Hey !! I am A creative SEO Expert, who helps clients generate leads, drive site traffic, & build their brands through helpful, well-designed marketing strategies.
    Best SEO Blogs

    ReplyDelete