Roxanne25
asked on
Error Message inside DTS Package
SQL Server Version: 2000
Hi, I am having some ODBC error messages when I try to open up DTS packages on our deveopment and production servers. Nobody has been able to answer this question for me at work, so I'm hoping one of the experts here can!
I'm getting a few variations on this error...
Mind you, all these DTS packages are live and running just fine on the server and they open up properly for the other DBA... so, I'm assuming it has something to do with my computer...
When I double click on some of the objects inside of the DTS package I'm getting errors...they usually occur when the package is connecting to an external data source to pull in data.
The first message is: Access Denied. With just an OK button. I click the OK button and then another error message pops up that says: No provider is selected. To continue, you must select a valid provider. With just an ok button. When you hit ok it does nothing. (This occurs when I double click on the solid black line that is going from one object to the other. If I click on the connection object, it just says access is denied and then brings up the connection window empty.
The second message I get (in a different DTS package): Error Source: Microsoft OLE DB Provicer for ODBC Drivers Error Description: Microsoft ODBC Driver Manager Data source name not found and no default driver specified. This occurs when I click on the solid black line that goes between the connection objects. If I click on the connection object, it just says access denied and brings up the blank connection window as in the first scenerio.
I assume this has something to do with the providers/drivers I have installed on my desktop... but I can't figure out how to fix it. I am running Windows XP and I cannot update the MDAC because it says I don't need to do it as my version of Windows has everything in it already.
Any thoughts?
Hi, I am having some ODBC error messages when I try to open up DTS packages on our deveopment and production servers. Nobody has been able to answer this question for me at work, so I'm hoping one of the experts here can!
I'm getting a few variations on this error...
Mind you, all these DTS packages are live and running just fine on the server and they open up properly for the other DBA... so, I'm assuming it has something to do with my computer...
When I double click on some of the objects inside of the DTS package I'm getting errors...they usually occur when the package is connecting to an external data source to pull in data.
The first message is: Access Denied. With just an OK button. I click the OK button and then another error message pops up that says: No provider is selected. To continue, you must select a valid provider. With just an ok button. When you hit ok it does nothing. (This occurs when I double click on the solid black line that is going from one object to the other. If I click on the connection object, it just says access is denied and then brings up the connection window empty.
The second message I get (in a different DTS package): Error Source: Microsoft OLE DB Provicer for ODBC Drivers Error Description: Microsoft ODBC Driver Manager Data source name not found and no default driver specified. This occurs when I click on the solid black line that goes between the connection objects. If I click on the connection object, it just says access denied and brings up the blank connection window as in the first scenerio.
I assume this has something to do with the providers/drivers I have installed on my desktop... but I can't figure out how to fix it. I am running Windows XP and I cannot update the MDAC because it says I don't need to do it as my version of Windows has everything in it already.
Any thoughts?
ASKER
Hmmm ... I do not have permission to the server to open them there. There is no other way to figure out which ones are the issue?
And lets say it was Oracle causing the problem, how do I get the oracle drivers registered on my computer? I have Oracle installed... wouldn't that add them?
And lets say it was Oracle causing the problem, how do I get the oracle drivers registered on my computer? I have Oracle installed... wouldn't that add them?
ASKER
When I open up the one connection object that gives me an error the blank connection screen defaults to Microsoft Data Link... is that the one its using or is it just defaulting to that one when it opens?
It could be a data link - I think this is usually a Microsoft Universal Data Link, which is really a text file containing the connection information. If this is how the server is configured, then your DTS will not work on your client without this file located in the same location (path) as it would be on the server. See http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx.
To answer your first question, I believe that DTS packages are actually vb script objects, so you could save the package to a file, and open it in a text editor. You should be able to work out the data source and connection properties from this if you can't get on the server. I had to do this many years ago, but can't remember if I had to se a third party tool to export the package or if you can do it directly from the enterprise manager; you will have to investigate this.
To answer your first question, I believe that DTS packages are actually vb script objects, so you could save the package to a file, and open it in a text editor. You should be able to work out the data source and connection properties from this if you can't get on the server. I had to do this many years ago, but can't remember if I had to se a third party tool to export the package or if you can do it directly from the enterprise manager; you will have to investigate this.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The easiest way to do this is to open the DTS package on the server and look at how the data pump tasks are configured. Once you do this, you will see where the data is coming from (data source), and which drivers are required to access this data(data pump).
At worst you can post this information here and we can see if we know what drivers you need.