How to Get the Output Column in OLE DB Command Transformation

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = @.OrderID

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

Hi,

I am writing a Dataflow task which will take a Particular column from the source table and i am passing the column value in the SQL command property. My SQL Command will look like this,

Select SerialNumber From SerialNumbers Where OrderID = ?

If i go and check the output column in the Input and output properties tab, I am not able to see this serial number column in the output column tree,So i cant able to access this column in the next transformation component.

Please help me.

Thanks in advance.

|||

It sounds as tho you are using the wrong component. To source stuff use the OLE DB Source Adapter, not the OLE DB Command.

-Jamie

|||

Dear Jamie,

Thanks for such a quick reply.

U Mean OLEDB Source From DataFlow Sources.

Actually the My dataflow task contains one OLEDB source component which is having connection to one table, from that table i am getting the orderID column, Then i am passing this OrderID column values to the query Which will get the serialnumber in the SerialNumbers table based on this OrderID. And my problem is i cant able to get this selected serialnumber column in the output column tree view,so i that column is not accessable for futher transformations.

Please give me some solution.

Thanks in advance.

- Dhivya

|||

You need the LOOKUP transform. That s exactly what it does.

-Jamie

|||

Dear Jamie,

That also i tried,the table contains multiple values(for same OrderID multiple serial numbers) and the lookup transform will take only the first value and map the same to the others.

-Dhivya

|||

So its a many-to-many?

Then you should use the MERGE JOIN component!

-Jamie

|||

Good advice, Jamie.

Dhivya, remember that the Merge Join needs a sorted input, so you'll also need to use sort components. Alternatively, use ORDER BY in the source queries, and set the IsSorted property of the source adapter output to True.

Donald

|||

I tried merge join, the problem is order ID is not unique in my source table and in transaction table. so if i put some inner or left joins i am not getting the values what i want.

-Dhivya

|||

Merge Join worked for me. I did right outer join.

Thank u Jamie and donald.

But still my question is, we cant get the output columns in OLE DB Command Component if we use select command?

|||

No. That's not what its for!

-Jamie

|||

OK. Thanks a lot

-Dhivya

How to get started on custom Merge Join component?

I need to extend the functionality of the Merge Join transformation to allow more complex joining scenarios. I have found some quickstarts on custom components (i.e. Hash Transform, etc.), however I haven't found any good references on more advanced tasks like processesing multiple inputs. Any ideas?

TIA,

Matthew

First as a disclaimer I would advise against this, as writing a multi imput component is significantly more complex that writing a single input component due to the threading that needs to be done in just about all scenarios.

With that said you can look at any of the samples that have async outputs as a good starting point. The additional complexity comes from the fact that you will, most likely, need to implement your own threading in order to do the merging. This means you will need to make a copy of the buffers to pass to your thread that does the merging. You need this threading because depending on the package both of your inputs could be called on the same thread and if you just waited on one input for the other input to get data then you would hang. All the stock multi input components do their own threading except for UnionAll because that one doesn't have any inter input dependencies to write the data to it output.

HTH,
Matt

|||Matt,

Thanks for the reply. I haven't seen any samples showing async outputs. Could you post a link?

Thanks!

Matthew|||BOL has a RemoveDuplicates sample which has an async output. You can look for sample applications[Integration Services] in the index.

Matt