SQL Server Integration Services (SSIS) provides some very nice tool to run the SSIS packages outside the SSMS (SQL Server Management Services). The two important tools are Execute Package Utility (dtExecUI.exe) tool, a GUI based tool and dtExec.exe, a command line based tool. You can run the SSIS packages programmatically as well.
Ms-Excel does not have an in-build mechanism to run SSIS package. If you requirement is to run the SSIS package from MS-Excel, you have to customize a solution for this
For demonstration purpose let us consider we have following SSIS package. Our objective is to run this package from Ms-Excel.
- SSIS: How to load multiple excel files into multiple SQL tables?
- SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to generate HyperLink in Excel output?
We have designed a form in Ms-Excel with a button named “Run SSIS package”. To run SSIS package outside SSMS, you have to wrap your code logic in a way that it uses either of the two tools (dtExec.Exe or dtExecUI.exe). So our code logic should be calling either of these two tools.
If you SSIS package are located in a file and print server or network drive or to your local hard disk, we can write following code logic to run our SSIS package using dtExec.exe utility.
Private Sub CommandButton1_Click() Dim strPackageName As String 'SSIS package location strPackageName = “C:\temp\SimplePackage.dtsx” Shell "cmd.exe /c dtexec /CONSOLELOG NM /f " & strPackageName, vbNormalFocus MsgBox "SSIS Package completed successfully", vbOKOnly End Sub
So this is how we can run our SSIS package from Excel.
