July 21, 2006 at 4:47 am
Hi,
I'm not very familar with using DTS packages but I am creating one where a sql query is executed and outputted to a destination file. I then need to ftp this file to a remote server. However, it seems that I can only receive files with the ftp task and not upload them. Is there a way around this?
Many thanks
Reet
July 21, 2006 at 10:34 am
You can do it by executing cmdshell commands in an ActiveX script task.
This code assumes you're using global variables to store the values necessary to perform this task.
Replace FTPSite with the ip or named address of the ftp site, FTPUser with the user id, FTPPassword with the password, FTPFolder with the path of the folder where your file exists(the path can't contain any blank spaces), and FileName with the name of the file you're trying to upload.
Dim objShell
Dim strCommand
Dim strScriptFile
Dim objFSO
Dim objTS
Const TemporaryFolder = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
' create the script file in the temp folder, file name can be anything you want.
strScriptFile = objFSO.GetSpecialFolder(TemporaryFolder).Path & "\FTPDownload.scr"
Set objTS = objFSO.CreateTextFile(strScriptFile, True)
strCommand = "open " & DTSGlobalVariables("FTPSite").Value & vbCrLf
strCommand = strCommand & DTSGlobalVariables("FTPUser").Value & vbCrLf
strCommand = strCommand & DTSGlobalVariables("FTPPassword").Value & vbCrLf
strCommand = strCommand & "lcd " & DTSGlobalVariables("FTPFolder").Value & vbCrLf
strCommand = strCommand & "put " & DTSGlobalVariables("FileName").Value & vbCrLf
strCommand = strCommand & "disconnect" & vbCrLf
strCommand = strCommand & "quit" & vbCrLf
strCommand = strCommand & "exit" & vbCrLf
objTS.Write strCommand
objTS.Close
Set objTS = Nothing
Set objFSO = Nothing
Set objShell = CreateObject("WScript.Shell")
objShell.Run("ftp -s:" & strScriptFile)
Set objShell = Nothing
July 24, 2006 at 7:29 am
There's also an add-in @ http://www.sqldts.com that you can use if you install it on any machines that will need to edit or run the package. It works well, but may not be the easiest thing to work with if you need to dynamically change any of its parameters as those are stored in XML. Check out the website for more details. If you just need to upload the same file to the same place repeatedly, that is probably the easiest choice for you.
-Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply