I want to use a mysql stored procedure for the transformation input, which is easy to do with the "Table Input" step ( just CALL the stored proc in the SQL section), but the field names of the result set don't show up downstream in subsequent steps. When I right click on a downstream step and select "show input fields", an "I Can't find any fields" messagebox pops up.
Some may find this a minor annoyance, but it makes subsequent steps difficult to deal with if you cant visualize the structure of the data stream in the transformation.
I saw some posts recommending the use of a "Select Values" Step, but for some reason, I could not get that to work. I spent way too much time trying several different permutations in this step.
This can't really consider this a bug since it seems unreasonable to expect software to reverse engineer a stored procedure to extract result set metadata.
RESOLUTION:
My work around seems to be working ok so far:
1) Create a "Generate Rows" Step, with 0 ( zero) rows output. In this step, configure the fields and datatypes to match the result set of your stored procedure. This step is essentially nothing more than a specification.
2) Create a "Table Input" step that calls your stored procedure.
3 ) Create an "Append Streams" step that merges 1) and 2) above. Set the Head Hop to 1) above, and the Tail Hop to the step from 2) above.
4) create your downstream step and create a hop to it from the "Append Streams". If you right click on this step and select "show input field fields", you can now see the structure of the stream.
Here is a rough picture:
PlanetMySQL Voting: Vote UP / Vote DOWN