SQL SERVER – EXECUTE SSIS PACKAGES FROM SQL SERVER MANAGEMENT STUDIO

By using xp_cmdshell we can run the Packages from the SQL server. Before to run the packages from the SQL Server we need to enable xp_cmdshell  by running below script

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Execute SSIS package from MSDB database : 

declare @excutePackage varchar(8000)

declare @cmdline varchar(8000)

set @cmdline='/DTS "\MSDB\PackageLogging\Package" /SERVER "." /CHECKPOINTING OFF  /REPORTING V '

set @excutePackage='dtexec'

set @excutePackage=@excutePackage+@cmdline

declare @returncode int

exec @returncode= xp_cmdshell @excutePackage

select @returncode

Execute SSIS package from FILE System(from local D:\)

declare @excutePackage varchar(8000)

declare @cmdline varchar(8000)

set @cmdline='/F "D:Packages\Package6.dtsx" '

set @excutePackage='dtexec.exe'

set @excutePackage=@excutePackage+@cmdline

declare @returncode int

exec @returncode= xp_cmdshell @excutePackage

select @returncode

• email
• facebook
• linkedin
• twitter
• reddit
• google+

Kiran Reddy A

Kiran has been a part of the IT industry for more than six years and Involved in many MSBI projects and product development with extensive usage of ETL and Reporting tools like SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), SQL Server Reporting Service (SSRS) and T-SQL

Get Free Email Updates to your Inbox!

• Rss
• Twitter
• FaceBook
• Google +