• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1101
  • Last Modified:

Use DTS to create text file via stored procedure and FTP to a site

Using SQL 2000, I need to:
* Get data via a stored procedure
* Write data to a text file that has a unique name ("Filename + date".txt)
* FTP this text file to a remote server (ftp://ip.add.res.s/Reports) using User ID and Password

I understand that could be run via T-SQL from a command shell or via DTS.  Please give me an example of how to complete one way or the other.
0
Lawrence Barnes
Asked:
Lawrence Barnes
  • 8
  • 5
1 Solution
 
HoggZillaCommented:
First, here is a script to name a file in an ActiveX scipt using a couple of variables, filename and filedate.
'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 
Function Main()
 
	'Change the File Name
	Set objTextConn = DTSGlobalVariables.Parent.Connections("Connection 2")
	DTSGlobalVariables("full_filename").Value = "\\mvciad\lalfidfs\FIS_FTP\FXRATES\" & _
		DTSGlobalVariables("filename").Value & DTSGlobalVariables("filedate").Value & ".txt"
	objTextConn.DataSource = DTSGlobalVariables("full_filename").Value
 
	Main = DTSTaskExecResult_Success
End Function

Open in new window

0
 
HoggZillaCommented:
I execute this Query in a Dynamic Properties Task against a SQL Server database to get the date and time for the file:

select replace(replace(convert(char(8),getdate(),10),'-','') + '_' + replace(convert(char(8),getdate(),108),':',''),' ','')

Open in new window

0
 
HoggZillaCommented:
In this script I build the script file for the ftp command: Obviously you can code in the sensitive data or store it in variables.
In this file I am using psftp as my ftp tool.

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
 
Function Main()
 
	set oFSO2 = CreateObject("Scripting.FileSystemObject")
	set oFile2 = oFSO2.OpenTextFile("\\server\folder\sendfile",2,1)
	oFile2.writeline "cd /tables/next"
	oFile2.writeline "lcd \\server\folder"
	oFile2.writeline "put " & DTSGlobalVariables("full_filename").Value
	oFile2.writeline "bye"
	oFile2.close
 
	dim pkg
	dim cus
	dim strCmdLine	
 
	set pkg = DTSGlobalVariables.Parent
	set cus = pkg.Tasks("DTSTask_DTSCreateProcessTask_1").Customtask
	
	'dpwd if you want to populate here
	dtsglobalvariables("dpwd").value = "password"	
 
	'Build the commandline string
	strCmdLine ="\\server\folder\psftp.exe " & DTSGlobalVariables("duser").Value & _
		 "@ftpsite -pw " & DTSGlobalVariables("dpwd").Value & " -be -b \\server\folder\sendfile"
 
	'assign it to the ProcessCommandline property
	cus.ProcessCommandLine = strCmdLine
 
	'Clean up
	set pkg = nothing
	Main = DTSTaskExecResult_Success
End Function

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Lawrence BarnesAuthor Commented:
Hoggzilla,
I'm using SQL 2000.  How would I run this script with it?  I do not have VB resources available to me.
Lawrence
0
 
Lawrence BarnesAuthor Commented:
Oh.. and I have a stored procedure that can provide the filename... or include the file name in the stored procedure that gets the data.
0
 
HoggZillaCommented:
Create an execute process task to call, notice that in the script above i set the ProcessCommandLine to my strCmdLine contents. I had to do this to make it dynamic.
If you have access to xp_cmdshell you could drop in an Execute SQL Task and call the dos ftp from there. Supplying your script file you create as in the code above.
exec master.dbo.xp_cmdshell 'ftp -s:sendfile
The contents of script.txt might look like this:
yourUserName
yourPassword
bin
cd /files
put file.zip
 bye '
0
 
HoggZillaCommented:
Hoggzilla,
I'm using SQL 2000.  How would I run this script with it?  I do not have VB resources available to me.
Lawrence
In DTS, use an ActiveX Script Task
0
 
HoggZillaCommented:
Strictly using SQL, you would want to use the xp_cmdshell. Do you know how to write a file out fo SQL?
0
 
HoggZillaCommented:
0
 
Lawrence BarnesAuthor Commented:
Wow... didn't know about the VB in DTS.  I'm attempting to put your scripts into play now.  
0
 
Lawrence BarnesAuthor Commented:
I'm a VB Noob... some questions:
***Below...I update everything in quotes right?  and the password, etc.  But I didn't see a spot for User ID.

set oFSO2 = CreateObject("Scripting.FileSystemObject")
      set oFile2 = oFSO2.OpenTextFile("\\server\folder\sendfile",2,1)
      oFile2.writeline "cd /tables/next"
      oFile2.writeline "lcd \\server\folder"
      oFile2.writeline "put " & DTSGlobalVariables("full_filename").Value
      oFile2.writeline "bye"
      oFile2.close
 *** Next question.  I understand this code and can replace it with a different sql statement.  But I don't see how to insert it into a dynamic properties object and have it relate to the file name.

This is my first DTS creation.  Another route would be an example  of the xp command shell that would be embedded in sql.  Opinion?

0
 
HoggZillaCommented:
In my example this is just building the ftp script. Look a little further down in the code and you see where I create a complete command with exectuable and assign it to the process task.
'Build the commandline string
strCmdLine ="\\server\folder\psftp.exe " & DTSGlobalVariables("duser").Value & _
"@ftpsite -pw " & DTSGlobalVariables("dpwd").Value & " -be -b \\server\folder\sendfile"

'assign it to the ProcessCommandline property
cus.ProcessCommandLine = strCmdLine
 
0
 
Lawrence BarnesAuthor Commented:
Thank you Hoggzilla.  Looking at it I'm sure this will work.  Won't know for a bit because I'm struggling to understand how to piece it together in the DTS package designer.  Thank you.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now