Post reply

SSIS Lookup to OLE with error no column information was returned

  • October 24, 2018 at 12:51 pm

    #375780

    I have a data flow (in SSIS 2016) and in the Lookup I am matching on the first three columns. However when I go to the OLE for the Lookup Match Output, I am getting the error that no column information was returned. I can click "Build Query" and it shows the table I am trying to write to.. and when I click on the link between the look up and the OLE, I have data. So do not understand what is going on.

    Any idea's on what I should be looking for the problem?

    Here is the update:

    UPDATE   ztb_matdoc
    SET      Transaction_Type = ?, Document_Type = ?, Posting_DM = ?, Entry_DM = ?, Changed_On_DM = ?, Ref_Doc_Num = ?, Movement_Type = ?, Mat_Num = ?,
             Plant = ?, Storage_Loc = ?, Batch_Num = ?, Special_Stock_Indicator = ?, Vendor_Num = ?, Customer_Num = ?, Sales_Order_Num = ?, Sales_Order_Item = ?, Debit_Credit_Indicator = ?, Currency_Key = ?,
             Amount_in_Local_Currency = ?, Debit_Credit_Revaluation = ?, Valuation_Type = ?, Qty = ?, UOM = ?, PO_Num = ?, PO_Item = ?, Ref_Doc_Fiscal_Yr = ?, Doc_Num_Of_Ref_Doc = ?, Item_Num_Of_Ref_Doc = ?,
             Item_Txt = ?, Goods_Recipient = ?, Cost_Center = ?, Fiscal_Yr = ?, Company_CD = ?, Accounting_Doc_Num = ?, Accounting_Doc_Item = ?, Receving_Issuing_Plant = ?, Movement_Indicator = ?,
             Reason_For_Mvmt = ?, Profit_Center = ?
    WHERE   (Mat_Doc_Num = ?) AND (Mat_Doc_Yr = ?) AND (Mat_Doc_Item = ?)

  • October 24, 2018 at 2:21 pm

    #2011017

    With that many parameters, you need to be rather sure about which one is which in your mapping.   Probably worth three or four re-looks at that kind of thing.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • October 25, 2018 at 3:00 pm

    #2011255

    So here is the table I am trying to write to, the first three rows are the Key and is in the Where stmt

    CREATE TABLE [dbo].[ztb_matdoc](

    [Mat_Doc_Num] [nvarchar](10) NOT NULL,

    [Mat_Doc_Yr] [nvarchar](4) NOT NULL,

    [Mat_Doc_Item] [nvarchar](4) NOT NULL,

    [Transaction_Type] [nvarchar](2) NULL,

    [Document_Type] [nvarchar](2) NULL,

    [Posting_DM] [nvarchar](8) NULL,

    [Entry_DM] [nvarchar](8) NULL,

    [Changed_On_DM] [nvarchar](8) NULL,

    [Ref_Doc_Num] [nvarchar](16) NULL,

    [Movement_Type] [nvarchar](3) NULL,

    [Mat_Num] [nvarchar](18) NULL,

    [Plant] [nvarchar](4) NULL,

    [Storage_Loc] [nvarchar](4) NULL,

    [Batch_Num] [nvarchar](10) NULL,

    [Special_Stock_Indicator] [nvarchar](1) NULL,

    [Vendor_Num] [nvarchar](10) NULL,

    [Customer_Num] [nvarchar](10) NULL,

    [Sales_Order_Num] [nvarchar](10) NULL,

    [Sales_Order_Item] [nvarchar](6) NULL,

    [Debit_Credit_Indicator] [nvarchar](1) NULL,

    [Currency_Key] [nvarchar](5) NULL,

    [Amount_in_Local_Currency] [nvarchar](255) NULL,

    [Debit_Credit_Revaluation] [nvarchar](1) NULL,

    [Valuation_Type] [nvarchar](10) NULL,

    [Qty] [nvarchar](255) NULL,

    [UOM] [nvarchar](3) NULL,

    [PO_Num] [nvarchar](10) NULL,

    [PO_Item] [nvarchar](5) NULL,

    [Ref_Doc_Fiscal_Yr] [nvarchar](4) NULL,

    [Doc_Num_Of_Ref_Doc] [nvarchar](10) NULL,

    [Item_Num_Of_Ref_Doc] [nvarchar](4) NULL,

    [Item_Txt] [nvarchar](255) NULL,

    [Goods_Recipient] [nvarchar](12) NULL,

    [Cost_Center] [nvarchar](10) NULL,

    [Fiscal_Yr] [nvarchar](4) NULL,

    [Company_CD] [nvarchar](4) NULL,

    [Accounting_Doc_Num] [nvarchar](10) NULL,

    [Accounting_Doc_Item] [nvarchar](3) NULL,

    [Receving_Issuing_Plant] [nvarchar](4) NULL,

    [Movement_Indicator] [nvarchar](1) NULL,

    [Reason_For_Mvmt] [nvarchar](4) NULL,

    [Profit_Center] [nvarchar](10) NULL,

    CONSTRAINT [PK_ztb_matdoc] PRIMARY KEY CLUSTERED

    (

    [Mat_Doc_Num] ASC,

    [Mat_Doc_Yr] ASC,

    [Mat_Doc_Item] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

  • October 25, 2018 at 3:01 pm

    #2011256

    This is from the Data Flow Path Editor (Metadata)

    NameData TypePrecisionScaleLengthCode PageSort Key PositionComparison FlagsSource Component
    Mat_Doc_NumDT_WSTR001000OLE DB Source
    Mat_Doc_YrDT_WSTR00400OLE DB Source
    Mat_Doc_ItemDT_WSTR00400OLE DB Source
    Transaction_TypeDT_WSTR00200OLE DB Source
    Document_TypeDT_WSTR00200OLE DB Source
    Posting_DMDT_WSTR00800OLE DB Source
    Entry_DMDT_WSTR00800OLE DB Source
    Changed_On_DMDT_WSTR00800OLE DB Source
    Ref_Doc_NumDT_WSTR001600OLE DB Source
    Movement_TypeDT_WSTR00300OLE DB Source
    Mat_NumDT_WSTR001800OLE DB Source
    PlantDT_WSTR00400OLE DB Source
    Storage_LocDT_WSTR00400OLE DB Source
    Batch_NumDT_WSTR001000OLE DB Source
    Special_Stock_IndicatorDT_WSTR00100OLE DB Source
    Vendor_NumDT_WSTR001000OLE DB Source
    Customer_NumDT_WSTR001000OLE DB Source
    Sales_Order_NumDT_WSTR001000OLE DB Source
    Sales_Order_ItemDT_WSTR00600OLE DB Source
    Debit_Credit_IndicatorDT_WSTR00100OLE DB Source
    Currency_KeyDT_WSTR00500OLE DB Source
    Amount_in_Local_CurrencyDT_WSTR0025500OLE DB Source
    Debit_Credit_RevaluationDT_WSTR00100OLE DB Source
    Valuation_TypeDT_WSTR001000OLE DB Source
    QtyDT_WSTR0025500OLE DB Source
    UOMDT_WSTR00300OLE DB Source
    PO_NumDT_WSTR001000OLE DB Source
    PO_ItemDT_WSTR00500OLE DB Source
    Ref_Doc_Fiscal_YrDT_WSTR00400OLE DB Source
    Doc_Num_Of_Ref_DocDT_WSTR001000OLE DB Source
    Item_Num_Of_Ref_DocDT_WSTR00400OLE DB Source
    Item_TxtDT_WSTR0025500OLE DB Source
    Goods_RecipientDT_WSTR001200OLE DB Source
    Cost_CenterDT_WSTR001000OLE DB Source
    Fiscal_YrDT_WSTR00400OLE DB Source
    Company_CDDT_WSTR00400OLE DB Source
    Accounting_Doc_NumDT_WSTR001000OLE DB Source
    Accounting_Doc_ItemDT_WSTR00300OLE DB Source
    Receving_Issuing_PlantDT_WSTR00400OLE DB Source
    Movement_IndicatorDT_WSTR00100OLE DB Source
    Reason_For_MvmtDT_WSTR00400OLE DB Source
    Profit_CenterDT_WSTR001000OLE DB Source
    Look_Mat_Doc_NumDT_WSTR001000Lookup
    Look_Mat_Doc_YrDT_WSTR00400Lookup
    Look_Mat_Doc_ItemDT_WSTR00400Lookup

  • October 25, 2018 at 3:04 pm

    #2011257

    sgmunson - Wednesday, October 24, 2018 2:21 PM

    With that many parameters, you need to be rather sure about which one is which in your mapping.   Probably worth three or four re-looks at that kind of thing.

    I am not seeing anything that looks wrong.. so I posted (above) both the table I am writing too, and the data coming from the Lookup.

  • October 26, 2018 at 8:12 am

    #2011350

    dwilliscp - Thursday, October 25, 2018 3:04 PM

    sgmunson - Wednesday, October 24, 2018 2:21 PM

    With that many parameters, you need to be rather sure about which one is which in your mapping.   Probably worth three or four re-looks at that kind of thing.

    I am not seeing anything that looks wrong.. so I posted (above) both the table I am writing too, and the data coming from the Lookup.

    Then is it possible that there are simply no matching rows for the data that has been supplied for the lookup?   When all else fails, check your inputs.   You'll want to do that manually, and then verify that the SQL that actually runs is indeed looking things up correctly.   You may need to use SQL Profiler to watch this run.... in order that you can see what actually happens.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 2, 2018 at 6:04 am

    #2012174

    Lookups are case sensitive unless you set the No Cache option.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply