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)

[code language=”vb”]

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
[/code]