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.

Advertisements
This entry was posted in SSIS, Syndication, T-SQL and tagged . Bookmark the permalink.

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

  1. 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

    • MikeDavisSQL says:

      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.

  2. Etienne says:

    Thanks this works great!

  3. James says:

    Are you missing a backslash in this statement?
    set @FilePath = ‘C:\Package Path’
    should be:
    set @FilePath = ‘C:\Package Path\’

  4. VidLord says:

    Are you missing a backslash here? set @FilePath = ‘C:\Package Path’
    should be:
    set @FilePath = ‘C:\Package Path\’

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