How To Execute SSIS Packages By SQL Server

@filepath is the main folder of the package and @package name is the ssis package name


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