| Author |
Topic |
|
kmistic
Starting Member
40 Posts |
 Posted - 2004-06-29 : 14:55:37
|
| I have a package scheduled to execute everyday at 4am, however it never is successful.I have set up the error log and get the following error:Step Error Source: Microsoft OLE DB Provider for ODBC DriversStep Error Description:[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specifiedStep Error code: 80004005Step Error Help File:Step Error Help Context ID:0The package imports data from text files to a sql database. The text files and database of both on the same computer. I am guessing that this is a permissions problem. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-29 : 15:06:57
|
| You need to pull up the DTS package on the database server. You need to configure it there. Then schedule it. The problem is that the package is referring to a DSN that was created on the client but does not exist on the database server. So once you have the package working on the client, you need to open it on the database server and make sure it works there before scheduling it to run.Tara |
 |
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-29 : 15:13:41
|
| When I execute the package from DTS normally, it works. Is there any other way to schedule it without actually physically going to the server and doing it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-29 : 15:15:33
|
| Yes it will work from your client machine. But when it runs as a job, it does not run from your client machine but rather from the database server. So whatever you setup on the client machine must exist on the database server in order for it to work. This means DSNs, files, and anything else that is specific to a machine.I use Terminal Services/Remote Desktops to get to the database server.Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-29 : 15:22:36
|
| cool, i will look into some sort of remote desktop thing.Thanks |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-30 : 14:11:20
|
| Today i went to the server and tried to make this scheduled package.The error i get today is this:Step Error Source: Microsoft OLE DB Provider for ODBC DriversStep Error Description:[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.Step Error code: 80004005Step Error Help File:Step Error Help Context ID:0 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-30 : 14:18:48
|
| Well, the paths in the DTS package are probably referring to files that your client machine can get to but the database server can not. You have to change the path so that the database server can get to them. So if they are connecting to a mapped network drive or UNC path, the database server must be able to get to it and the account that SQLSERVERAGENT service uses must be able to access them as well.Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-30 : 14:22:47
|
| the files are on the same computer as the sql server.I can execute it from the server successfully, if the paths were wrong would that work?how do i know what account SQLSERVERAGENT uses? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-30 : 14:26:14
|
| You have to go to Services in Administrative Tools. Double click on the service and go to logon tab.If the paths were wrong, it should not work from the server. How did you schedule it? Through the schedule package or by creating a job with dtsrun?Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-30 : 14:28:43
|
| Schedule Package |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-30 : 14:36:58
|
| Try running the package from the command line on the database server. Log into the database server with the account that SQLSERERAGENT uses. Then:Go to Start..Run..Type in cmd and hit enter. Type in:dtsrun -S(local) -E -NNameOfDTSPackageIf that works, you should schedule (via SQL Agent new job) the above command instead of using the schedule package as there's no telling what it put in the command.Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-06-30 : 14:42:38
|
| Great I will try thatThanks again!!! |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-07-01 : 11:41:16
|
quote:
Originally posted by tduggan
You have to go to Services in Administrative Tools. Double click on the service and go to logon tab.Tara
I looked here today, the logon is this "./Administrator"What is ./ ? The computer is not logged in as administrator, but the account that is logged on is an administrator.Also,I am executing the package from the server by using dtsrun. It seems to be working. This package takes about an hour to run. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-01 : 11:57:44
|
| ./Administrator means the administrator account on the local machine. Since it is working via dtsrun just schedule the command in SQLAgent.Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-07-01 : 12:35:54
|
| I just scheduled with SQLAgent, It fails at step1 with the Same error:Step Error Source: Microsoft OLE DB Provider for ODBC DriversStep Error Description:[Microsoft][ODBC Text Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.Step Error code: 80004005Step Error Help File:Step Error Help Context ID:0It still works from the command line. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-01 : 12:38:47
|
| When you ran the DTS package from the command line with dtsrun, were you logged in with Administrator? You must test this under the account that SQLSERVERAGENT service uses. Is the DTS package referring to ANY files that are not on local drives of the database server? If they are, you will need to modify the SQLSERVERAGENT service so that it uses a domain account that has local admin privileges and also privileges on the servers where the files are located.Tara |
|
|
|
kmistic
Starting Member
40 Posts |
Posted - 2004-07-01 : 13:07:12
|
| When i ran the DTS package from the command line I was logged in as Dave, which is an administrator. SQLSERVERAGENT uses ./Administrator. So i need to login to windows as ./Administrator or change the user that SQLSERVERAGENT uses. If i change the user to me is that going to create a new account or use the existing account i have on the domain? All files are on the same server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-01 : 13:16:04
|
| You shouldn't use anyone's account for services. A new domain account should be created that is only used by SQL Server. I suggest creating one called sqladmin. It should be a domain user that is a member of the local admin group. After you've logged in with this new account, go into design view of the package and execute it there. This will mimic what the SQLAgent is going to do. So once you receive the error there, you should be able to tell what is wrong with the package as it will tell you which task is failing.Tara |
|
|
|
|
Another scheduled package problem