April 29, 2014 at 2:01 am
Also you can try to check what the response is and encode it as in this example http://msdn.microsoft.com/en-us/library/system.net.httpwebrequest.getresponse(v=vs.80).aspx
Far away is close at hand in the images of elsewhere.
Anon.
April 29, 2014 at 2:08 am
Also forgot to mention the response contains StatusCode and StatusDescription, check these as well as you might have an error which would be why no file was sent.
Far away is close at hand in the images of elsewhere.
Anon.
April 30, 2014 at 5:15 am
I have tried a couple of different versions of this and keep coming up with "ReadToEnd is not a member of System.IO.Stream". Google can't seem to find anything with that exact error message (it keeps sending me to the official MSDN links on how to use ReadToEnd and System.IO.Stream) and keeps removing any quotes I put around the message, preventing me from searching only on that.
Here's the code as it currently stands:
Public Sub Main()
Dim FilePathName As String = Dts.Variables.Item("InboundPathAndName").Value
MsgBox("The Creds are: " + Dts.Variables.Item("HttpAuth").Value.ToString + " and the website is: " + Dts.Variables.Item("HttpPostURL").Value.ToString _
+ " and the file path / name is: " + Dts.Variables.Item("InboundPathAndName").Value.ToString)
Try
'create the information we need to send as part of post to let
'the ASPX page know about the file data
Dim DataString As StringBuilder = New StringBuilder
DataString.Append("------=_Part_153_2117173873.1044898432007" + vbCrLf)
DataString.Append("Content-Disposition: form-data; name=" + _
"""" + "file" + """" + "; filename=" + """" + FilePathName + """" + vbCrLf)
'set the file type to octet-stream so we can handle any kind of Data()
DataString.Append("Content-Type: application/octet-stream" + vbCrLf + vbCrLf)
Dim webReq As HttpWebRequest = SetWebRequest(New System.Uri(Dts.Variables.Item("HttpPostURL").Value), FilePathName)
'send the data about the file
SendFileInformation(webReq.GetRequestStream(), DataString, FilePathName)
'Get the response from the server
Dim webResp As HttpWebResponse = CType(webReq.GetResponse(), HttpWebResponse)
Dim sr As Stream = webResp.GetResponseStream()
Dim readStream As New StreamReader(sr, Encoding.UTF8)
'put the stream data in a string
Dim respData As String = sr.ReadToEnd()
sr.Close()
webResp.Close()
MessageBox.Show(respData)
Catch webExcp As WebException
'If you reach this point, an exception has been caught.
'Write out the WebException message.
MessageBox.Show("An error occured." + webExcp.ToString())
Return
Catch myExcp As Exception
MessageBox.Show("A WebException has been caught." + myExcp.ToString())
Return
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Private Function SetWebRequest(ByVal Url As System.Uri, ByVal FilePathName As String) As HttpWebRequest
Dim webReq As HttpWebRequest
webReq = CType(WebRequest.Create(Url), HttpWebRequest)
'POST Data
webReq.Method = "POST"
webReq.Headers.Add("Accept-Language", "en-us")
webReq.KeepAlive = True
' convert username:password to basic 64 format and append to the HTTP header
webReq.Headers.Add("Authorization", Dts.Variables.Item("HttpAuth").Value.ToString)
'If My.Computer.FileSystem.FileExists(FilePathName) Then
'webReq.ContentLength = New FileInfo(FilePathName).Length
'Else
' Throw New Exception("File not found")
' End If
webReq.ContentType = "multipart/form-data; boundary=------=_Part_153_2117173873.1044898432007"
webReq.Headers.Add("Accept-Encoding", "gzip, deflate")
Return webReq
End Function
Private Sub SendFileInformation(ByVal tempStream As Stream, ByVal DataString As StringBuilder, ByVal FilePathName As String)
Dim ReadIn As FileStream = New FileStream(FilePathName, FileMode.Open, FileAccess.Read)
ReadIn.Seek(0, SeekOrigin.Begin) 'move to the start of the file
Dim DataRead As Integer = 0
Dim FileData(1024) As Byte
Dim FileInfo As Byte() = System.Text.Encoding.Default.GetBytes(DataString.ToString())
tempStream.Write(FileInfo, 0, FileInfo.Length)
Do
DataRead = ReadIn.Read(FileData, 0, 1024)
If (DataRead > 0) Then 'we have data
tempStream.Write(FileData, 0, DataRead)
Array.Clear(FileData, 0, 1024) 'clear the array
End If
Loop While (DataRead > 0)
Dim endingBoundary As Byte() = _
System.Text.Encoding.Default.GetBytes(vbCrLf + "------=_Part_153_2117173873.1044898432007" + vbCrLf)
' send the closing boundry
tempStream.Write(endingBoundary, 0, endingBoundary.Length)
'close the stream
ReadIn.Close()
tempStream.Close()
End Sub
End Class
Any thoughts on why I'm getting this specific error?
I'm sure I need to change the code around the response (the String declaration probably won't work), but I've tried everything short of the Console.Writeline because I don't want this to write to the Console. Eventually the response is going to a SQL table as soon as I make sure the message box returns correctly. But before I get there, I need to figure out what is going on with this specific error.
April 30, 2014 at 5:26 am
Change
Dim respData As String = sr.ReadToEnd()
to
Dim respData As String = readStream.ReadToEnd()
Far away is close at hand in the images of elsewhere.
Anon.
April 30, 2014 at 5:45 am
Oh good grief. I'm going to go bury my head in the sand now...
Thanks, David.
April 30, 2014 at 5:51 am
Darnit. Still getting the same pictogram thing.
April 30, 2014 at 6:20 am
After
Dim readStream As New StreamReader(sr, Encoding.UTF8)
Add
MessageBox.Show(webResp.StatusCode.ToString)
MessageBox.Show(webResp.StatusDescription)
MessageBox.Show(String.Join(",", Array.ConvertAll(System.Text.Encoding.UTF8.GetBytes(readStream.ReadToEnd()), Function(x) x.ToString())))
This will give you three prompts, response status, response description and ascii codes for response buffer
Far away is close at hand in the images of elsewhere.
Anon.
May 2, 2014 at 11:48 am
So I fought and fought with this, ultimately going back to one of the sample scripts I was given. Here's where I ended up with (two read variables, one write variable):
Public Sub Main()
Dim FilePathName As String = Dts.Variables.Item("InboundPathAndName").Value
Dim url As System.Uri
Dim webReq As HttpWebRequest
Try
url = New System.Uri(Dts.Variables.Item("HttpPostURL").Value.ToString)
webReq = CType(WebRequest.Create(url), HttpWebRequest)
'POST Data
webReq.Method = "POST"
webReq.ContentType = "multipart/form-data; boundary=xyz"
webReq.Headers.Add("Cookie", "SMCHALLENGE=YES")
' convert username:password to basic 64 format and append to the HTTP
' header
webReq.Headers.Add("Authorization", _
Dts.Variables.Item("HttpAuth").Value.ToString)
webReq.KeepAlive = True
Dim dataBoundary As String = "------xyz"
Dim endingBoundary As Byte() = _
System.Text.Encoding.Default.GetBytes(vbCrLf + "------xyz--" + vbCrLf)
Dim ReadIn As FileStream
Dim tempStream As Stream
'create the information we need to send as part of post to let
'the ASPX page know about the file data
Dim DataString As StringBuilder = New StringBuilder
DataString.Append(dataBoundary + vbCrLf)
DataString.Append("Content-Disposition: form-data; name=" + _
"""" + "file" + """" + "; filename=" + """" + _
FilePathName.ToString + """" + vbCrLf)
'set the file type to octet-stream so we can handle any kind of Data()
DataString.Append("Content-Type: application/octet-stream" + _
vbCrLf + vbCrLf)
'open the file to post
ReadIn = New FileStream(FilePathName, FileMode.Open, FileAccess.Read)
ReadIn.Seek(0, SeekOrigin.Begin) 'move to the start of the file
Dim FileData(1024) As Byte 'read the file in 1k chunks
Dim DataRead As Integer = 0
tempStream = webReq.GetRequestStream()
'send the data about the file
Dim FileInfo As Byte() = _
System.Text.Encoding.Default.GetBytes(DataString.ToString())
tempStream.Write(FileInfo, 0, FileInfo.Length)
Do
DataRead = ReadIn.Read(FileData, 0, 1024)
If (DataRead > 0) Then 'we have data
tempStream.Write(FileData, 0, DataRead)
Array.Clear(FileData, 0, 1024) 'clear the array
End If
Loop While (DataRead > 0)
' send the closing boundry
tempStream.Write(endingBoundary, 0, endingBoundary.Length)
'close the stream
ReadIn.Close()
tempStream.Close()
'Get the response from the server
Dim webResp As HttpWebResponse = webReq.GetResponse()
Dim sr As New StreamReader(webResp.GetResponseStream())
'put the stream data in a string
Dim respData As String = sr.ReadToEnd()
sr.Close()
webResp.Close()
'MessageBox.Show(respData) 'Troubleshoot response
Dts.Variables.Item("WebsiteResponse").Value = respData.ToString
Catch webExcp As WebException
'If you reach this point, an exception has been caught.
'Write out the WebException message.
MessageBox.Show("An error occured." + webExcp.ToString())
Return
Catch myExcp As Exception
MessageBox.Show("A WebException has been caught." + myExcp.ToString())
Return
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
Just a note. When I tried getting rid of the 1 KB chunk process, the whole script blew up telling me the data was too large to read. So I added it back in. I'm not sure if the "too big" came from BIDS or the website, but I now know that I need it in this specific instance.
EDIT: Thanks everyone for all your assistance. It was very valuable.
May 14, 2014 at 4:33 am
So this job has been running for a few weeks and for the first week I had to make changes to the package to account for scenarios I hadn't considered. The script task has changed a little bit (not by much) because the MessageBox.Show method cannot be used with an automated job. When SQL Agent runs the package via a job, I get time out errors from the remote server. When I run the Script Task manually, though, the file gets sent without issue.
This is driving me nuts. The whole purpose behind automation was so no one had to manually upload the file to the vendor. Can anyone help me figure out what the problem is?
When I google the error, I get all sorts of results back regarding the Send Mail Task (which is NOT what I am using) and comments about using a Script Task instead (headdesk). Here's the error:
Error Message
An error occured.System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 74.200.96.4:443 at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) --- End of inner exception stack trace --- at System.Net.HttpWebRequest.GetRequestStream(TransportContext& context) at System.Net.HttpWebRequest.GetRequestStream() at ST_34df453e99e647559a2fcbd13a9d9831.vbproj.ScriptMain.Main()
And here's my updated VB script:
Public Sub Main()
Dim FilePathName As String = Dts.Variables.Item("InboundPathAndName").Value
Dim url As System.Uri
Dim webReq As HttpWebRequest
Try
url = New System.Uri(Dts.Variables.Item("HttpPostURL").Value.ToString)
webReq = CType(WebRequest.Create(url), HttpWebRequest)
'POST Data
webReq.Method = "POST"
webReq.ContentType = "multipart/form-data; boundary=xyz"
webReq.Headers.Add("Cookie", "SMCHALLENGE=YES")
' convert username:password to basic 64 format and append to the HTTP
' header
webReq.Headers.Add("Authorization", _
Dts.Variables.Item("HttpAuth").Value.ToString)
webReq.KeepAlive = True
Dim dataBoundary As String = "------xyz"
Dim endingBoundary As Byte() = _
System.Text.Encoding.Default.GetBytes(vbCrLf + "------xyz--" _
+ vbCrLf)
Dim ReadIn As FileStream
Dim tempStream As Stream
'create the information we need to send as part of post to let
'the ASPX page know about the file data
Dim DataString As StringBuilder = New StringBuilder
DataString.Append(dataBoundary + vbCrLf)
DataString.Append("Content-Disposition: form-data; name=" + """" _
+ "file" + """" + "; filename=" + """" + _
FilePathName.ToString + """" + vbCrLf)
'set the file type to octet-stream so we can handle any kind of Data()
DataString.Append("Content-Type: application/octet-stream" _
+ vbCrLf + vbCrLf)
'open the file to post
ReadIn = New FileStream(FilePathName, FileMode.Open, _
FileAccess.Read)
ReadIn.Seek(0, SeekOrigin.Begin) 'move to the start of the file
Dim FileData(1024) As Byte 'read the file in 1k chunks
Dim DataRead As Integer = 0
tempStream = webReq.GetRequestStream()
'send the data about the file
Dim FileInfo As Byte() = _
System.Text.Encoding.Default.GetBytes(DataString.ToString())
tempStream.Write(FileInfo, 0, FileInfo.Length)
Do
DataRead = ReadIn.Read(FileData, 0, 1024)
If (DataRead > 0) Then 'we have data
tempStream.Write(FileData, 0, DataRead)
Array.Clear(FileData, 0, 1024) 'clear the array
End If
Loop While (DataRead > 0)
' send the closing boundry
tempStream.Write(endingBoundary, 0, endingBoundary.Length)
'close the stream
ReadIn.Close()
tempStream.Close()
'Get the response from the server
Dim webResp As HttpWebResponse = webReq.GetResponse()
Dim sr As New StreamReader(webResp.GetResponseStream())
'put the stream data in a string
Dim respData As String = sr.ReadToEnd()
sr.Close()
webResp.Close()
'MessageBox.Show(respData) 'Troubleshoot response
Dts.Variables.Item("VendorResponse").Value = respData.ToString
Catch webExcp As WebException
'If you reach this point, an exception has been caught.
'Write out the WebException message.
'MessageBox.Show("An error occured." + webExcp.ToString())
Dts.Variables.Item("VendorResponse").Value = ("An error occured." _
+ webExcp.ToString())
Return
Catch myExcp As Exception
'MessageBox.Show("A WebException has been caught." + myExcp.ToString())
Dts.Variables.Item("VendorResponse").Value = _
(Dts.Variables.Item("VendorResponse").Value _
+ "A WebException has been caught." + myExcp.ToString())
Return
End Try
Dts.TaskResult = ScriptResults.Success
End Sub
Any thoughts?
May 14, 2014 at 5:13 am
So I found HttpWebRequest.Timeout and HttpWebRequest.ReadWriteTimeout. ReadWriteTimeout defaults at 300000, so I'm going to set it at twice that to see if it works (600000). I can't seem to find the default value for the Timeout property though.
Does anyone know the default value for the regular Timeout property?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply