Execute SSIS packages without a table containing the package name

Question

I am using SQL Server 2005. I have a few SSIS packages located here: C:\SSIS

This code below is used to execute all the packages but I still need to place each package name in a table called Packages.

Can I execute all the packages without having to save the package name? I just want to supply the path of where they all are sitting and want SQL to do the rest.

DECLARE @package_name varchar(200)

Declare @PackageCount int

Declare @X int

Set @X = 1

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

set @FilePath = 'C:\SSIS'

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

Diego Diego · Accepted Answer · 2012-07-05T08:39:43

you would need to use xp_cmdshell to execute a loop on the folder and get the file names.

Here is an example on how to do it. Of course, you'll need to clean up the result and get only the rows that matter

SET NOCOUNT ON

DECLARE @Command VARCHAR(100)
SET @Command = 'dir  C:\test'

DECLARE @Folder VARCHAR(100)
SET @Folder = 'C:\test'

DECLARE @FilesInAFolder TABLE  (FileNamesWithFolder VARCHAR(500))
INSERT INTO @FilesInAFolder
EXEC MASTER..xp_cmdshell @Command

select * from @FilesInAFolder

How to enable xp_cmdshel:

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO

Execute SSIS packages without a table containing the package name

2 Answers