错误” ...非错误输出上的OLE DB Source.Outputs [OLE DB源输出] .Columns [XXXXXXXX]没有相应的输出 [英] Error" ...The OLE DB Source.Outputs[OLE DB Source Output].Columns[XXXXXXXX] on the non-error output has no corresponding output

问题描述

我已经花费了数小时试图解决此问题。对于出现问题的列，我正在使用sql server中的标量函数来获取名称。该功能是正确的并且可以正常工作。然后，我使用存储过程与ssis一起使用。给我错误的列表示错误输出上没有相应的输出列。一切似乎都很好。我已经按照之前的建议进行了以下操作：

Ive spent hours trying to fix this issue. For the column that's giving me the problem i am using a scalar-function in sql server to grab the name. The function is correct and works fine. I then use a stored procedure to use with ssis. The column that is giving me the error is stating it has no corresponding output column on the error output. Everything seems to be fine. I have done the following per previous recommendations:

1. 删除源和目标并重新映射所有内容


2. 已更改DelayValidation属性为true


3. 将最大错误计数更改为100


4. 未选中并重新选中源中的可用外部列

1. Deleted source and destination and remapped everything
2. Changed DelayValidation property to true
3. Changed maximum error count to 100
4. Unchecked and rechecked Available External Columns in source

这些是以前论坛中的建议。

These were recommendations per previous forums.

我是一名实习生，希望自己获得此证书而不必问我的经理。我花了无数小时来解决问题。

I am an intern and want to get this on my own without having to ask my manager. I have spent countless hours trying to fix the problem.

有什么建议吗？

以下是我在OLE DB源中的查询

The following is my query in my OLE DB source

SELECT *
FROM RExtenstionBase R
LEFT OUTER JOIN AExtensionBase A
ON R.ASN = A.SN
AND R.ARN = A.R

此查询为我提供了Dynamics CRM中需要的字段。

This query gives me the fields I need from Dynamics CRM.

以下是我存储的OLE DB命令中的过程调用：

The following is my stored procedure call in the OLE DB Command:

EXEC InsertRepairs ?,?,?,?,?,?,?,?,?,?

以下是我的存储过程脚本：

The following is my stored procedure script:

USE [MSCRM_RC]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[InsertRepairs] 
    -- Add the parameters for the stored procedure here

    @ROID nvarchar(100),
    @AType nvarchar(100),
    @ARN nvarchar(100),
    @DateReceived datetime,
    @RForRR nvarchar(100),
    @C nvarchar(100),
    @SN nvarchar(100),
    @ASN nvarchar(100),
    @Performed nvarchar(100),
    @COR decimal


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @newID AS UNIQUEIDENTIFIER
    SET @newID = NEWID()

INSERT INTO [dbo].[A_RBase] /***BASE TABLE ALWAYS THE SAME ***/
           ([A_RCRIDID] /*Always change this tho */
           ,[CreatedOn]
           ,[CreatedBy]
           ,[ModifiedOn]
           ,[ModifiedBy]
           ,[CreatedOnBehalfBy]
           ,[ModifiedOnBehalfBy]
           ,[OwnerId]
           ,[OwnerIdType]
           ,[OwningBusinessUnit]
           ,[statecode]
           ,[statuscode]
           ,[ImportSequenceNumber]
           ,[OverriddenCreatedOn]
           ,[TimeZoneRuleVersionNumber]
           ,[UTCConversionTimeZoneCode])
     VALUES
           (@newID
           ,GETUTCDATE() -- CreatedOn
           ,'XXXXXXXXXXXXXXXXXXXXXXX' -- CreatedBy
           ,GETUTCDATE()                            -- ModifiedOn
           ,'XXXXXXXXXXXXXXXXXXXXXXX'   -- ModifiedBy
           ,NULL                                    -- CreatedOnBehalf
           ,NULL                                    -- Modified''
           ,'XXXXXXXXXXXXXXXXXXXXXXX'   -- OwnderId
           ,8
           ,'XXXXXXXXXXXXXXXXXXXXXXX'
           ,0                                       -- statecode
           ,1                                       -- statuscode
           ,5                                       -- ImportSequenceNumber
           ,NULL                                -- Overrid
           ,0                                       -- TimeZoneRule 
           ,NULL)                                   -- UTCConversion




INSERT INTO [dbo].[A_RExtensionBase]
           ([A_RCRID]
        ,[A_Name]
           ,[A_AType]
        ,[A_ARN]
           ,[A_DateReceived]
           ,[A_RForRR]
        ,[A_C]
        ,[A_SN]
        ,[A_ASN]
           ,[A_Performed]
           ,[A_COR])
     VALUES
           (@newID,
         @ROID,
         @AType,
         @ARN,
            @DateReceived,
            @RForRR,
         @C,
         dbo.Lookup_SNID_GUID(@SN)
         @ASN,
            @Performed ,
         @COR)


END

以下是我的标量函数：

USE [MSCRM_RC]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER FUNCTION [dbo].[Lookup_SNID_GUID] 
(
      -- Add the parameters for the function here
      @SN_Name nvarchar(100)
)
RETURNS UNIQUEIDENTIFIER
AS
BEGIN
      -- Declare the return variable here
      DECLARE @SN_GUID UNIQUEIDENTIFIER
      SELECT @SN_GUID = SNID

      FROM SNExtensionBase
      WHERE name = (@SN_Name)

      RETURN @SN_GUID
END

推荐答案

显然这是常见的SSIS程序包的错误。我通过钻入OLE DB源代码，单击列选项卡，找到错误消息中包含的可用外部列，然后取消选中该复选框并重新选中它来消除错误。错误已解决！

Apparently this is a common bug with SSIS packages. I got it to eliminate the error by drilling into the OLE DB Source, clicking on the columns tab, finding the Available External Column that was included in the error message, and then unchecking the check box and rechecking it. ERROR SOLVED!

这篇关于错误” ...非错误输出上的OLE DB Source.Outputs [OLE DB源输出] .Columns [XXXXXXXX]没有相应的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！