Forum Stats

  • 3,769,586 Users
  • 2,252,984 Discussions
  • 7,875,105 Comments

Discussions

SSIS Lookup Tool inoperative when using Parameterized Queries

SMerrill8
SMerrill8 Member Posts: 21
edited Aug 10, 2017 5:30PM in Oracle Provider for OLE DB

Platform: Windows 7 Enterprise, SSIS on VS2015, 32-bit ODAC

Problem: When using the (32-bit) SSIS Lookup Tool with Oracle OleDB 11.2, I cannot choose the Advanced > Parameters command button without getting an error.

Microsoft Visual Studio

Parameters cannot be extracted from the SQL statement. The provider might not help to parse parameter information from the statement.

ADDITIONAL INFORMATION:

Provider cannot derive parameter information and SetParameterInfo has not been called. (OraOLEDB)

In the SqlCommand property, my Oracle Query in the Lookup tool is simple:

SELECT * FROM TABLENAME WHERE A=:A AND B = :B

Of course I am using ? parameters in the SqlCommandParam property

SELECT * FROM TABLENAME WHERE A=? AND B = ?

Is there no way to map which parameter goes to which input?

Is this Oracle's bug , or Microsoft's bug?

Answers

  • SMerrill8
    SMerrill8 Member Posts: 21
    edited Aug 10, 2017 5:30PM

    Partial Workaround . . . Here is the way to map which data column input feeds into which Oracle Parameter, presuming that you have more than one Oracle Parameter:

    1. Right-mouse the Lookup Component, and choose Properties.
    2. Notice in the Cutom Properties group, there is a property named "ParameterMap".  For my example, the contents of this ended up being the following value: "#15;#66;".  What does this syntax mean?  It is what would be put there if the Bug reported above did not exist and you were able to use the dialog box to map each Oracle Parameter to each Input.
    3. For each Oracle Parameter, starting with the first question-mark in the query,
      1. In the Path (blue arrow) which is pointing into the Lookup component, there are one or more data columns in the path's metadata. Decide which Data column you wish to wire up to the Oracle Parameter in question.   Right-mouse the Source Component which is generating that data column, and choose Show Advanced Editor.
      2. Choose the Input and Output Properties tab, and then expand the Output Columns node which contains the sourcing data column, and select the column itself.
      3. Take note of the number shown in the ID property.  This is the number you want; it is called a Lineage Identifier.  Close the Advanced Editor, Right-mouse the Lookup Component, and choose Properties again. Place that number into the ParameterMap in the next position, in the syntax Pound, Number, Semicolon.
      4. Repeat for the next Oracle Parameter.
    4. Once you have set a number for each Oracle Parameter, you have accomplished the mapping of data columns to the input parameters of the Lookup Component in SSIS.

    The reason I have not closed the question is that even though SSIS is set up properly and works for me now (no showstopper), I still cannot double-click the Lookup Component, Click the Advanced tab, and click the Parameters button without getting an error.

    pastedImage_3.png

This discussion has been closed.