Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Execute Multiple 2008/2005 SSIS Packages with a T-SQL

If you want to execute a set of SSIS packages in SQL Server 2008 or 2005, you can do this using T-SQL. First you will  need a table with all of your package names on it. Then a While loop to execute each package.

Here is the example code:

Declare @FilePath varchar(2000)

Declare @cmd varchar(2000)

DECLARE @package_name varchar(200)

Declare @PackageCount int

Declare @X int

Set @X = 1

Set @PackageCount = (Select COUNT(*) from Packages)

set @FilePath = ‘C:\Package Path\’

While (@X <= @PackageCount)
Begin

With PackageList as
(
Select PackageName, Row_Number() Over(Order by PackageName) as  Rownum
From Packages
)
SELECT @package_name = PackageName
FROM PackageList
Where Rownum = @X

select @cmd = ‘DTExec /F “‘ + @FilePath + @Package_name + ‘”‘

print @cmd

Set @X = @X + 1

exec master..xp_cmdshell @cmd

End

In the new version of SSIS 2012 you will be able to launch packages with T-SQL Natively.


Comments

Leave a comment on the original post [mikedavissql.com, opens in a new window]

Loading comments...