Solved

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

Posted on 2008-10-30
13
1,083 Views
Last Modified: 2013-11-30
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
Comment
Question by:Lawrence Barnes
  • 8
  • 5
13 Comments
 
LVL 17

Expert Comment

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

Expert Comment

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

Expert Comment

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

Author Comment

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

Author Comment

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

Expert Comment

by:HoggZilla
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

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

Expert Comment

by:HoggZilla
Comment Utility
Strictly using SQL, you would want to use the xp_cmdshell. Do you know how to write a file out fo SQL?
0
 
LVL 17

Expert Comment

by:HoggZilla
Comment Utility
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
Wow... didn't know about the VB in DTS.  I'm attempting to put your scripts into play now.  
0
 
LVL 5

Author Comment

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

Accepted Solution

by:
HoggZilla earned 500 total points
Comment Utility
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
 
LVL 5

Author Closing Comment

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 40
MS SQL server Varchar and nvarchar, GMT_DATE 23 49
Getting certain data from a string 1 22
Ranking Based On Value 3 28
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

9 Experts available now in Live!

Get 1:1 Help Now