如何处理 SSIS 包中存储过程返回的 NULL 值？

Question

我是 ssis 的新手，在使用 ssis 将包含 NULL 值的存储过程的结果加载到表中时遇到问题。我的情况如下：

步骤 1

在 Database1 上执行存储过程将返回完整结果集并放入 ADO 对象 User::CallResults

步骤 2

然后我循环遍历 User:: 的结果CallResults 映射 2 个变量：

Variable         Index ADO object Type     Nullable  
User::ID         0      Object              NO  
User::Result     1        Object              Yes    

步骤 3

然后在 Insert Row Into Database2 中获取每一行并执行“insert into dbo.myTable id, result value (?,?)”，

我将 ID 分别映射为 int，将 Result 分别映射为 long。

当我执行时，出现错误：

失败并出现以下错误：“提取文件时发生错误 结果转化为类型为(DBTYPE_I4)的变量”。可能的失败原因： 查询出现问题，“ResultSet”属性设置不正确， 参数未正确设置，或连接未正确建立。

似乎当 Result 中存在 null 时会出错。有什么建议可以让 ssis 允许空值吗？

Answer 1

您可以使用数据流任务实现问题中描述的流程。以下是使用存储过程获取数据然后插入表的分步描述，所有操作均在数据流任务内完成。此示例只是为了说明如何完成此操作，并且它仅使用 SQL Server 的单个实例。

分步过程：

1. 创建两个名为 dbo.Source 和 dbo.Destination 的表，并使用以下内容填充表 dbo.Source数据如屏幕截图#1所示。 脚本部分下提供了创建表脚本。

2. 使用脚本部分下提供的脚本创建一个名为dbo.GetData的存储过程。

3. 在 SSIS 包上，创建一个名为 StoredProcedure 的变量，如屏幕截图 #2 所示。该变量将包含存储过程执行语句。

4. 在连接管理器中创建一个 OLE DB 连接 以连接到 SQL Server 实例。

5. 在 SSIS 包的控制流选项卡上，放置一个数据流任务，如屏幕截图 #3 所示。

6. 双击数据流任务以导航到数据流选项卡。在“数据流”选项卡内，放置一个 OLE DB Source 和一个 OLE DB Destination，如屏幕截图 #4 所示。

7. 配置 OLE DB 源，如屏幕截图 #5 和 #6 所示。请注意，源正在使用在步骤 #3 中创建的变量。存储过程返回的数据将作为源输入。

8. 配置“OLE DB 目标”，如屏幕截图 #7 和 #8 所示。这会将数据插入目标表中。

9. 屏幕截图 #9 显示示例包执行。

10. 屏幕截图#10 显示包执行后表中的数据。请注意，目标表包含 NULL 值。这是可能的，因为列数量可以接受NULL值。但是，如果我们将 NULL 值传递给 ItemNumber 列，则包将会失败，因为该列不可为 null。

希望有帮助。

脚本：
。

CREATE TABLE [dbo].[Destination](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](50) NOT NULL,
    [Qty] [int] NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Source](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ItemNumber] [varchar](50) NOT NULL,
    [Qty] [int] NULL,
CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[GetData]
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      Id
            ,   ItemNumber
            ,   Qty
    FROM        dbo.Source
END
GO

屏幕截图 #1：

屏幕截图 #2：

屏幕截图 #3：

屏幕截图 #4：

屏幕截图 #5 ：

屏幕截图#6：

屏幕截图 #7：

屏幕截图 #8：

屏幕截图 #9：

< img src="https://i.sstatic.net/XDukn.png" alt="9">

屏幕截图 #10：

Answer 2

这并不是真正的答案，但由于代码无法在注释中很好地格式化，因此我将其放在这里。

您是否意识到，如果您的数据库位于同一服务器上，您可以执行以下操作：

INSERT INTO 
    database1.dbo.Results
EXEC 
    database2.dbo.SampleStoredProcedure @param1, @param2, @param3

Answer 3

将此逻辑放入数据流中。完成后，只需进行批量插入，它应该允许空值通过。

SSIS - 无法在 BULK INSERT 中为空白字段插入 NULL