How to execute SSIS package by sql query in sql server

We can execute a SSIS package and pass the parameters by sql query in sql sever.

Suppose we have created a SSIS package with:

Name of package with path: C:\SSIS\ImportData.dtxs

Suppose it has two user defined variables:

1. ConnStr

2. FilePath

Now we are creating a stored procedure to execute this SSIS package:

CREATE PROCEDURE uspImportdata(

    @ConnStr AS NVARCHAR(2000),

    @FilePath AS NVARCHAR(2000),

    @PackageName AS NVARCHAR(2000)

)

AS

BEGIN

    SET NOCOUNT ON

   

    DECLARE @StatusCode INT

    DECLARE @Params AS NVARCHAR(2000)

    DECLARE @SSISCommand NVARCHAR(4000)

    DECLARE @ErrMsg AS VARCHAR(MAX) = ''

   

    BEGIN TRY

        

         SET @ConnStr = '/set \package.variables[ConnStr].Value;"\"' + @ConnStr + '\"" '

         SET @FilePath = '/set \package.variables[FilePath].Value;"\"' + @FilePath + '\"" '

   

         SET @Params =  @Params + @ConnStr + @FilePath

         SET @SSISCommand = 'DTEXEC /f ' + @PackageName + ' ' + @Params

        

         CREATE TABLE #ErrTbl(

                 ErrMsg VARCHAR(2000)

         )

            

        INSERT INTO #ErrTbl

         EXEC @StatusCode = xp_cmdshell @SSISCommand

        

         IF @StatusCode = 0

             SELECT 'Success'

         ELSE BEGIN

             SELECT @ErrMsg = @ErrMsg + ISNULL(ErrMsg,'')  FROM #ErrTbl WHERE ErrMsg LIKE '%Description%'

             RAISERROR(@ErrMsg,16,1)

         END

        

    END TRY

    BEGIN CATCH

         SELECT ERROR_NUMBER()

    END CATCH

END

To execute it:

EXECUTE uspImportdata

    'Data Source=ServerName;Initial Catalog=DbName;Integrated Security=True;',

    'C:\SSIS\demo.txt',

    'C:\SSIS\ImportData.dtxs'