SQL Server Performance

Capturing errors

Discussion in 'SQL Server DTS-Related Questions' started by v1rt, Jan 20, 2009.

  1. v1rt New Member

    I created a DTS package and I didn't put error checking. I assumed that the vendor's input file will always be correct. Last week, they changed their text file by increasing a field from length of 50 to 75. My BULK INSERT code was failing with truncate and it wouldn't continue the whole DTS package. It started failing last December 22, 2008 and the db administrator never told us about the failure. It failed for more than 20 days without anyone knowing about the failure until a business user noticed last week that a field didn't change. I had to look at the history table I created and yes, the last successful changes was December 19.
    Now am being asked by business to add a code that can notify us of any BULK INSERT failure. How do I capture it? I know how to send the email but capturing the error without letting the DTS package exit, how do I do that?
    Here is some of the code I wrote that I want to have error checking.
    -- ***********************************************************************************************
    -- * Check if RLTDInc file Exists
    -- ***********************************************************************************************
    CREATE TABLE #RLTDIncfilechecks (fileE int, fileD int , fileP int)
    INSERT INTO #RLTDIncfilechecks
    exec master..xp_fileexist @RLTDInc_file
    --drop table #RLTDIncfilechecks
    IF ((select fileE from #RLTDIncfilechecks) = 1)
    BEGIN
    print 'found RLTDInc file'
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_tmp_RLTD_CountyFeeDailyDownload]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    begin
    drop table [dbo].[_tmp_RLTD_CountyFeeDailyDownload]
    print '_tmp_RLTD_CountyFeeDailyDownload dropped'
    end
    CREATE TABLE [dbo].[_tmp_RLTD_CountyFeeDailyDownload] (
    [ATTENTION] [varchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PAYEE] [varchar] (65) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAIL_ADDRESS1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAIL_ADDRESS2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAIL_CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [state] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MAIL_ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [phone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fax] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [fee1] [decimal](6, 3) NULL ,
    [fee1pg] [int] NULL ,
    [fee2] [decimal](6, 3) NULL ,
    [fee2pg] [int] NULL ,
    [fee3] [decimal](6, 3) NULL ,
    [fee3pg] [int] NULL ,
    [sfee1] [decimal](6, 3) NULL ,
    [sfee1pg] [int] NULL ,
    [sfee2] [decimal](6, 3) NULL ,
    [sfee2pg] [int] NULL ,
    [sfee3] [decimal](6, 3) NULL ,
    [sfee3pg] [int] NULL ,
    [roffice] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [page] [varchar] (15) not NULL
    ) ON [PRIMARY]
    SET @SQL = 'BULK INSERT _tmp_RLTD_CountyFeeDailyDownload FROM ''' +@RLTDInc_file+ ''' WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''
    '', FIRSTROW = 2 ) '
    EXEC (@SQL)
    END
    else
    BEGIN
    print 'RLTDInc file missing'
    SET @messageblock = 'There will be no changes since ' + @RLTDInc_file + ' was not found.'
    exec @rc = master.dbo.xp_smtp_sendmail
    @FROM = N'db.sql@xxxxxxxxx.net',
    @FROM_NAME = N'DTS SQLAdmin',
    @TO = N'<RECIPIENTS>,<RECIPIENTS2>',
    @priority = N'NORMAL',
    @subject = N'[CRITICAL] RLTDInc Source File missing',
    @message = @messageblock,
    @server = N'smtprelay.xxxxxxxxx.net'
    IF @rc <> 0 PRINT 'Error sending mail'
    END
    print 'dropping #RLTDIncfilecheck table'
    DROP TABLE #RLTDIncfilechecks
    print 'First #RLTDIncfilecheck was dropped'
    GO
    Thanks in advance!
  2. satya Moderator

    Is the source table schema changes everytime?
    If not why not simply create a SSIS package and schedule the process to IMPORT rows from souce to destination tables.
    Using your login you may not catch compilation errors in the scope they appear, but you can catch them in the calling scopes. And it makes kind ofsense, because the compilation error could appear just as well when the procedure is invoked, as for BULK INSERT, it appears that deferred name resolution is inplay here as well. So what most people thinks is a rune-time error, is a compilation error for SQL Server.

Share This Page