Solved

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

Posted on 2008-10-30
17
611 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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
 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

717 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