How Do I Delete Data from Oracle Table from within SQL 2k DTS Package?

[fuzzyocelot]

Hi everyone!

I’ve searched on this for over an hour and haven’t found much yet. So I would really appreciate any advice.

I have a DTS package (SQL 2000) that inserts data into an Oracle table from a SQL Server table via a Transform Data Task. It’s very simple. Now I just need to be able to delete the old data out of the Oracle table before I do the insert.

How do I delete data from an Oracle table in a SQL DTS package? I tried to create a linked server to Oracle but haven't had much luck in getting it to work.

Thanks!!!!

 

[mrdenny]

Add a script task and write the delete statement directly against the Oracle database.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

This is driving me nuts! I’m sure the solution is pretty simple yet it still eludes me. I’m trying to delete all of the records in the Oracle table by using an ActiveX VB script. My script is below.

Function Main()

	Dim  mySQLCmdText, cmd

	set cmd = CreateObject("adodb.command")
	mySQLCmdText = "DELETE FROM prod.importedtable"
	cmd.activeconnection = "Provider=msdaora;Data Source=test;User Id=importid;Password=pwd12#$"
	cmd.commandtext = mySQLCmdText
	cmd.execute

	Main = DTSTaskExecResult_Success
End Function

It keeps hanging up. The table only has 200 records in it yet the DTS package will sit there for 10-15 minutes with a "Not Responding" message on the title bar. Then I end up "killing" it via Task Manager and none of the records were deleted. What am I doing wrong?

Thanks!

 

[AlexCuse]

I don't know squat about oracle, but I think its' funny that their connection string doesn't include a server name...

Does oracle require you to write

delete mytable from mytable

rather than

delete from mytable

?

Just a shot in the dark at this point. Make sure to post back when you find a solution. There seem to be loads of problems making DTS work with oracle. Hopefully you don't need to resort to ODBC :-(

Ignorance of certain subjects is a great part of wisdom

 

[fuzzyocelot]

Thanks for your reply. Actually, the connection string is using an ODBC DSN named test. Maybe that's my problem. Maybe my provider needs to be something else like OraOLEDB or something. I don't know.

I connected to the Oracle instance using Oracle SQL Developer and was able to use the "delete from table" syntax successfully.

I'll probably just go ask our Oracle DBA for help since she also knows SQL Server. I was hoping to figure it out myself or with help from the forums.

Once I get it working, I'll make sure to post the solution.

Thanks!

 

[fuzzyocelot]

Eureka! I figured it out! I had to add an entry to my listener.ora file for the Oracle server. Now it works great! Woo hoo! I already had it in my tnsnames file but forgot about the listener. Doh!