Best way of using the FTP action?
-
I have a DTS package that
- Truncates about 30 Microsoft Access tables.
- If successful those tables are repopulated from SQL Server.
What I want to do if everything works is FTP the Access file.
I have set up a seperate DTS package that executes the first and then runs the FTP task however this sometimes fails for a reason I can't fathom.
Is there a way of doing this within a single DTS package, or is there a better way of doing this?
-
This was removed by the editor as SPAM
-
David, I'm not sure if I'm of any help, just been dabbling into FTPtask myself with probnlems of my own. However, I think your question is to vague. Ofcourse you can do a lot in 1 DTSpackage, splitting tasks over 2 packages can help to keep a package less cluttered.
What exactly is failing, the FTPtask, anything else? What kind of message do you get? If I read your situation right I'd create a SQLtask to truncate alle tables, possibly using an ActiveX script to cycle through all your Access db's. At the end have a Good workflow to your FTPtask, again maybe use an ActiveX script to cycle through your Access db's & destinations.
Found a link, which may be of help:
http://www.sqlteam.com/item.asp?ItemID=12408
Good luck
Greetz,
Hans Brouwer -
Basically I created the DTS package using the export data wizard then changed all the "CREATE TABLE" SQL tasks to "DELETE FROM" tasks.
There are twelve database tables. The wizard creates two connections for each database so half the tables are exported using one pair and the other half uses the 2nd pair.
My 2nd DTS task simply says, on successful completion of my first DTS, FTP the results.
For the most part this works fine, but in about 10% of cases the 2nd task simply hangs. No error message is produced, it simply goes on ad inifinitum. I was hoping that something would timeout but it doesn't seem to.
-
So your FTPtask hangs. I'm sorry, I am at a loss there. Does checking the Current activity tell you anything? Have you checked the processes in Taskmanager?
Greetz,
Hans Brouwer -
If the DTS hangs when running within Enterprise Manager then because the display is modal I cannot get to the current activity.
Taskmanager shows 100% CPU utilisation with the sqlserv process going absolutely bonkers.
Nothing shows up in the event log and eventually I have to force enterprise manager to quit.
I have found that the package is more likely to fail under enterprise manager than if it is run as a regular task.
-
And if you schedule the package and run the job?
Greetz,
Hans Brouwer -
In the majority of cases it works. In the past year it has failed 15 times on scheduled runs.
It is more the fact that I cannot get any diagnostic information to tell me why it has failed, than the fact that it has failed.
Is my approach fundamentally correct, that is to have one package that exports the data and a 2nd to run the first and FTP on success?
-
IMO it does not matter to seperate your FTPtask. It is the task that fails, not the package.
Greetz,
Hans Brouwer -
I have founf DTSFTPTask not reliabale. It can only pull file not push.
I have created the wrap around the http://FTP.EXE program from Windows System folder. to get my FTP needs running.
I use CretaProcessTask and pass to it:
command:
cmd
parameter(s):
/C D: && cd "\PathToFolderFromWhereToPushFile" && C:\winnt\system32\ftp.exe -s:"JobPush.txt" > JobPush.log
The file JobPush.txt I will create dynamically before executing the task. If your file location, name and destination do not change you can create it statically.
It would contain something like this:
username
password
prompt
cd /subfolder
mput "nameofthefiletopush.ext"
prompt
quit
After finishing of the run I am examining the content of the log file (from redirection of the http://FTP.EXE output) JobPush.log ( it is placed in the curent folder for the process which in my example is
D:\PathToFolderFromWhereToPushFile\JobPush.log
and if the whole package is executed as a job the D drive will refer to server d: drive.
I also have found that I need to use file local to server so if your accessdatabase file is not on your SQLserver you may need to copy it first (eg using ActiveX script and FileSystemObject.CopyFile method)to your server.
I am using this for more than a year now although mostly for pulling files.
-
I don't like the ftp task in DTS either. It doesn't sound like you really even need DTS though. Just take your delete table statements and put them in one step of a job. On the next step of the job, run http://ftp.exe like the last poster was saying. It's much more reliable.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply