ORA-12154: TNS:could not resolve service name error in dts 1

[ThomMenzies]

Hi All

I've created a dts on SQL Server 2000 that pulls data from Oracle. I have 4 different connections to 4 different Oracle servers via 'Microsoft OLE DB Provider For Oracle'. The transformation tasks run one after the other, upon successful completion.

Testing the connections works fine and if I only want to pull back a small amount of data (say one day's worth) the package will run without any issues.

However if I broaden the range (to say a week's worth of data - approx 2 million rows) the first task (to connection 1) will work fine, but I get the above error when it tries to perform the next task.

After a lot of Googling, I'm still stumped. The error seems to occur when the connections are not set up correctly with most people obtaining the error when they test the connection, but I can run the entire process on a small dataset.

This leads me to believe it could be a timeout issue, but I'm not sure where or what to change in order to fix it.

Incidentally I get the same error if I use 'Microsoft OLE DB Provider For ODBC Drivers'. The only way around it is to use 'Other (ODBC Data Source)' but that is painfully slow.

Any ideas?

 

[ZenRaven]

Try checking the "Close connection upon completion" option in the workflow task properties.

 

[ThomMenzies]

Zen - worked a treat, you've saved me a headache! Many thanks!

 

[ZenRaven]

I remember the first time I ran into that one. There's no telling how many hours I lost. Glad I could be of assistance.


--Adam
--"He who knows best knows how little he knows." - Thomas Jefferson