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

Split string with delimiter

While developing any small, medium or large application, we always come to scenario where we need to split string with delimiter. Everyone having their own logic to implement this feature. Please find below 3 ways of implementation for this scenario.

1) Using while loop.

CREATE FUNCTION [dbo].[fn_split_string](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string     
Created By            :  Asif Ghanchi
Date                :  10/03/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput NVARCHAR(MAX),
        @vIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    
    WHILE @vIndex <> 0
    BEGIN
        SET @vInput        = RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vIndex - 1)))
        SET @pInputList = RTRIM(LTRIM(SUBSTRING(@pInputList,@vIndex + @vLengthOfDelimiter,@vLengthOfInputList)))
        
        INSERT INTO @List SELECT @vInput
        
        SET @vLengthOfInputList = LEN(@pInputList)    
        SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    END

    INSERT INTO @List SELECT @pInputList -- Put the last item in
    
    RETURN
END

GO

2) Using XQuery

CREATE FUNCTION [dbo].[fn_split_string_update_1](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Stored Procedure    :  fn_split_string_update_1     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_update_1('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE @vInputList XML = '<InputList><Input>'+ REPLACE(@pInputList,@pDelimiter,'</Input><Input>') +'</Input></InputList>'
    
    INSERT INTO @List
    SELECT
        LTRIM(RTRIM(CAST(IL.Col.query('data(.)') AS NVARCHAR)))
    FROM
        @vInputList.nodes('/InputList/Input') IL(Col);    
    RETURN
END
GO

3) We also can create function with different data type to increaes peformance. Below is one example for INT.

CREATE FUNCTION [dbo].[fn_split_string_INT](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item INT)
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string_INT     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :    
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_INT('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput INT,
        @vIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    
    WHILE @vIndex <> 0
    BEGIN
        SET @vInput        = RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vIndex - 1)))
        SET @pInputList = RTRIM(LTRIM(SUBSTRING(@pInputList,@vIndex + @vLengthOfDelimiter,@vLengthOfInputList)))
        
        INSERT INTO @List SELECT @vInput
        
        SET @vLengthOfInputList = LEN(@pInputList)    
        SET @vIndex = CHARINDEX(@pDelimiter,@pInputList,0)
    END

    INSERT INTO @List SELECT @pInputList -- Put the last item in
    
    RETURN
END
GO

4) Using CTE

CREATE FUNCTION [dbo].[fn_split_string_update_3](
    @pInputList NVARCHAR(MAX),        -- List of delimited items
    @pDelimiter NVARCHAR(MAX) = ',' -- delimiter that separates items
) 
RETURNS @List TABLE(item NVARCHAR(MAX))
AS
/******************************************************************************************************************************************************      
Function Name        :  fn_split_string_update_3     
Created By            :  Asif Ghanchi
Date                :  01/02/2012
                      :   
Description            :  This function returns list list of delimited items.
                    :    
                    : 
Procedure called from : Database      
-------------------------------------------------------------------------------------------------------------------------------------------------------      
Maintenance log      
-------------------------------------------------------------------------------------------------------------------------------------------------------   -----------------------------------------------------------------------------------
MOD LOG:    
DEVELOPER    DATE        COMMENTS                                 
----------- ------------- -----------------------------------------------------------------------------------------------------------------------------      
*******************************************************************************************************************************************************      
** Sample of SP execution      
** ------------------------      
SELECT * FROM dbo.fn_split_string_update_3('1,2,3',',')
**
** Result:
** -----------------------------------------------------------------------------
item
1
2
3
** -----------------------------------------------------------------------------
** RUNTIME in DEV: (hh:min:ss): 00:00:00
** RUNTIME in QA:  (hh:min:ss): 00:00:00     
******************************************************************************************************************************************************/ 
BEGIN
    DECLARE 
        @vInput INT,
        @vCurrentIndex INT,
        @vNextIndex INT,
        @vLengthOfDelimiter INT,
        @vLengthOfInputList INT
        
    SET @vLengthOfDelimiter = LEN(@pDelimiter)
    SET @vLengthOfInputList = LEN(@pInputList)    
    SET @vCurrentIndex = CHARINDEX(@pDelimiter,@pInputList,1)
    SET @vNextIndex = CHARINDEX(@pDelimiter,@pInputList,@vCurrentIndex + @vLengthOfDelimiter)
    
    ;WITH InputList(Input,CurrentIndex,NextIndex)
    AS
    (
        SELECT 
            RTRIM(LTRIM(SUBSTRING(@pInputList,1,@vCurrentIndex - 0 - 1))),  
            @vCurrentIndex,
            @vNextIndex
        WHERE 
            @vCurrentIndex > 0
        UNION ALL
        SELECT 
            RTRIM(LTRIM(SUBSTRING(@pInputList,CurrentIndex + @vLengthOfDelimiter, CASE WHEN NextIndex = 0 THEN @vLengthOfInputList + 1 ELSE NextIndex - CurrentIndex -@vLengthOfDelimiter END))), 
            NextIndex,
            CAST(CHARINDEX(@pDelimiter,@pInputList,NextIndex + @vLengthOfDelimiter) AS INT)
        FROM InputList WHERE CurrentIndex <> 0
    )
    INSERT INTO @List
    SELECT Input FROM InputList
    
    RETURN
END

GO

Compare actual table vs. archive table

We have seen many times that we have altered actual table column but missed to change in archival table. It will fail our archival process to work. Archival is really important to application or it will increase load to database. Purging is used to cleanup unwanted data from database to free space on server.

I tried to create script which used to compare actual table vs. archival table also create backup and release script for same.

SELECT 
    MT.name                    'Table Name',
    MTC.name                'Column Name',
    MTCT.name                'Column Type Name',
    MTC.max_length            'Column max_length',
    MTC.precision            'Column precision',
    AT.name                    'Archive Table Name',
    ATCT.name                'Archive Column Type Name',
    ATC.max_length            'Archive Column max_length',
    ATC.precision            'Archive precision',
    'ALTER TABLE ' + SCHEMA_NAME(AT.schema_id) + '.' +  AT.name + ' ALTER COLUMN ' + ATC.name + ' ' + ATCT.name +
    CASE 
        WHEN ATCT.name IN ('char','varchar','nchar','nvarchar') AND ATC.max_length = -1 THEN '(max)'
        WHEN ATCT.name IN ('char','varchar') THEN '('+ CAST(ATC.max_length AS VARCHAR) +')'
        WHEN ATCT.name IN ('nchar','nvarchar') THEN '('+ CAST(ATC.max_length/2 AS VARCHAR) +')'
        WHEN ATCT.name IN ('decimal','numeric') THEN '('+ CAST(ATC.precision AS VARCHAR) +','+ CAST(ATC.scale AS VARCHAR) +')'
        ELSE ''
    END 'Backup Script',
    'ALTER TABLE ' + SCHEMA_NAME(MT.schema_id) + '.' +  AT.name + ' ALTER COLUMN ' + MTC.name + ' ' + MTCT.name +
    CASE 
        WHEN MTCT.name IN ('char','varchar','nchar','nvarchar') AND MTC.max_length = -1 THEN '(max)'
        WHEN MTCT.name IN ('char','varchar') THEN '('+ CAST(MTC.max_length AS VARCHAR) +')'
        WHEN MTCT.name IN ('nchar','nvarchar') THEN '('+ CAST(MTC.max_length/2 AS VARCHAR) +')'
        WHEN MTCT.name IN ('decimal','numeric') THEN '('+ CAST(MTC.precision AS VARCHAR) +','+ CAST(MTC.scale AS VARCHAR) +')'
        ELSE ''
    END 'Release Script'
FROM 
    sys.objects MT 
    INNER JOIN sys.objects AT ON MT.name + '_Archive' = AT.name
    INNER JOIN sys.columns MTC ON MTC.object_id = MT.object_id
    INNER JOIN sys.types MTCT ON MTCT.user_type_id = MTC.user_type_id
    INNER JOIN sys.columns ATC ON ATC.object_id = AT.object_id
        AND MTC.name = ATC.name
        AND 
            (
                MTC.system_type_id <> ATC.system_type_id
                OR MTC.user_type_id <> ATC.user_type_id
                OR MTC.max_length <> ATC.max_length
                OR MTC.precision <> ATC.precision
                OR MTC.scale <> ATC.scale
            )
    INNER JOIN sys.types ATCT ON ATCT.user_type_id = ATC.user_type_id
WHERE 
    MT.type = 'U' 
    AND AT.type = 'U'
ORDER BY 
    MT.name,
    AT.name