DTS Error Handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Error Handling

Author

Topic

rrlevron
Starting Member

2 Posts

Posted - 2007-03-29 : 12:11:08

I am using ActiveX scripting(VBscript). I want to copy records from table1 to table2 in SQL Server 2000. If the insert to table2 fails, I want to copy the record from table1 to Exception table.

Any ideas on how to accomplish this?

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-29 : 15:14:26

Yeah, don't use DTS

How big are the tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rrlevron
Starting Member

2 Posts

Posted - 2007-03-29 : 16:50:55

First, to answer your question...

table1 has 31 fields to copy over to table2, 529 records
table2 will recieve 31 fields, 65 records

1. State the question

The package will attempt to insert from table1 to table2. If it is unsuccessful, will cause an error and write that record to Exception table instead.

2. Please post the DDL of your tables (Including Indexes, and constraints)

[Code]

CREATE TABLE [dbo].[SSD] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CallID] [int] NOT NULL ,
[LinkID] [int] NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleInit] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Suffix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth] [datetime] NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PhoneNumber] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Addr1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Addr2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (28) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DistrictCode] [int] NOT NULL ,
[CountyCode] [int] NOT NULL ,
[CallDate] [datetime] NOT NULL ,
[TennCareInvited] [int] NOT NULL ,
[DupFlag] [int] NOT NULL ,
[ExcepFlag] [int] NOT NULL ,
[EnrollBeginDate] [datetime] NULL ,
[EnrollEndDate] [datetime] NULL ,
[AppealFlag] [bit] NULL ,
[AppealFileDate] [datetime] NULL ,
[AppealApplSentDate] [datetime] NULL ,
[AppealApplRecvDate] [datetime] NULL ,
[AppealEnrollBeginDate] [datetime] NULL ,
[AppealEnrollEndDate] [datetime] NULL ,
[AppealDecision] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NoticeMailDate] [datetime] NULL ,
[ApplStage] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplDecision] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplDecisionDate] [datetime] NULL ,
[ApplDenialReason] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ARADApplNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCENTCaseNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCENTStatus] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ApplRecvDate] [datetime] NULL ,
[ApplRegDate] [datetime] NULL ,
[CentralUser] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountyUser] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DDSUser] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSentToCounty] [datetime] NULL ,
[DateAssignedToCntyUser] [datetime] NULL ,
[DateSentToDDS] [datetime] NULL ,
[DDSDecision] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DDSDecisionDate] [datetime] NULL ,
[RoundNumber] [int] NOT NULL ,
[SpanishNotice] [int] NOT NULL ,
[MEUUser] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateSentToMEU] [datetime] NULL ,
[MEUDecision] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEUDecisionDate] [datetime] NULL ,
[ACCENTAuthDt] [datetime] NULL ,
[AssocClaim] [bit] NULL ,
[AssocClmDtFiled] [datetime] NULL ,
[AssocClmDecDt] [datetime] NULL ,
[AssocClmStatus] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssocClmAddlACCENTAction] [bit] NULL ,
[AssocClmAddlACCENTActionTaken] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSAAplFiled] [bit] NULL ,
[SSAAplDt] [datetime] NULL ,
[SSAAplResult] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSAAplAddlACCENTAction] [bit] NULL ,
[SSAAplAddlACCENTActionTaken] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MEUToDDSTrans] [bit] NULL ,
[DDSToMEUTrans] [bit] NULL ,
[Comments] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateModifiedByDHS] [datetime] NOT NULL ,
[FileLangID] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FileID] [int] NOT NULL ,
[UpdatedBy] [smallint] NULL ,
[ARTSAppeal] [bit] NULL ,
[FileNetProcessFlag] [bit] NULL
) ON [PRIMARY]
GO

[Code]

3. Post some sample data in the form of DML

INSERT INTO SSDTOARTS " [ID],[CALLID],[FIRSTNAME],[MIDDLEINIT], "& _
[LASTNAME],[SUFFIX],[DATEOFBIRTH],[SSN],[ADDR1],[ADDR2], [CITY], "& _
[STATE],[ZIP],[DISTRICTCODE ],[COUNTYCODE ],[ACCENTCASENO],[ASSOCCLAIM], "& _
[ASSOCCLMDTFILED ],[ASSOCCLMDECDT],[ASSOCCLMSTATUS], "& _
[ASSOCCLMADDLACCENTACTION],[ASSOCCLMADDLACCENTACTIONTAKEN], "& _
[SSAAPLFILED],[SSAAPLDT],[SSAAPLRESULT],[SSAAPLADDLACCENTACTION], "& _
[SSAAPLADDLACCENTACTIONTAKEN],[ROUNDNUMBER], [ARTSprocessedFlag], "& _
[ARTSprocessedDt],[DateModified] ) "& _
SELECT DISTINCT S.ID, S.CALLID, S.FIRSTNAME, "& _
S.MIDDLEINIT, S.LASTNAME,S.SUFFIX,S.DATEOFBIRTH, "& _
S.SSN, S.ADDR1, S.ADDR2, S.CITY, S.STATE, S.ZIP, "& _
S.DISTRICTCODE , S.COUNTYCODE , S.ACCENTCASENO, S.ASSOCCLAIM, "& _
S.ASSOCCLMDTFILED ,S.ASSOCCLMDECDT, S.ASSOCCLMSTATUS, "& _
S.ASSOCCLMADDLACCENTACTION ,S.ASSOCCLMADDLACCENTACTIONTAKEN, "& _
S.SSAAPLFILED , S.SSAAPLDT, S.SSAAPLRESULT,S.SSAAPLADDLACCENTACTION, " & _
S.SSAAPLADDLACCENTACTIONTAKEN,S.ROUNDNUMBER,0 as ARTSprocessedFlag, " & _
Null as ARTSprocessedDt,getdate() as DateModified FROM [SSD] AS S "& _
WHERE S.ASSOCCLAIM = 1 AND S.SSN IS NOT NULL AND S.CALLID NOT IN "& _
( SELECT CALLID FROM SSDTOARTS) AND S.FILENETPROCESSFLAG=1 "

4. Post whatever DML that you have attempted already...

5. Post the expected results

I want any failed attempts to insert into table2 to instead insert the record into Exception table.

quote:
Originally posted by X002548

Yeah, don't use DTS

How big are the tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources