SELECT COUNT(*) Oracle Table SSIS w/o Linked Server & assign to variable

Question

SELECT COUNT(*) Oracle Table SSIS w/o Linked Server & assign to variable

Welsh Corgi

SSC Guru

Points: 116520
More actions
July 14, 2012 at 3:59 pm

#392747

I need to get a record count from an Oracle Table and store the values in a log table before and after the load (OLE DB).
I need to do this without assigning a Linked Server.
Any ideas would be greatly appreciated?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Welsh Corgi (7/14/2012)
I need to get a record count from an Oracle Table and store the values in a log table before and after the load (OLE DB).
I need to do this without assigning a Linked Server.

If the "log" table is sitting on the Oracle database then SQLPlus can do it, SQLPlus query can be wrapped in a job.

On the other hand, if "log" table is sitting on SQL Server side then I do not know how to do it if specifications forbid the use of a Linked Server.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Paul,

I need to do it within an SSIS Package.

I could truncate a table and insert the count into table but that is Kinda silly.

So it looks like I need to do an OPENROWSET or an OPENQUERY with a Linked Server.

Thanks for replying.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

I could truncate a table and insert the count into table but that is Kinda silly.

What about an UPDATE?

UPDATE Table_Name

SET Column_Name = Column_Name -- fake update

WHERE 1 = 1 -- Optional, always true predicate

Then a row count...

http://msdn.microsoft.com/en-us/library/ms141136.aspx

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Dev (7/15/2012)
I could truncate a table and insert the count into table but that is Kinda silly.
What about an UPDATE?
UPDATE Table_Name
SET Column_Name = Column_Name -- fake update
WHERE 1 = 1 -- Optional, always true predicate
Then a row count...
http://msdn.microsoft.com/en-us/library/ms141136.aspx

Maybe I'm missing something but I need a ROWCOUNT on Oracle Tables?

I'm installing the 64 bit Oracle Client and I'm hoping that that installs the ODBC Drivers so that I can define a linked Server.

I did not want to use OPENQUERY because it is so slow, etc.

Thanks for responding.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

I was able to setup the Linked Server to Oracle so I would consider this post closed.

Thanks for all of the input.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/