Writing errors / status of DTS Package execution
-
Iam new bee in sql server 2000 .
I Created a new Package and have written couple of Data Driven Query Tasks
which is actually inserting/updating rows into the tables. I have a
lookup function and iam checking that in actives script.
i need to write error logs like if the taks fails(Data
Driven Query Task) i have to record them in a text file and is the task is fine even then i have to record a log describing
1) Status :: success or failure
2) Number of records updated
3) Reason for failure if status is success..
I can create a text filke using CreateTextFile function in ActiveX Script....An i can write some matter into it...
But i couldnt find a function which will tell me the number of rows
inserted or updated...and i dont know the function which will give me the status of the row(whether updated or inserted) or even the total number of rows inserted.... Anyone can suggest the functions i have to use would be better ..
Regards
lenin
-
Hi Lenin,
You can do this with a ActiveX Component provided by Microsoft. But you need to have SQL Server 2000 Resource Kit.
Please follow the link. You will get everything you want.
http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part5/c2161.mspx
Thanks,
Ganesh
-
First of all get rid of the Data Driven Query task. Using this task means your data load is done one row at a time.
Import your records into a staging table first using a standard Datapump, or Bulk Insert task. This way you'll get maximum throughput. Once the data is in the staging table, you can run standard T-SQL procedures to scrub the data and remove any bad rows to an exception table.
Then run insert/update/delete prcedures tp update the production table. This way you'll be able to get SQL error codes and row counts. You can log these to a table as well.
--------------------
Colt 45 - the original point and click interface
-
"First of all get rid of the Data Driven Query task".
Word...those things are utter crap. If you follow phillcart's advice your processing will be much faster, and you will be able to do all the things you want (and some things you haven't thought of, yet).
The tables where I log row counts, etc are standard across processes. It was then trivial to write a report and hook it up to these tables, giving me a realtime dashboard of sorts.
---------------------------------------------------------------------------------------
CREATE TABLE [Batch] (
[BatchID] [int] IDENTITY (1, 1) NOT NULL ,
[BatchStartDate] [datetime] NOT NULL ,
[BatchEndDate] [datetime] NULL ,
[BatchDescription] [varchar] (500) NULL ,
[BatchOwner] [varchar] (100) NULL ,
PRIMARY KEY CLUSTERED
([BatchID]) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [BatchDetail] (
[BDid] [int] IDENTITY (1, 1) NOT NULL ,
[BatchID] [int] NOT NULL ,
[Type] [varchar] (50),
[Description] [varchar] (100) NULL ,
[DML] [varchar] (100) NULL ,
[TableName] [varchar] (100) NULL ,
[RowCnt] [int] NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[TotalSecond] [decimal](18, 0) NULL ,
[RowBySecond] [decimal](18, 0) NULL ,
[IO] [decimal](18, 4) NULL ,
[CPU] [decimal](18, 4) NULL ,
[SPID] [int] NULL ,
[SprocName] [varchar] (255) NULL ,
PRIMARY KEY CLUSTERED
([BDid]) ON [PRIMARY] ,
CONSTRAINT [FK_Batch_BatchID] FOREIGN KEY
([BatchID]) REFERENCES [Batch] ([BatchID])) ON [PRIMARY]
GO
CREATE Proc Batch_Insert
@Description varchar(255),
@BatchID int = NULL OUtput,
@isSelect bit = 1
as
set nocount on
Insert Batch (BatchStartDate, [BatchDescription])
Select getdate(), @Description
select @BatchID = scope_identity()
set nocount off
if isnull(@isSelect, 1) = 1
BEGIN
select @BatchID BatchID
END
GO
CREATE Proc Batch_Update
@BatchID int
as
Update Batch
set BatchEndDate = getdate()
Where BatchID = @BatchID
GO
create procedure BatchDetail_Insert
@BatchID int,
@SprocName varchar(100) = 'Not Provided',
@Description varchar(100) = 'Not Provided',
@DML varchar(100) = 'Not Provided',
@TableName sysname = 'Not Provided',
@BDid int = NULL OUTPUT,
@isSelect bit = 0
AS
set nocount on
Insert BatchDetail (BatchID, SprocName, [Description], DML, TableName, StartDate, [IO], [CPU], SPID)
Select top 1 @BatchID, @SprocName, @Description , @DML, @TableName, getdate(),
s.cpu,
s.physical_io,
@@Spid
from master.dbo.sysprocesses s (nolock)
Where s.spid = @@spid
select @BDid = scope_identity()
set nocount off
IF @IsSelect = 0
return @BDid
Else
Select @BDid as BDid
GO
create procedure BatchDetail_Update
@BDid int ,
@RowCnt decimal = NULL
AS
Declare @decIO decimal (18,4),
@decCPU decimal (18,4)
Select @decIO = s.cpu,
@decCPU = s.physical_io
from master.dbo.sysprocesses s (nolock)
Where s.spid = @@spid
Update BatchDetail
Set RowCnt = cast(@RowCnt as decimal),
EndDate = Getdate(),
TotalSecond = datediff(ss, StartDate, Getdate()),
RowBySecond = Case datediff(ss, StartDate, Getdate())
When 0 then @RowCnt
Else cast(@RowCnt as decimal)/cast(datediff(ss, StartDate, Getdate()) as decimal)
END,
[IO] = @decIO - [IO],
[CPU] = @decCPU - [CPU]
Where BDid = @BDid
Signature is NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply