CODEWITHSUNDEEP
sqlsql-serverstored-proceduresssis
StackTrace
StackTrace

Reputation: 9416

How to view rows inserted in StoredProcedure's temporary table during SSIS package execution

ALTER PROC [dbo].[StoredProcedure1] (@XMLRows XML)
AS
BEGIN
SET NOCOUNT ON

DECLARE @myTable TABLE
    (
     [FirstName] [nvarchar](50) NULL,
     [LastName] [nvarchar](50) NULL,
     [Gender] [char](1) NULL
    )

    Insert into @myTable
    (
    FirstName ,
    LastName ,
    Gender 
    )
    SELECT distinct
    FirstName
    ,LastName
    ,Coalesce(Gender,'') Gender
    FROM
    (
    SELECT
     items.value (N'FirstName[1]', 'nvarchar(50)') AS [FirstName]
    ,items.value (N'LastName[1]', 'nvarchar(50)') AS [LastName]
    ,items.value (N'Gender[1]', 'char(1)') AS [Gender]
    FROM @ExportData a
            CROSS APPLY XML_File.nodes('MyNode/Employee') AS NodeTable(Specs)
            OUTER APPLY XML_File.nodes('MyNode/Employee/StaffList') AS NodeTable1(items)
            OUTER APPLY XML_File.nodes('MyNode/Employee/OtherDetails') AS NodeTable2(items2)
            OUTER APPLY XML_File.nodes('MyNode/Employee/MoreDetailsHere') AS NodeTable3(items3)
            OUTER APPLY XML_File.nodes('MyNode/EvenMoreDetails') AS NodeTable4(items4)
    )myTable

END

I have the above stored StoredProcedure which i am calling from an SSIS package. Is there any way i can view the rows inserted in the temporary table @myTable when the package is executed. I am troubleshooting a bug and i want to see exactly what is in @myTable when my package is executed.

Upvotes: 0

Views: 85

Answers (1)

Eoin Campbell
Eoin Campbell

Reputation: 44306

Seperate to your stored procedure. Create another "real" table in your database.

CREATE TABLE MyTableLog
(
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL,
 [Gender] [char](1) NULL
)

Then during your stored procedure execution, truncate the real table, and repopulate it with the contents generated during your sproc

ALTER PROC [dbo].[StoredProcedure1] (@XMLRows XML)
AS
BEGIN
SET NOCOUNT ON

    DECLARE @myTable TABLE (...)

    Insert into @myTable (...)
    SELECT distinct ...


    TRUNCATE TABLE MyTableLog
    INSERT INTO MyTableLog (FirstName, LastName, Gender)
    SELECT FirstName, LastName, Gender FROM @myTable

END

Once your SPROC has finished executing via the SSIS Package, you can simply query your log table from any query window.

SELECT FirstName, LastName, Gender FROM MyTableLog

Upvotes: 1

Related Questions