September 21, 2016 at 6:40 pm
Here in the below procedure we are taking some data from temp data and inserting into main table.
Every time the package runs it will insert the records which are not exists in the main table from temp table.
Inserts are being done in loop here.In the same procedure we are inserting errors into error table as well as tracking the insertlog count(No of records inserted).
Now I want to change this logic..
I am expecting to create a package where it first truncates temp table and loads from source(temp) to destination(main table) and last this procedure will do a consolidate task (Call below Procedure)where it insert the records which are not exists in the main table from temp table.
Before this procedure i want to add one execute sql task which will do log insert(success) itand load log error (if it fails) Instead of doing both log insert and log error(same stuff) in the below procedure. Mainly i want to separate it with different stored procedures one for log insert and one for to capture errors.
CREATE PROCEDURE CONSOLIDATE
SET NOCOUNT ON
DECLARE
@Employee,
@EmpCode,
@NoOfRecsInserted INT,
@Error INT
SET @NoOfRecsInserted = 0
DECLARE ACursor CURSOR STATIC FOR
SELECT Employee
FROM dbo.EmpTemp
where --bla bla (filtering the records)
OPEN ACursor
FETCH NEXT FROM ACursor INTO
@Employee
WHILE @@FETCH_STATUS = 0
BEGIN
SET NOCOUNT ON
INSERT INTO dbo.Emp (Employee) VALUES(@Employee)
SET @Error = @@Error
IF @Error <> 0
BEGIN
SET NOCOUNT ON
SELECT 'EmpCode' + CAST(@EmpCode AS VARCHAR(50))
SET NOCOUNT ON
INSERT INTO dbo.ErrorTable(StoreProcName,ProcessName,KeyValue,Error)
VALUES
('dummy','dummy','@EmpCode',@Error)
EXEC dbo.Exception @Employee
END
IF @Error = 0
BEGIN
SET @NoOfRecsInserted = @NoOfRecsInserted + 1
END
FETCH NEXT FROM ACursor INTO
@EMployee
END
CLOSE ACursor
DEALLOCATE ACursor
-- Insert audit rows into LogCount table
EXEC dbo.ILogCounts 'TableName',@NoOfRecsInserted,0,0,'Blabla'
---------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[ILogCounts]
@Name varchar(30),
@InsertCount int = null,
@UpdateCount int = null,
@DeleteCount int = null,
@ScrName varchar(100)
AS
INSERT INTO dbo.LogCounts
(
Name,
InsertCount,
UpdateCount,
DeleteCount,
ScrName,
CreatedDate
)
VALUES
(
@Name,
@InsertCount,
@UpdateCount,
@DeleteCount,
@ScrName,
getdate()
)
Here in the LogCounts stored procedure how can i track the no of records inserted? I mean insert count.
I want to keep this stored procedure(Logcounts) in the execute sql task before the consolidate process(CONSOLIDATE PROCEDURE)
September 22, 2016 at 2:05 am
Apologies in advance if I've misunderstood your question 🙂
Have you considered implementing an output parameter in the stored procedure(s), which can be used with the Execute SQL task "ResultSet" option to populate an SSIS variable?
September 22, 2016 at 6:00 am
Ya,That's what i am concerned like how to implement it.
Using SSIS variable how to get insertion count?
September 23, 2016 at 12:59 am
@@ROWCOUNT or an output clause will return the inserted record count in your SQL procedure. Assigning this value to an output parameter or just doing a SELECT as the last action in the procedure to return it will bring back the value from the procedure.
A quick Google on returning a value from execute SQL tasks will probably explain better and more comprehensively how to setup SSIS to consume the value returned from the stored procedure. For example: https://www.simple-talk.com/sql/ssis/ssis-basics-using-the-execute-sql-task-to-generate-result-sets/
If you're looping multiple times and need to record multiple row counts I'd use a For Each loop component and a second variable to record the overall count. Expression Task or Script Task to update the overall count with its current value + the returned count
Hope this helps get you a bit closer
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply