April 9, 2003 at 6:25 am
I have used an example from this site labeled Push FTP and have scaled it down dramatically. What I wanted to accomplish was to create a txt file that would be comprised of some general FTP information. This information included the FTP site, login information as well as the location and names of files that needed to be uploaded. Here is what I have so far but the problem is when I run this ActiveX script I get thrown in and infinite loop where the package never ends. Any help would help in determining what I have done wrong OR if someone else has done this same thing in a different way.
I also still need to create it so that I can now EXEC this text file from the command line in DTS (would be nice):
Like - ftp -s:"c:\MyFile.txt"
Any help with that would be greatly appreciated.
Thank you all!
'**********************************************************************
' Create FTP File to Exec
'************************************************************************
Function Main()
On Error Resume Next
Dim strDestPath, fNewFile, strSourcePath, fOldFile, sBackup
Dim fso, f, f1, fc, fcreated, fname, fldrItem, fldrName
Dim objTxtFile, baseDate
' Initialize the variables
strSourcePath = "Q:\Shared\GRIPA\WebDevelopment\GRIPAWEB\images\PDF"
strDestPath = "c:\"
' Set fso = CreateObject("Scripting.FileSystemObject")
' Create the Text File
Set objTxtFile = fso.CreateTextFile( strDestPath & "\ftpSend.txt", TRUE)
' Write the Header
objTxtFile.writeline( "open http://ftp.mysite.com" )
objTxtFile.writeline( "username" )
objTxtFile.writeline( "password" )
objTxtFile.writeline( "cd images" )
objTxtFile.writeline( "cd pdf" )
' Set f = fso.GetFolder(strSourcePath)
dim conn
Set conn = createobject("adodb.connection")
' Set rsFName = Server.CreateObject("adodb.Recordset")
conn.open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=gripaWeb_writer;Password=writer;Initial Catalog=GRIPANET;Data Source=HUMBOLDT75"
SQL = " SELECT FileName" _
& " FROM Alerts" _
& " WHERE (Month(InsDate) = Month(GetDate()) AND Day(InsDate) = Day(GetDate()))" _
& " OR " _
& " (Month(UpdateDate) = Month(GetDate()) AND Day(UpdateDate) = Day(GetDate()))"
Set rsFName = conn.execute(SQL)
Do While Not rsFName.EOF
objTxtFile.writeline( "put """ & strSourcePath & "\" & rsFName("FileName") & """")
rsFName.MoveNext
Loop
objTxtFile.writeline( "quit" )
objTxtFile.Close
' CLEAN UP
Set objTxtFile = Nothing
Set fso = Nothing
conn.close
set conn = nothing
Main = DTSTaskExecResult_Success
End Function
April 10, 2003 at 3:06 am
You have too many double quotes on this line:
objTxtFile.writeline( "put """ & strSourcePath & "\" & rsFName("FileName") & """")
You should only have 3 at the very last part. By having 4 there, it thinks you are escaping the double quote and that the string to write is still going on. So, the MoveNext command isn't being called because it thinks that it is part of the string.
No MoveNext = infinite loop
That's why this is dangerous: On Error Resume Next
April 10, 2003 at 6:35 am
Thanks I did not see that. I have however run the script and it runs however it does not produce a file and I definatley have records that meet that meet my SQL query. In the past at least it created the file with no records to FTP!
April 11, 2003 at 4:46 pm
Can you change the writeline within the loop to just write the query result and not the "put", etc.
Do you get any writes from the function?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 14, 2003 at 8:18 am
Thanks Steve, I actually fixed the problem. For some reason the file that my sql query was producing was dirty and some miscellaneous spaces in it which caused it to not ftp to me remote server. Here is the final code:
ACTIVEX Task
'**********************************************************************
' Create FTP File to Exec
'************************************************************************
Function Main()
On Error Resume Next
Dim strDestPath, strSourcePath
Dim fso
Dim objTxtFile
Dim conn
' Initialize the variables
strSourcePath = "\\viahealth84\gripanet$\images\PDF"
strDestPath = "c:\"
Set fso = CreateObject("Scripting.FileSystemObject")
' Create the Text File
Set objTxtFile = fso.CreateTextFile("c:\ftpsend.txt", TRUE)
' Write the Header
objTxtFile.writeline( "open my.com" )
objTxtFile.writeline( "username" )
objTxtFile.writeline( "password" )
objTxtFile.writeline( "cd images" )
objTxtFile.writeline( "cd pdf" )
Set conn = createobject("adodb.connection")
' Set rsFName = Server.CreateObject("adodb.Recordset")
conn.open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=Webwriter;Password=writer;Initial Catalog=db;Data Source=server"
' ALERTS ******************************************************************************************************
SQL1 = "SELECT FileName" _
& " FROM dbo.Alerts" _
& " WHERE (Month(InsDate) = Month(GetDate()) AND Day(InsDate) = Day(GetDate()))" _
& " OR " _
& " (Month(UpdateDate) = Month(GetDate()) AND Day(UpdateDate) = Day(GetDate()))"
Set rsAlertFName = conn.execute(SQL1)
Do While Not rsAlertFName.EOF
objTxtFile.writeline( "put " & strSourcePath & "\" & rsAlertFName("FileName") & "")
rsAlertFName.MoveNext
Loop
' ANNUAL REPORTS ****************************************************************************************
SQL2 = "SELECT FileName" _
& " FROM dbo.AnnualReports" _
& " WHERE (Month(InsDate) = Month(GetDate()) AND Day(InsDate) = Day(GetDate()))" _
& " OR " _
& " (Month(UpdateDate) = Month(GetDate()) AND Day(UpdateDate) = Day(GetDate()))"
Set rsAnReFName = conn.execute(SQL2)
Do While Not rsAnReFName.EOF
objTxtFile.writeline( "put " & strSourcePath & "\" & rsAnReFName("FileName") & "")
rsAnReFName.MoveNext
Loop
' MEETING MINUTES ***************************************************************************************
SQL3 = "SELECT FileName" _
& " FROM dbo.MeetingMinutes" _
& " WHERE (Month(InsDate) = Month(GetDate()) AND Day(InsDate) = Day(GetDate()))" _
& " OR " _
& " (Month(UpdateDate) = Month(GetDate()) AND Day(UpdateDate) = Day(GetDate()))"
Set rsMMFName = conn.execute(SQL3)
Do While Not rsMMFName.EOF
objTxtFile.writeline( "put " & strSourcePath & "\" & rsMMFName("FileName") & "")
rsMMFName.MoveNext
Loop
' NEWS LETTERS *******************************************************************************************
SQL4 = "SELECT FileName" _
& " FROM dbo.Newsletters" _
& " WHERE (Month(InsDate) = Month(GetDate()) AND Day(InsDate) = Day(GetDate()))" _
& " OR " _
& " (Month(UpdateDate) = Month(GetDate()) AND Day(UpdateDate) = Day(GetDate()))"
Set rsNLFName = conn.execute(SQL4)
Do While Not rsNLFName.EOF
objTxtFile.writeline( "put " & strSourcePath & "\" & rsNLFName("FileName") & "")
rsNLFName.MoveNext
Loop
objTxtFile.writeline( "quit" )
objTxtFile.Close
' CLEAN UP
Set objTxtFile = Nothing
Set fso = Nothing
conn.close
set conn = nothing
Main = DTSTaskExecResult_Success
End Function
AND THEN SQL TASK
EXEC master..xp_cmdshell 'ftp -s:c:\ftpsend.txt'
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply