Catch error in SQL Server from SSIS

There are many cases where we need to trigger SSIS from Stored Procedure and also need to catch error if any error occurs from SSIS.

SSIS can have one more more error depend on package design. Here, we tried to get top 1 error from SSIS and Raise to stored procedure.

Below is sample code for that.

DECLARE
    @pParameter1 VARCHAR(100) = 'Asif',
    @pParameter2 VARCHAR(100) = 'Ghanchi'

DECLARE 
    @vQuery VARCHAR(4000),
    @vPackagePath VARCHAR(100) = '"E:\Package.dtsx"',
    @vPackagePassword VARCHAR(100) = 'naiweb',
    @vResult INT,
    @vLogMsg VARCHAR(255)

SET @vQuery = 'E:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /f ' + @vPackagePath 
    + ' /de ' + @vPackagePassword 
    + ' /X86'
    + ' /SET "\package.Variables[User::Parameter1].Properties[Value]";"\"' + ISNULL(@pParameter1,' ') + '\""'
    + ' /SET "\package.Variables[User::Parameter2].Properties[Value]";"\"' + ISNULL(@pParameter2,' ') + '\""'


DECLARE @vSSISOutput TABLE(ID INT IDENTITY(1,1), SSISOutput varchar(max))

INSERT INTO @vSSISOutput
EXEC @vResult = master..xp_cmdshell @vQuery

DECLARE @pID INT

IF (ISNULL(@vResult,0) <> 0) OR EXISTS(select 1 from @vSSISOutput WHERE SSISOutput LIKE '%Error%')
BEGIN
    SELECT TOP 1 @pID = ID FROM @vSSISOutput WHERE SSISOutput LIKE '%Error%'
    
    SELECT TOP 1 @vLogMsg = SUBSTRING(SSISOutput,CHARINDEX(SSISOutput,'Description:') + 17 ,8000) FROM @vSSISOutput WHERE ID > @pID AND SSISOutput LIKE '%Description:%'
    
    RAISERROR(@vLogMsg,16,-1)
END