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
