Solved

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

Posted on 2008-10-30
17
602 Views
Last Modified: 2013-11-30
I need a code example for:
Using T-SQL (SQL 2000), I need to use an existing procedure to send the data results to a remote FTP folder as a .csv file with pipe delimiters.  I can modify the existig procedure or call it with another procedure.

I have already explored this with DTS ... and am looking for a code example of the above that I can cut/paste/modify to get this request off my desk.  (I'll go back to some great DTS examples later.)

Thank you
0
Comment
Question by:Lawrence Barnes
  • 11
  • 6
17 Comments
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
I'm sure it will work using DTS...the friction is on my end with my unfamiliarity of it.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
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.)
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
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.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
Yes, I could use xp_cmdshell.  I'll start along that line.  Where do the UserID/password and sitepath come into play?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
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
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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

Open in new window

dts-constants.bmp
dts-on-success.bmp
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
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
0
 
LVL 17

Accepted Solution

by:
HoggZilla earned 500 total points
Comment Utility
Finally, last step.
Drag over an Execute Process Task and set the Win32 process to:
command.com
I also need you to update the ActiveX Script Task code to include the "command.com" before the "ftp -s:". It should be this now:
 

strCmdLine ="command.com ftp " & " -s:" & DTSGlobalVariables("ScriptPathFile").Value & " " & DTSGlobalVariables("FTPSite").Value

Open in new window

0
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
Comment Utility
Thank you so much!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now