CODEWITHSUNDEEP
ssis
Jenish Jani
Jenish Jani

Reputation: 5

how to call stored procedure from SSIS?

I want to create a SSIS package which will call the stored procedure and dump the output of the procedure into table.

SSIS package will return the output and i need this resultset ouput to dump into another table. But before inserting i want to update the rows from that tables if any rows exist for that date or then insert the resultset.

Upvotes: 0

Views: 145

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

I do't quite understand your requirements but you could do it this way:

  1. Drop on an execute SQL Task

  2. Ensure that YourStagingTable exists and columns match the stored procedure output

Here is some sample code to put in your Execute SQL Task that does what you want:

-- Clear Staging Table
TRUNCATE YourStagingTable

-- Save results of stored proc into staging table
INSERT INTO YourStagingTable
EXEC YourProc

-- Update any existing records
UPDATE YourFinalTable
SET YourUpdateField = YourStagingTable.YourSourceField
FROM YourStagingTable
WHERE YourFinalTable.JoinField1 = YourStagingTable.JoinField1

-- Insert any non existing records
INSERT INTO YourFinalTable (Column1,Column2)
SELECT Column1,Column2
FROM YourStagingTable
WHERE NOT EXISTS (
    SELECT 1 FROM YourFinalTable 
    WHERE YourFinalTable.JoinField1 = YourStagingTable.JoinField1
    )

Upvotes: 0

Related Questions