Stored procedure sp_ToExecuteAll SSIS packages Remotely

Posted: January 4, 2008 in Uncategorized

————-Configure proxy account———
sp_xp_cmdshell_proxy_account [ NULL { ‘account_name’ , ‘password’ } ]
——–To Enable xp_cmdshell sp——–
sp_configure ‘show advanced options’, 1GORECONFIGUREGOsp_configure ‘xp_cmdshell’, 1GORECONFIGUREGO

—————————————————-
Create procedure [dbo].[sp_ProcessAllFilesInDir]
@FilePath varchar(1000) ,
@FileMask varchar(100) ,
@ProcSp varchar(128)
as set nocount
on
declare @File varchar(128) ,
@MaxFile varchar(128) ,
@cmd varchar(2000)

create table #Dir (s varchar(8000))
select @cmd = ‘dir /B ‘ + @FilePath + @FileMask insert #Dir exec master..xp_cmdshell @cmd
delete #Dir where s is null or s like ‘%not found%’
select @File = ”, @MaxFile = max(s) from #Dir while @File @File select @cmd = @ProcSp + ‘ ”’ + @FilePath + ”’ , ”’ + @File + ”” exec (@cmd) end
drop table #Dirgo
Create procedure [dbo].[sp_ExecutePackage]
@FilePath varchar(1000) ,@Filename varchar(128)asDeclare @cmd varchar(1000) select @cmd = ‘dtexec /F “‘ + @FilePath + @Filename + ‘”‘ exec master..xp_cmdshell @cmdgo

exec sp_ProcessAllFilesInDir ‘D:\’,’package1.dtsx’,’sp_ExecutePackage’
or
Exec [dbo].[s_ProcessAllFilesInDir] @FilePath = ‘d:\TestPackages\’ , @FileMask = ‘t1*.dtsx’ , @ProcSp = ‘[dbo].[s_ExecutePackage]’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s