locked
OLE DB Command, clicking OK or Refresh is removing all Mappings and External Columns - need help. RRS feed

  • Question

  • I'm using MS SQL as the OLE DB Source and a DB2 database as the OLE DB Command destination in a Data Flow.  Basically I'm calling a Stored Procedure in DB2 and passing parameters to it from the OLD DB source, but in testing this no matter waht command I have in the SQLCommand option it's doing the same thing.  The SqlCommand is this:

    call "DB2USER"."uspDB2REP" (?,?,?,?,?,?,?,?,?);

    Under the Column Mappings tab all my columns from the OLE DB Source are appearing, but in the Input and Output Properties tab nothing is loading under the External columns.  I add Param_0, Param_1, and so forth with the appropriate data types and lengths, go under Column Mappings to map the parameters, but clicking OK and going back in the External Columns and mappings are removed.  Also after entering all this clicking Refresh removes the columns too.

    I can't find any references to this occurring, so any thoughts?  Documentation shows that if External Columns don't pick-up the columns to enter them as Param_0 and so forth, which I did, but I'm at a loss on why this is happening.  

    This is in Data Tools for MS SQL 2012. 

    Thanks in advance.

    Thursday, September 5, 2013 2:34 PM

Answers

All replies

  • I was able to get the Mapping to save by settings ValidateExternalMetadata to False, but now when I run the Data Flow I get this error:

    [OLE DB Command [47]] Error: The layout and one or more components failed validation.
    [SSIS.Pipeline] Error: OLE DB Command failed the pre-execute phase and returned error code 0xC004700D.

    The error code on MSDN is this

    DTS_E_VALIDATIONFAILED
    The layout and one or more components failed validation.

    But I'm not sure what this means.  I honestly just want it to pass the values from the OLE DB source table into the Call command to be parsed by DB2, no validation or anything.  DB2 will take it from there, but it seems SSIS is trying to verify something though it's not telling me what.

    Any thoughts?  

    Thanks.  

    Thursday, September 5, 2013 2:50 PM
  • Hi Samalex01,

    From the document OLE DB Source, we can see that:

    The IBM OLE DB DB2 provider and Microsoft OLE DB DB2 provider do not support using an SQL command that calls a stored procedure. When this kind of command is used, the OLE DB source cannot create the column metadata and, as a result, the data flow components that follow the OLE DB source in the data flow have no column data available and the execution of the data flow fails. 

    Regarding the issue, it is the expected behavior due to the OLE DB limitation.

    To work around this issue, you can call the DB2 stored procedure through a Script task. Here is an example for your reference:
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0f6ac4fe-1ae7-4703-bd71-78725d1e5ab9/call-db2-stored-procedure-from-ssis-2008

    Here is a connect thread about this topic:
    https://connect.microsoft.com/SQLServer/feedback/details/615901/ssis-db2-and-stored-procedures

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Proposed as answer by Zaim Raza Monday, September 9, 2013 4:00 AM
    • Marked as answer by Mike Yin Sunday, September 15, 2013 1:45 PM
    Monday, September 9, 2013 2:53 AM