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
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

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


Brandie Tarvin
Brandie Tarvin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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.
Cadavre
Cadavre
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

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


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
David Burrows
David Burrows
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

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


Brandie Tarvin
Brandie Tarvin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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.
Ville-Pekka Vahteala
Ville-Pekka Vahteala
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 3038
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).
Brandie Tarvin
Brandie Tarvin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20180 Visits: 9118
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 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