February 13, 2015 at 4:00 am
Initially I had designed ETL using Dataflow task in SSIS ,
No I have converted into Store procedure using merge statement.
I am getting new records inserted, source records and deleted row count when I am running sqp manually.
In ssis simple I have used the rowcount transformation to capture the records.
below variable i have declared in SSIS Package.
User::etlArchiveLogId
User::sourcerecords
User::newrecords
User::changerecord
now I am incremental loading using Stored procedure
below is the sp which is executing when task success and logs the records.
ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)
AS
BEGIN
UPDATE dbo.EtlArchiveLog
SET TimeEnd = GETDATE(),
RowsSource = @RowsSource,
RowsNew = @RowsNew,
RowsChanged = @RowsChanged,
Successful = @Sucessful,
[Description] = @Description
WHERE EtlArchiveLog_Id = @EtlArchiveLog_Id
END
EXEC dbo.usp_LogArchiveBBxEndTime ?, ?, ?, ?, 1, ''
Now I want to log the variable records which I have declared in my stored procedure .
how can I log it in table please suggest me.
regards,
Vipin jha
February 13, 2015 at 4:06 am
You can use @@ROWCOUNT
ALTER PROCEDURE [dbo].[usp_LogArchiveBBxEndTime]
@EtlArchiveLog_Id int,
@RowsSource int,
@RowsNew int,
@RowsChanged int,
@Sucessful bit,
@Description varchar(500)
AS
BEGIN
UPDATE dbo.EtlArchiveLog
SET TimeEnd = GETDATE(),
RowsSource = @RowsSource,
RowsNew = @RowsNew,
RowsChanged = @RowsChanged,
Successful = @Sucessful,
[Description] = @Description
WHERE EtlArchiveLog_Id = @EtlArchiveLog_Id
SET @RowsChanged = @@ROWCOUNT
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply