Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

.NET SSIS Script Task issue Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 5:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:45 AM
Points: 5,572, Visits: 6,356
Darnit. Still getting the same pictogram thing.

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1566330
Posted Wednesday, April 30, 2014 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
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.

Post #1566345
Posted Friday, May 2, 2014 11:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:45 AM
Points: 5,572, Visits: 6,356
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1567138
Posted Wednesday, May 14, 2014 4:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:45 AM
Points: 5,572, Visits: 6,356
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1570715
Posted Wednesday, May 14, 2014 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 10:45 AM
Points: 5,572, Visits: 6,356
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?


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #1570752
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse