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 record count only from FTS

Hi,
Is there a way to get the FTS system to return you the count of matches
only, without passing back all the key ids.
Basically if a user doesn't find what they are looking for with their
initial multi term search, i.e '10k resistor tomatoes'.
I want to display a list of the counts for each word. 10k = 500,
resistor = 46000, tomatoes = 5. This will help them refine their
search.
select count(*) from freetexttable([MyCatName],*,'resistor') seems
quite slow where there are a large number of matches as I assume it's
passing the data back for SQL to count. Is there a way to just get the
count it came up with.
Thanks,
No, there is no way to do this using SQL FTS, some of the Microsoft Search
engines return a hitcount value which is the raw number of hits for all
search tokens.
You could maintain a count should you shred all documents in an inverted
file index - this will require a lot of work however.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Martin" <bigmarts@.hotmail.com> wrote in message
news:1169052866.809003.281110@.a75g2000cwd.googlegr oups.com...
> Hi,
> Is there a way to get the FTS system to return you the count of matches
> only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get the
> count it came up with.
> Thanks,
>
|||Hello Martin,
Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
this.
We cache keyword counts to achieve something similar
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> Hi,
> Is there a way to get the FTS system to return you the count of
> matches only, without passing back all the key ids.
> Basically if a user doesn't find what they are looking for with their
> initial multi term search, i.e '10k resistor tomatoes'.
> I want to display a list of the counts for each word. 10k = 500,
> resistor = 46000, tomatoes = 5. This will help them refine their
> search.
> select count(*) from freetexttable([MyCatName],*,'resistor') seems
> quite slow where there are a large number of matches as I assume it's
> passing the data back for SQL to count. Is there a way to just get
> the count it came up with.
> Thanks,
>
|||Hi, it's 2005.
It's not a major issue, current method of counting the results is
actually performing ok.
Shredding inverted file indexes sounds interesting. Any pointers of
where to look for info on what you were suggesting..?
I assume you
On 18 Jan, 20:11, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Martin,
> Is this SQL 2000 or SQL 2005. The latter is orders of magintude better at
> this.
> We cache keyword counts to achieve something similar