How to Export BLOB data to Files in SSIS

Overview

This topic will cover how to export data from BLOB fields in SQL Server and save those files out to the filesystem. This is accomplished within VB.Net code, but could just as easily be ported over to C#.

DBServer: SQL Server 2008 SP2
Development Environment: Visual Studio 2008

Package Setup

  1. Add an Execute SQL Task that retrieves the BLOB information from the Database
    • ResultSet: Full result set
    • Parameter Mapping: Add any necessary parameters for your stored procedure
    • Result Set
      • Result Name: 0 (zero)
      • Variable Name: Doesn’t matter, so long as the variable is of type System.Object
  2. Connect the Execute SQL Task to a For Each Loop Container (FELC)
    1. Collection: Foreach ADO Enumerator
    2. ADO object source variable: Result Set Variable Name from Step 1
    3. Enumeration mode: Rows in the first table
    4. Variable Mapping: Map out the columns from the Result Set to Package Variables (Index is 0-based)
      1. NOTE: Be sure to setup the BLOB output variable as a System.Object
  3. Add a Script Task to output the BLOB information to the file system

Script Code (VB.Net)


Public Sub SaveMemoryStream(ByVal buffer2 As Array, ByVal FileName As String, ByVal ms As MemoryStream)
        Dim outStream As FileStream

        outStream = File.OpenWrite(FileName)
        outStream.Write(buffer2, 0, ms.Position)
        outStream.Flush()
        outStream.Close()
    End Sub

    Public Sub Main()
        Dim Folder_Path As String
        Dim File_Name As String
        Dim s_File_Name As String
        Dim buffer() As Byte
        Dim oStream As System.IO.MemoryStream = Nothing
        Dim oFileStream As System.IO.FileStream = Nothing
        Dim buffer2() As Byte

        Folder_Path = Dts.Variables("Working_Directory").Value.ToString() + "\"
        File_Name = Dts.Variables("File_Path").Value.ToString()

        s_File_Name = Folder_Path & File_Name

        Try
            buffer = CType(Dts.Variables("Blob").Value, Byte())
            oStream = New System.IO.MemoryStream(buffer)
            oStream.Write(buffer, 0, buffer.Length)

            buffer2 = oStream.ToArray()
            SaveMemoryStream(buffer2, s_File_Name, oStream)

            'Close the stream
            oStream.Flush()

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception
            Dts.Events.FireError(0, "Create Temp Image", ex.Message, "", 0)
            Dts.TaskResult = ScriptResults.Failure

        Finally
            If Not oStream Is Nothing Then
                oStream.Close()
            End If

            oStream = Nothing

            If Not oFileStream Is Nothing Then
                oFileStream.Close()
            End If

            oFileStream = Nothing
        End Try
        '
        Dts.TaskResult = ScriptResults.Success
    End Sub

Leave a Reply