针对表中的所有记录运行存储过程

I'm trying to create a SSIS package where I pull data from an Oracle source, insert into a destination table and run that data against a stored procedure that manipulates the data.

我正在尝试创建一个SSIS包,我从Oracle源中提取数据,插入目标表并针对操作数据的存储过程运行该数据。

The stored procedure has the following variables:

存储过程具有以下变量:

@ShiftID Varchar (50),
@ProcedureID int,
@Registered int,
@Performed  int,
@Deferred  int,
@Collected  int,
@QNS int,
@Deleted int,
@Intent int,
@APH int,
@ResultError int output,
@ResultMessage varchar(1024) output

And the table I'd like to run the procedure against contain the following columns:

我想要运行该过程的表包含以下列:

Shift_ID
ProcedureID
Registered
Performed
Deferred
Collected
QNS
Deleted
APH

Right now I have about 500 records in the table and I'd like to run this procedure against all records but I'm not sure how to go about this task.

现在我在表中有大约500条记录,我想对所有记录运行此程序,但我不确定如何执行此任务。

How can I assign the variables to specific columns in the table and then have it loop or cursor through the entire table?

如何将变量分配给表中的特定列,然后将循环或光标放在整个表中?

Thanks,

1 个解决方案

#1

You can use after Insert trigger for that on the Staging table where you insert rows from your ssis package .

您可以在Staging表上使用Insert触发器后,从您的ssis包中插入行。

Like this --

像这样 -

CREATE TRIGGER [dbo].[TriggerName] ON [dbo].[yourTableName]
AFTER INSERT
AS
DECLARE @ShiftID VARCHAR(50)
    ,@ProcedureID INT
    ,@Registered INT
    ,@Performed INT
    ,@Deferred INT
    ,@Collected INT
    ,@QNS INT
    ,@Deleted INT
    ,@Intent INT
    ,@APH INT

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT @ShiftID = ins.ShiftID
        ,@ProcedureID = ins.ProcedureID
        ,@Registered = ins.Registered
        ,@Performed = ins.Performed
        ,@Deferred = ins.Deferred
        ,@Collected = ins.Collected
        ,@Pincode = ins.Pincode
        ,@QNS = ins.QNS
        ,@Deleted = ins.Deleted
        ,@Intent = ins.Intent
        ,@APH = ins.APH
    FROM inserted ins

    -- TODO: Set parameter values here.
    EXECUTE [your Proc Name] @ShiftID
        ,@ProcedureID
        ,@Registered
        ,@Performed
        ,@Deferred
        ,@Collected
        ,@QNS
        ,@Deleted
        ,@Intent
        ,@APH
        -- Insert statements for trigger here
END
GO

#1