Executing a SSIS Package from Stored Procedure (xp_cmdshell) in SQL Server

There are multiple way to execute SSIS packages. In this Article we will see how to run the package using stored procedure.

We can call the SSIS package through below way

Ø  Call SQL Agent jobs using SQL server (EXEC dbo.sp_start_job)

Ø  Call package which is deploy in SSISDB catalog (exec ssisdb.catalog.create_execution)

Ø  Call SSIS package directly (EXEC master..xp_cmdshell)

In this article, we will see how to call SSIS package using xp_cmdshell.

Let’s see this demo

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 calling this package using System SP

DECLARE @SQLQuery AS VARCHAR(2000) DECLARE @ServerName VARCHAR(200) = 'DESKTOP-DQLCKGK' SET @SQLQuery = 'DTExec /FILE ^"H:\Package.dtsx^" ' EXEC master..xp_cmdshell @SQLQuery GO

       

Before call this SP we need to configure the “sp_configure 'xp_cmdshell', 1”

Using below Script

USE master GO -- To allow advanced options to be changed. EXEC sp_configure 'show advanced options', 1 GO -- To update the currently configured value for advanced options. -- WITH OVERRIDE disables the configuration value checking if the value is valid RECONFIGURE WITH OVERRIDE GO -- To enable the xp_cmdshell component. EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE WITH OVERRIDE GO -- Revert back the advance option EXEC sp_configure 'show advanced options', 0 GO RECONFIGURE WITH OVERRIDE GO

         

Now we can run this Sp.

Before running this SP see the records in the table

    

   

Now Running the Script

  

Now see the records in the table