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.
Although this is valid and with 2012 it isn’t needed, it is still a bad practice regarding the use of xm_cmdshell. With 2005 and 2008 (and R2) we have far safer ways of handling this as far as calling SSIS.
xp_cmdshell
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/sql-server-and-xp_cmdshell-the
A safer method of calling SSIS from T-SQL
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/execute-ssis-from-sql
The safer method could easily be modified to call several packages
There are simply far too many safer methods do do this post 2000. xp_* should have died with 2000. IMO and security wise concerns
Ted, I agree with your comments. I should have clarified this is the post. This was for a client that refused to use anything except xp_*. Just a quick answer for them and anyone else looking to use a table with packag names to execute a set of packages.
Thanks this works great!
Are you missing a backslash in this statement?
set @FilePath = ‘C:\Package Path’
should be:
set @FilePath = ‘C:\Package Path\’
You are correct. I have corrected it in the blog
Are you missing a backslash here? set @FilePath = ‘C:\Package Path’
should be:
set @FilePath = ‘C:\Package Path\’