Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
slugfest
Starting Member
18 Posts |
 Posted - 2004-08-06 : 17:02:55
|
| I'm trying to run a scheduled DTS package that will grab a text file off another server via FTP and then insert the data in my DB table. Of course, when I run it manually and have the text file deposited on my machine before inserting everything is dandy. And when I then schedule it, it fails. Big surprise, right. The hitch is I don't have access to the SQL Server (hosted) and they say I'm not allowed to deposit text files on the server. So what is my work around? Is there a way to drop the data straight into the table WITHOUT dropping it somewhere first? It seems there would be since the Import/Export Wizard essentially does the same thing.Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-06 : 17:29:54
|
| When a DTS package runs from a job, the job executes under the SQLSERVERAGENT service account. Your ISP would have set this up. You need to give that account access to the file on your machine. It'll probably need access to a share since you won't be able to map a drive on the database server.Tara |
 |
|
|
slugfest
Starting Member
18 Posts |
Posted - 2004-08-06 : 17:53:33
|
| Princess :)You're saying that it can still run the package in DTS and somehow access the file on my machine? How can I do that? How do I set it up to share?Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-06 : 18:42:34
|
| Well if the network allows it, then yes it can be done. You just create share on your machine in the folder where the file exists. Then grant the SQLSERVERAGENT account read access to that share. When I say share, I am using a Windows term. It is not a SQL Server term.Tara |
|
|
|
|
|
|
Import text file workaround