DTS problem with FTP task
-
I have a DTS job designed to FTP files from a UNIX box to SQLServer. It works fine when I execute it from SQL Server Enterprise Manager but when I schedule it from Enterprise manager it fails. The destination folder is on a network drive. I have tried to use the format drive:\path and I have tried the
\\server\path formats for the server. It works using Execute Package but fails when I use Schedule Package. I am thinking it might be authentication issues with the network from SQL Server. Secondly, is there a way to download all files in a remote folder to the destination without having to explicitly state the files to download using the FTP task in DTS?
-
I think you're on the right track looking at authentication. Scheduled jobs run under the security context of the Windows account specified for SQL Server Agent. It probably doesn't have permissions to write to the destination. Are you getting any error messages? Enable package logging and check the log after a failed execution.
I haven't used the FTP task, but I'll try to find some info about your second question.
Greg
Greg
-
According to Books onLine and my 'Professional SQL Server 2000 DTS' book, you can designate an entire directory to FTP by clicking the double arrow (>>
on the FTP Transformation tab of the FTP task properties.Greg
Greg
-
Another twist to the FTP issue is that when I log onto the UNIX box it logs me into my account root /usr/myaccount but the files I want are in /tmp/dwl for our data warehouse dumps. Hence, I have a task that declares a dynamic property task to set a global variable for the source filename: i.e. file;path;size;. Then I have an ActiveX task which sets this parameter to the file(s) that I want downloaded. So the DTS looks like:
ActiveX --->> dynamic property task --->>> FTP task
-
ok so let me understand this... you want to FTP to a unix server, then get all of the files in a certain directory and put the on a network drive somewhere and you're having difficulties doing this in a DTS job?
I'm not that great with activeX and so forth and maybe this is a bit of an old-school solution, but it will work. Use a execute process task to call a batch file that will map your network drive with whatever user credentials you need, if you dont' want to use a domain account, you should be able to create a local account on the server the network drive resides on and pass those credentials to limit the user's acces etc.
Then after your drive is created run ftp -s:commandfile.txt where commandfile.txt is a text file that stores all of your ftp commands, for instance
open [ftpsite]
[username]
[password]
cd [/tmp/dwl]
lcd [yournewly mapped network drive]
prompt (to turn prompting off)
mget *.*
bye
Then control goes back to your batch file and you delete the mapping of the drive and you're done.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply