December 30, 2008 at 11:35 am
I think there is something I am not understanding about the use of the Webclient class. Someone on this forum gave me the following link for code to upload FTP files: http://www.windowsdevcenter.com/pub/a/windows/2006/12/12/building-ftp-services-using-net-20.html
So I used the binary upload which could be used also for text files according to the site, and it's not working. I'm not even getting a specific error message back.
Anyone have any clue as to what I'm doing wrong? Variables are sanitized for security reasons.
Variables are: FTPServerIP (ftp://10.80.123.8/FolderName)
ClaimFileSource (MyDataFile.txt)
ClaimControlFileSource (MyControlFile.txt)
FTPFileSource (\\MyServer\ShareDrive\Subfolder\Unprocessed\)
JobLogPath (\\MyServer\C$\SQLJobLogs\LogFile.txt)
Here's the code (bolded at the error point) and below that is the error message.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Public Class ScriptMain
Public Sub Main()
Dim JobLogPath As String = Dts.Variables("JobLogPath").Value.ToString
Try
Dim ftpURI As String = Dts.Variables("FTPServerIP").Value.ToString
Dim ClaimFileSourceName As String = _
Dts.Variables("ClaimFileSource").Value.ToString
Dim ClaimControlFileSourceName As String = _
Dts.Variables("ClaimControlFileSource").Value.ToString
Dim FTPFileSource As String = _
Dts.Variables("FTPFileSource").Value.ToString
Dim Claimfiledest As String = ftpURI & ClaimFileSourceName
Dim ClaimControlfiledest As String = _
ftpURI & ClaimControlFileSourceName
'Dim DataFileSource As String = FTPFileSource & ClaimFileSourceName
Dim client As New WebClient
client.Credentials = New NetworkCredential("Login", "Password")
MsgBox("Credentials Set.")
client.UploadFile(Claimfiledest, DataFileSource)
'FTPFileSource & ClaimFileSourceName
'-- above originally used in place of DataFileSource
MsgBox("Data File Uploaded")
client.UploadFile(ClaimControlfiledest, FTPFileSource & _
ClaimControlFileSourceName)
MsgBox("Control File Uploaded")
Using objWriter As New StreamWriter(JobLogPath, True) _
objWriter.WriteLine(String.Format("Files Uploaded" _
& DateTime.Now.ToString))
End Using
MsgBox("Writeline has been passed")
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
MsgBox("Into failure code")
Using objWriter As New StreamWriter(JobLogPath, True) _
objWriter.WriteLine(String.Format("Files NOT Uploaded. _
Reason: " & ex.ToString & " " & DateTime.Now.ToString))
End Using
Dts.TaskResult = Dts.Results.Failure
MsgBox("Leaving Failure Code")
End Try
End Sub
End Class
ERROR
Information: 0x40016041 at MyPackage:
The package is attempting to configure from the XML file "D:\ConfigFiles\MyPackage.dtsConfig".
SSIS package "MyPackage.dtsx" starting.
Error: 0x4 at FTP Claim Files: The Script returned a failure result.
Task failed: FTP Claim Files
SSIS package "MyPackage.dtsx" finished: Success.
I'm pulling my hair out. The solution has to be simple, right?
Help appreciated. Thanks in advance.
December 30, 2008 at 12:00 pm
Okay, I'm a dummy. I didn't look at page two of the link. So, I've corrected my code, but still getting an error.
Take the bolded from above and replace with the following:
....
Dim ControlFileSource As String = FTPFileSource & _
ClaimControlFileSourceName
.....
Dim reqFTP As FtpWebRequest
'Dim client As New WebClient
'client.Credentials = New NetworkCredential("Login", "Password")
reqFTP.Method = WebRequestMethods.Ftp.UploadFile
reqFTP.Credentials = New NetworkCredential ("Login", "Password")
MsgBox("Credentials Set.")
Dim stream As StreamReader = New StreamReader(DataFileSource)
Dim b() As Byte = _
System.Text.Encoding.UTF8.GetBytes(stream.ReadToEnd())
stream.Close()
reqFTP.ContentLength = b.Length
Dim s As Stream = reqFTP.GetRequestStream()
s.Write(b, 0, b.Length)
s.Close()
Dim ftpResp As FtpWebResponse = CType(reqFTP.GetResponse(), _
FtpWebResponse)
MsgBox(ftpResp.StatusDescription)
'client.UploadFile(Claimfiledest, DataFileSource) 'FTPFileSource & _
'ClaimFileSourceName
MsgBox("Data File Uploaded")
'client.UploadFile(ClaimControlfiledest, FTPFileSource & _
'ClaimControlFileSourceName)
Dim stream2 As StreamReader = New StreamReader(ControlFileSource)
Dim b2() As Byte = _
System.Text.Encoding.UTF8.GetBytes(stream.ReadToEnd())
stream2.Close()
reqFTP.ContentLength = b2.Length
Dim s2 As Stream = reqFTP.GetRequestStream()
s2.Write(b, 0, b.Length)
s2.Close()
Dim ftpResp2 As FtpWebResponse = CType(reqFTP.GetResponse(), _
FtpWebResponse)
MsgBox(ftpResp.StatusDescription)
MsgBox("Control File Uploaded")
.....
New Error
Files NOT Uploaded. Reason: System.NullReferenceException: Object reference not set to an instance of an object.
at ScriptTask_53f39ac1d41f45538e96d70edaf6c6bf.ScriptMain.Main() 12/30/2008 1:52:32 PM
December 30, 2008 at 2:43 pm
Hi Brandie,
Im the one who sent you the link for the FTP Upload Function in your previous post.Was waiting for your response since you mentioned of getting someones help.Anyways looking back at the previous post you had asked me if using ip address rather than the address ,would make it work.The answer is yes.Here is the code that I have tried with.Im not very familiar with .Net ,this code might need some coding improvements.
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO
Imports System.Net
Imports System.Text
Public Class ScriptMain
Public Sub Main()
Dim f As New ScriptMain
f.FTPUpload()
End Sub
Public Sub FTPUpload()
Try
Dim filepath As String = "C:\temp\1.txt"
Dim filename As String = "1.txt"
Dim client As New WebClient
client.Credentials = New NetworkCredential("DomainName/xxx", "xxx")
client.UploadFile("ftp://11.200.200.200/Temp/" & filename, filepath)
client.Dispose()
Catch ex As Exception
Console.WriteLine(ex.Message)
Console.ReadLine()
End Try
End Sub
End Class
You should be able to pass the following as variables
FTP Address
User Name & Password
FileName
FilePath
December 31, 2008 at 4:07 am
I'm trying to avoid hardcoding the IP address info or the file info in the script. Things change so often around my workplace, that it's easier for us to change a config file than to change SSIS packages.
And I must be missing something because I still can't get this thing to work. It keeps skipping into the Failure code.
Let me try again.
December 31, 2008 at 4:32 am
Now my error is....
Files NOT Uploaded. Reason: System.Net.WebException: The remote server returned an error: (501) Syntax error in parameters or arguments.
at System.Net.WebClient.UploadFile(Uri address, String method, String fileName)
at System.Net.WebClient.UploadFile(String address, String fileName)
at ScriptTask_53f39ac1d41f45538e96d70edaf6c6bf.ScriptMain.Main() 12/31/2008 6:13:57 AM
ARGH! This is annoying. Destination variable equates to ftp://10.40.106.33/MyFolder/My.File.Name.Data and Source variable equates to \\MyServer\MyShare\Subfolder1\Subfolder2\My.File.Name.Data
Is it all the periods in my filename that could be causing the problem?
It hits the MsgBox of "Credentials Set". Then hesitates & pauses as if it's actually trying to send the file. Then I get the "Into failure code" message as if it skipped the entire file send.
Dim client As New WebClient
client.Credentials = New NetworkCredential ("Login", "Password")
MsgBox("Credentials Set.")
client.UploadFile(Claimfiledest, DataFileSource)
client.Dispose()
MsgBox("Data File Uploaded")
Catch ex As Exception
MsgBox("Into failure code")
Using objWriter As New StreamWriter(JobLogPath, True)
objWriter.WriteLine(String.Format("Files NOT Uploaded. Reason: _
" & ex.ToString & " " & DateTime.Now.ToString))
End Using
Dts.TaskResult = Dts.Results.Failure
MsgBox("Leaving Failure Code")
Oh, and what's with this bit of code? It doesn't seem to alter anything if I take it or leave it.
Public Sub Main()
Dim f As New ScriptMain
f.FTPUpload()
End Sub
Public Sub FTPUpload()
.....
December 31, 2008 at 6:13 am
Okay, I've tried 3 or 4 different ways of scripting this out and now it always comes down to the same error:
Reason: System.Net.WebException: The remote server returned an error: (501) Syntax error in parameters or arguments.
Whether I use StreamReader or the WebClient, it all comes down to the same issue. With the webclient, it's the UploadFile method line. With the streamreader, it bombs at the GetRequestStream method line.
So it has to be in my source or destination path. Does anyone know how I can figure out where my syntax issue is?
Destination is: ftp://10.40.106.33/MyFolder/TESTOR00.UI.DS168C.Data
Source is:
\\MyServerName\Sharefolder\SubFolder\Unprocessed\TESTOR00.UI.DS168C.Data
Or maybe there's a better way of logging the problem to get me more detail?
December 31, 2008 at 6:49 am
Wow. I'm going to fill up this thread will all of my own replies.. @=)
I just figured something out.
Error:
Files NOT Uploaded. Reason: System.Net.WebException: The remote server returned an error: (501) Syntax error in parameters or arguments.
at System.Net.WebClient.UploadFile(Uri address, String method, String fileName)
at System.Net.WebClient.UploadFile(String address, String fileName)
at ScriptTask_53f39ac1d41f45538e96d70edaf6c6bf.ScriptMain.Main() 12/31/2008 6:13:57 AM
I just realized the VB.Net is trying to force me to use the UploadFile(URI, String, String) method when I want to use the UploadFile(String, String) method. I bet this is where my error is coming from.
Does anyone know how to force SSIS / VB.Net to use the method you want to use instead of the default?
Thanks,
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply