Script to execute all SSIS packages Remotely

Posted: April 1, 2008 in Scripts to use

————-Configure proxy account———

sp_xp_cmdshell_proxy_account [ NULL | { ‘account_name’ , ‘password’ } ]

——–To Enable xp_cmdshell sp——–

sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

—————————————————-

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 < @MaxFile   begin
select @File = min(s) from #Dir where s > @File
select @cmd = @ProcSp + ‘ ”’ + @FilePath + ”’ , ”’ + @File + ””

exec (@cmd)
end

drop table #Dir
go

Create procedure [dbo].[sp_ExecutePackage]

@FilePath   varchar(1000) ,
@Filename   varchar(128)as
Declare @cmd varchar(1000)

select @cmd =  ‘dtexec /F “‘ + @FilePath + @Filename + ‘”‘
exec master..xp_cmdshell @cmd
go

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