Need T-SQL 'example' on how to FTP text file.

Sorry it didn't work good in DTS. Here is a walkthrough for SQL FTP. I hope this helps.
http://www.sqlteam.com/article/using-ftp-in-transact-sql

I'm sure it will work using DTS...the friction is on my end with my unfamiliarity of it.

Hoggzilla: this article requires setting new dlls to the server, I won't be able to do that.  I though it was available via BCP or or as a command shell (using the windows explorer ftp ability.)

xp_cmdshell is disabled in my environment so I can't test this. But you could execute a DOS ftp command from SQL using the following code. This example calls a script file with ftp commands.
set @cmd = 'ftp -s:c:\temp\script.txt'
exec master..xp_cmdshell @cmd
 
First, let's say you already have the file? Would that be true or do you need to create the file in .csv format? If you need the file, I would be happy to write a DTS package for you and send it. I know this is pressing and I could do it 15 minutes. Would that help?

I have been in SSIS for so long now I forgot I cannot save a DTS package to a file, that stinks. It only saves to msdb or other non-worthy file types. I hope you can use the xp_cmdshell.

I've created the stored procedure that outputs the file with a variable name.  FileNameMMDDYYYHHMM.csv.  I'm just looking for how to have this file sent to a specific ftp site...and then to schedule it so it run each week on a certain day/time.   Either via T-SQL where I would just have it run the stored procedure(s) that first create and the FTP the file (triggured by DTS.)  Or via your VB script within DTS.  It would be great if you could create a working example...then I would see how your scripts are pieced together.

Yes, I could use xp_cmdshell.  I'll start along that line.  Where do the UserID/password and sitepath come into play?

I'm sure you probably know ftp, but here is a helpful resource with all the commands. I prefer to build a script file and call it from the -s option. In the stored procedure that build the file and names it, have it build a script file that contains the filename.
You can schedule it to run in SQL Server Agent as a recurring job. That puts me back to the DTS option. Maybe tonight I will document a 1-2-3 for creating the DTS FTP package.

Meant to attach this.
http://support.microsoft.com/kb/96269 

Let's see, how do they say it overseas... If you write a 1-2-3 DTS FTP Package "me luv you long time" :)
I've been going through a lot of the posts...lots of great snippets, but not something that ties it all together for the SQL user who's new to DTS and has no exposure to other forms of scripting.

Lawrence

I am building the steps for you now. The next few posts will contain a series of steps to follow and build the package.
Step 1
Create a new DTS Package. I often use the wizard to get started. In the end, this DTS Package will execute a simple FTP of a file which already exists.
Step 2
Drag over a Microsoft OLE DB Provider for SQL Server connction object and assign connection details. The stored procedure will be on this database.

new-dts.bmp
dts-connection.bmp

Step 3
Right click in the open space of the package and select Package Properties. Choose the Global Variables tab. Let's create 3 string variables. FilePathName is the file we will be sending in the FTP, FTPUser and FTPPwd are self explanatory.
Step 4
Let's assign the values to these variables in a Dynamic Properties Task. From the Task toolbox drag over the Dynamic Properties Task. In the dialog window select Add.

dts-pkg-vars.bmp
dts-dynamic-prop.bmp

Step 4 continued
Assigning values to the variables is available in many ways. You can hard-code them or use this task to retrieve the values. Assuming you have the FilePathName available through a select statement, this is what it would look like.
Press Add, select the variable to fill, click on the Default Value column and it will open the Add/Edit Assignment window. Choose Query or write the SQL to return the value.

dts-dyn-prop-qry.bmp

I have populated the variables with Constant for this example
Step 5
Build the FTP script file. I like to use a script file and call it from the FTP command line via -s option. Drag over an ActiveX Script from the toolbox.  Here is the script, copy and paste. Oh, I created another variable (ScriptPathFile) for the script file path and name. I am using c:\temp\script.txt as the value.
Also, DTS uses a workflow to determine the processing order. Click on the Dynamic Properties Task first and then the ActiveX Script Task second. Select Workflow from the menu and choose "On Success". This will draw a green arrow from DynPrpTsk to ScrptTask.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 
Function Main()
 
 
	set oFSO2 = CreateObject("Scripting.FileSystemObject")
	set oFile2 = oFSO2.OpenTextFile(DTSGlobalVariables("ScriptPathFile"),2,1)
	oFile2.writeline DTSGlobalVariables("FTPUser").Value 	oFile2.writeline DTSGlobalVariables("FTPPwd").Value 
	oFile2.writeline "cd /tables/fx"
	oFile2.writeline "lcd c:\temp"
	oFile2.writeline "put " & DTSGlobalVariables("FilePathName").Value
	oFile2.writeline "quit"
	oFile2.close
 
	dim pkg
	dim cus
	dim strCmdLine	
 
	set pkg = DTSGlobalVariables.Parent
	set cus = pkg.Tasks("DTSTask_DTSCreateProcessTask_1").Customtask
	
 
	'Build the commandline string
	strCmdLine ="ftp " & " -s:" & DTSGlobalVariables("ScriptPathFile").Value & " " & DTSGlobalVariables("FTPSite").Value
 
	'assign it to the ProcessCommandline property
	cus.ProcessCommandLine = strCmdLine
 
	'Clean up
 
	set pkg = nothing
 
 
	Main = DTSTaskExecResult_Success
End Function

Select allOpen in new window

dts-constants.bmp
dts-on-success.bmp

Please note I have also created another variable: FTPSite where you can store your ftp ip address or url. Here are the variables.
dts-vars.bmp

Thank you so much!