question

hey Vertigo...not sure if this will help at all. I've created an 2005 SSIS package with a VB.net script to export images to file. Here is the script first, Option Strict Off Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Imports System.io Imports System.Drawing.Imaging Imports System.Drawing Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) ' ' Add your code here ' ' Writes the BLOB to a file (*.bmp). Dim bw As BinaryWriter ' Streams the BLOB to the FileStream object. Dim bufferSize As Integer = 100 ' Size of the BLOB buffer. Dim outbyte As Byte() = New Byte(bufferSize - 1) {} ' The BLOB byte[] buffer to be filled by GetBytes. Dim retval As Long ' The bytes returned from GetBytes. Dim startIndex As Long = 0 ' The starting position in the BLOB output. Dim emp_id As String = "" ' The employee id to use in the file name. ' Open the connection and read data into the DataReader. emp_id = Row.ID Dim myByte() As Byte myByte = DirectCast(Row.PICTURE.GetBlobData(0, Row.LENGTH), Object) Dim intArraySize As Integer Dim ms As New MemoryStream '("d:\test\" & emp_id & ".jpg", FileMode.OpenOrCreate, FileAccess.Write) ms.Write(myByte, 0, Row.LENGTH) Dim eps As EncoderParameters = New EncoderParameters(1) eps.Param(0) = New EncoderParameter(Encoder.Quality, 40) Dim ici As ImageCodecInfo = GetEncoderInfo("image/jpeg") Dim picImage As Image Try picImage = Image.FromStream(ms) '(fs.Write(myByte, 0, Row.LENGTH)) Catch ex As Exception msgbox(ex.Message) End Try Try picImage.Save("\\server\share\" & emp_id & ".jpg", ici, eps) 'picImage.Save("d:\test\" & emp_id & ".jpg", ici, eps) Catch ex As Exception End Try End Sub Private Function GetEncoderInfo(ByVal mimeType As String) As ImageCodecInfo Dim j As Integer Dim encoders As ImageCodecInfo() encoders = ImageCodecInfo.GetImageEncoders() For j = 0 To encoders.Length If encoders(j).MimeType = mimeType Then Return encoders(j) End If Next j Return Nothing End Function End Class Then in the package, just pass the image and the length of the image into the script. The script is a destination, not a transformation.

On SQL Server 2005+ you can create a CLR function for writing the BLOB data back to files. Below is a code which creates a very simple scalar CLR function which handles the writing. using System; using System.Collections.Generic; using System.Text; using System.Data.SqlTypes; using System.IO; using Microsoft.SqlServer.Server; public class BlobExport { [SqlFunction(IsDeterministic = true)] public static int ExportBlobField(SqlBytes inputData, SqlString destinationPath) { using (FileStream dataStream = new FileStream(destinationPath.Value, FileMode.Create)) { dataStream.Write(inputData.Value, 0, (int)inputData.Length); dataStream.Close(); } return 1; } } For example using a C# Express you crate a new Class Library. Put the above code as the source of any class. Then compile it and register it as Assembly in your database. The assembly needs External Access permission set and therefore your database have to be set as TRUSTWORTHY ALTER DATABASE [YourDB] SET TRUSTWORTHY ON Also CLR needs to be enabled on the server: sp_configure 'clr enabled', 1 RECONFIGURE WITH OVERRIDE Once CLR is enabled and TRUSWORTHY SET, you can register your assembly: CREATE ASSEMBLY [PPSQLCLRSafe] AUTHORIZATION [dbo] FROM 'PathToYourAssembly.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS After that you can register the Scalar function: CREATE FUNCTION [dbo].[WriteBlobToFile]( @inputdata varbinary(max), @destinationPath nvarchar(255) ) RETURNS [int] WITH EXECUTE AS CALLER AS EXTERNAL NAME [YourAssembly].[BlobExport].[ExportBlobField] Once registered, you can use a simple selects to write the blob data to the files: SELECT [ID] ,[FileName] ,[fileData] ,dbo.WriteBlobToFile([fileData], 'C:\OutputDirectory\' + [FileName]) FROM [TestDB].[dbo].[TestTable]