SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


.NET SSIS Script Task issue


.NET SSIS Script Task issue

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14496 Visits: 8990
Brandie Tarvin (4/25/2014)
Trying Cadavre's script I get the following error:


A WebException has been caught.System.UriFormatException: Invalid URI: The URI scheme is not valid.
at System.Uri.CreateThis(String uri, Boolean dontEscape, UriKind uriKind)
at ST_34df453e99e647559a2fcbd13a9d9831.vbproj.ScriptMain.Main()


It could be what I'm reading into the variables, but the URL is valid and the authorization should be valid too.


So I was reading the authorization values from the wrong instance (DOH!). Got past this error and ran into another one:


A WebException has been caught.System.Net.ProtocolViolationException: Bytes to be written to the stream exceed the Content-Length bytes size specified.
at System.Net.ConnectStream.InternalWrite(Boolean async, Byte[] buffer, Int32 offset, Int32 size, AsyncCallback callback, Object state)
at System.NetConnectStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at
ST_34df453e99e647559a2fcbd13a9d9831.vbproj.ScriptMain.SendFileInformation(Stream tempStream, StringBuilder DataString, String FilePathName)
at ST_34df453e99e647559a2fcbd13a9d9831.vbproj.ScriptMain.Main()


At which point I find a question over on stackoverflow which indicates that this might be auto-set by the program and trying to manually set it will cause problems. So I'm going to experiment with that option.

If anyone has information to the contrary, though, could you please let me know?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14496 Visits: 8990
As I continue troubleshooting this I keep finding interesting things. Now I'm getting a message box back with this response (see attached picture).

Anyone know what this means?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Attachments
Website Response 4-28-2014.png (17 views, 6.00 KB)
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 9746
Brandie Tarvin (4/28/2014)
As I continue troubleshooting this I keep finding interesting things. Now I'm getting a message box back with this response (see attached picture).

Anyone know what this means?



It is the output from the line MessageBox.Show(respData) and is the response from the web request (GetResponseStream)

*Edited*
The data returned needs to be processed depending on what is returned.
Have you any details of what the service returns after a call?


Far away is close at hand in the images of elsewhere.

Anon.


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14496 Visits: 8990
David Burrows (4/28/2014)
Brandie Tarvin (4/28/2014)
As I continue troubleshooting this I keep finding interesting things. Now I'm getting a message box back with this response (see attached picture).

Anyone know what this means?



It is the output from the line MessageBox.Show(respData) and is the response from the web request (GetResponseStream)


Yes, it is, but I'm trying to figure out what this output means.


*Edited*
The data returned needs to be processed depending on what is returned.
Have you any details of what the service returns after a call?


That's all I'm getting back. Two little pictograms. I was hoping someone would recognize it and be able to tell me what I missed. The script task succeeded this time around, but didn't actually send the file to the vendor's testing site. So now I'm really lost. And frustrated.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 9746
Brandie Tarvin (4/28/2014)[hrThat's all I'm getting back. Two little pictograms.

No you are seeing an attempt to convert a binary array as a string and there may be more than 2 characters.
To see what is actually returned you need to convert the return stream to a byte array and output each byte as it's decimal value, this will enable you to interpret what is returned.


Far away is close at hand in the images of elsewhere.

Anon.


David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 9746
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.


David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 9746
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.


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14496 Visits: 8990
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9541 Visits: 9746
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.


Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14496 Visits: 8990
Oh good grief. I'm going to go bury my head in the sand now...

Thanks, David.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search