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 Friday, April 25, 2014 7:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
I am still a rookie when it comes to Visual Basic. I've been giving VB code to read a file into a FileStream object and send it via HTTPS to a vendor server. Unfortunately, as part of the header I need to add the content length. I've found the following article on how to check a file length, but it doesn't seem to give me the ability to read a file into an object. I'm trying to figure out how to marry the two bits of code together.

BTW, I'm doing this in a SSIS Script Task and reading in the file path and authorization and web address from package level variables.

Any thoughts on how I can get the content length before I'm sending the file?


Public Sub Main()
'
' Add your code here
'

Dim FilePathName As String = Dts.Variables.Item("InboundPathAndName").Value
Dim url As System.Uri
Dim webReq As HttpWebRequest
Dim myCred As NetworkCredential --'I don't see where this is used in the code.
Dim MyCrendentialCache As New CredentialCache --'I don't see where this is used in the code either

Try
url = New System.Uri(Dts.Variables.Item("HttpPostURL").Value)
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)

'Content length needs to go here
webReq.ContentType = "multipart/form-data; boundary=------xyz"
webReq.Headers.Add("Accept-Encoding", "gzip, deflate")

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 + """" + 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)

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


I might be misreading the code, but it seems to send the file directly to the vendor site as it's reading the file. Am I mistaken about that?


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 #1565096
Posted Friday, April 25, 2014 8:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 7,013, Visits: 7,179
Dim f As New System.IO.FileInfo(FilePathName)
f.Length 'Size in bytes of the file


Can the file be large? Just curious why reading 1k chunks!



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

Anon.

Post #1565111
Posted Friday, April 25, 2014 8:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
As far as I can tell, it's reading in chunks because that's how it transmits. It's part of the multiform / data-part thing. But again, I got this code from a vendor and am not that good with VB yet.

Thanks for the code chunk. I'll try it out.


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 #1565116
Posted Friday, April 25, 2014 8:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,386, Visits: 7,611
From a real quick look, bearing in mind that I'm not a VB developer: -

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Text

<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum

Public Sub Main()
Dim FilePathName As String = Dts.Variables.Item("InboundPathAndName").Value

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("------xyz" + 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 = 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)

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)

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=------xyz"
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 + "------xyz--" + vbCrLf)

' send the closing boundry
tempStream.Write(endingBoundary, 0, endingBoundary.Length)

'close the stream
ReadIn.Close()
tempStream.Close()
End Sub

End Class

--edit--
I didn't refresh the page before I posted, so didn't spot that David Burrows had already answered. The majority of the changes I've made are formatting so that I could get my head around what was going on.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1565125
Posted Friday, April 25, 2014 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:26 AM
Points: 7,013, Visits: 7,179
Brandie Tarvin (4/25/2014)
As far as I can tell, it's reading in chunks because that's how it transmits. It's part of the multiform / data-part thing.

That is a misconception, the multipart refers to multiple data separated by boundaries not how many chunks you write. Besides the code builds a single stream and outputs that stream to the request in one go.



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

Anon.

Post #1565133
Posted Friday, April 25, 2014 10:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
The misconception would be mine, then. This is sample code supplied to me by the vendor receiving the file. It seems pretty generic and the person who gave it to me is not one of the developers. He's just a point of contact (the gatekeeper, if you will).

Which means I have no idea why they have it reading in 1 KB chunks.

I presume from what you've said in a previous post that it's not really necessary?


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 #1565176
Posted Friday, April 25, 2014 11:26 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 92, Visits: 2,549
Brandie Tarvin (4/25/2014)
The misconception would be mine, then. This is sample code supplied to me by the vendor receiving the file. It seems pretty generic and the person who gave it to me is not one of the developers. He's just a point of contact (the gatekeeper, if you will).

Which means I have no idea why they have it reading in 1 KB chunks.

I presume from what you've said in a previous post that it's not really necessary?


If they are trying to optimize TCP stream since MTU for Ethernet is 1500 bytes?
We normally read and write in 1024 byte chunks and for each iteration we send notification to listeners (UI).
Post #1565196
Posted Friday, April 25, 2014 11:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
Cadavre (4/25/2014)
I didn't refresh the page before I posted, so didn't spot that David Burrows had already answered. The majority of the changes I've made are formatting so that I could get my head around what was going on.


I'll still look at your code.

Sorry about using QUOTE for the prior code. I just couldn't get the comments to work correctly. The CODE block kept turning lines of code into character formating color.


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 #1565198
Posted Friday, April 25, 2014 11:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
Ville-Pekka Vahteala (4/25/2014)
Brandie Tarvin (4/25/2014)
The misconception would be mine, then. This is sample code supplied to me by the vendor receiving the file. It seems pretty generic and the person who gave it to me is not one of the developers. He's just a point of contact (the gatekeeper, if you will).

Which means I have no idea why they have it reading in 1 KB chunks.

I presume from what you've said in a previous post that it's not really necessary?


If they are trying to optimize TCP stream since MTU for Ethernet is 1500 bytes?
We normally read and write in 1024 byte chunks and for each iteration we send notification to listeners (UI).


Hmmm. Now there's a thought.


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 #1565200
Posted Friday, April 25, 2014 12:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:12 PM
Points: 5,615, Visits: 6,413
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.

Looking at David's script, I'm wondering... Is the length of a regular text file the same in bytes as the length of a MIME encoded file?


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 #1565222
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse