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!
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.