[thelist] Binary from table column to DTS (SQL Server 2k)

Michael Pack michaelpack at wvdhhr.org
Tue Jul 1 09:05:27 CDT 2008

Howdy folks, I'm attempting a new feat (in my life anyway). I want to
take a txt or csv file that was uploaded and saved as binary in an image
field then retrieve it and use it in a DTS package that passes it to a
stored procedure that performs the data allocation into my inventory
table. All is written and working except for my binary grab and pass
from the DB.

I've succesffully implemented this in asp.net VB but am not up to par
on DTS. Any ideas/suggestions. Below is the code I've used to retrieve
and send to a user in a browser. I would like to work off of this,
basically instead of sending it to a download box, send it to the DTS

        Dim SQL As String = "SELECT CSV_File FROM invFile"
        Dim dbConnection As SqlConnection = New
        Dim dbCommand As SqlCommand = New SqlCommand
        dbCommand.CommandTimeout = 360
        dbCommand.CommandText = SQL
        dbCommand.Connection = dbConnection

        Dim drData As SqlDataReader = dbCommand.ExecuteReader()
        While drData.Read
            Dim fileName As String = drData("filename").ToString

            'Get rid of any spaces in the uploaded file's stored
            fileName = Regex.Replace(fileName, " ", "")

            If (fileName.EndsWith(".txt") OrElse
fileName.EndsWith(".csv")) Then
                Response.ContentType = "text/plain"
            End If

            'forces download dialog box
            Response.AppendHeader("Content-Disposition", ("attachment;
filename=" + fileName))

            'need to convert object to binary array as can not pass
reader["CSV_File"] into response.binarywrite() function directly
            Dim ms As IO.MemoryStream = New IO.MemoryStream
            Dim bf As
Runtime.Serialization.Formatters.Binary.BinaryFormatter = New
            bf.Serialize(ms, drData(" CSV_File"))

            'below function only accepts binary array whereas
reader["CSV_File"] was object, so converted to byte array


        End While

Many thanks in advance.

More information about the thelist mailing list