Solved

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

Posted on 2008-10-30
17
604 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
ID: 22845095
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
ID: 22845153
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
ID: 22845214
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
ID: 22845271
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
ID: 22845389
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
ID: 22845435
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
ID: 22845449
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
ID: 22845657
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 17

Expert Comment

by:HoggZilla
ID: 22845674
Meant to attach this.
http://support.microsoft.com/kb/96269 
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 22846229
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
ID: 22847157
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
ID: 22847201
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
ID: 22847223
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
ID: 22847330
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
ID: 22847341
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
ID: 22847351
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
ID: 31511847
Thank you so much!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

896 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

16 Experts available now in Live!

Get 1:1 Help Now