Jul 21, 2013

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'

No comments:

Post a Comment