May 15, 2006 at 4:05 am
I have a DTS package that uses Lookups on a data Transform Task. This package has recently been moved to a new server (with different name). I am unable to amend the lookups to query the new server. Error "Could not obtain required interface from OLE DB provider...."
Did think about creating a new lookup but I still get the same error. Therefore seem to be in never ending circle. Do not want to delete and recreate the DTS as it is somewaht complicated and the author is noo longer with us.
The DTS package were moved by restoring the msdb Dbase from backup files.
It is interesting to note that if I run the DTS via its scheduled Job that it works OK but that if I run it via the DTS it fails with an error about lack of space. I have put this down to the fact that the Job is picking up the Lookups from the old server, but the DTS is trying to create its own and is failing
Thanks
Colin
May 15, 2006 at 8:55 am
have you changed the connections to point to right servers?
Vasc
May 16, 2006 at 1:11 am
The connection for the DTS has been changed. It is just the Lookups which are causing the problem (I think).
When I open the Lookups tab and click on query, I get the error message mentioned before about OLE DB. I cancel the error and then get a screen which has an option to select servers (called linked servers) this shows the original server name but not the new server name. Looking at the original serevr I would expect to see the host server and any linked servers. On the new machine I can see the linked servers and the ORIGINAL host srver but NOT the NEW host server. It is that setting that I need to change (I think).
Thanks
Colin
May 16, 2006 at 7:34 am
On the new server where you want to put the DTS packages add as LinkedServer the server that you wish your lookup to connect to using
sp_addlinkedserver or EM
Vasc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply