如何查看在 SSIS 包执行期间插入到存储过程的临时表中的行

Question

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

我有上面存储的StoredProcedure，我是从SSIS包调用的。当包执行时，我是否可以查看临时表@myTable中插入的行。我正在排除一个错误，我想看看到底是什么在@myTable当我的包被执行。

Answer 1

分隔到您的存储过程。在数据库中创建另一个"真实"表

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

然后在存储过程执行期间，截断实际表，并用在您的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

一旦你的SPROC完成了通过SSIS包执行，你可以简单地从任何查询窗口查询你的日志表。

SELECT FirstName, LastName, Gender FROM MyTableLog