Exporting Binary Files To The File System

In my last post I demonstrated how to use SSIS to load binary files into a SQL Server 2005 VARBINARY(MAX) column, http://jahaines.blogspot.com/2009/10/ssis-importing-binary-files-into.html.  This post will focus on recreating the binary documents on the file system.  I will be using a combination of TSQL and the BCP utility to perform the export, http://msdn.microsoft.com/en-us/library/ms162802.aspx.  I will be using the same table and data from the last post.  I will start by creating the TSQL to dynamically create a BCP command.

Below is the stored procedure I will use to export the data.  You will see that I have decided to use a cursor to process all documents.  The procedure also accepts a DocID, which limits the export to a single document.  A cursor is fine here because we are limited to executing a single BCP command; however, you could create an SSIS package that executes the stored procedure across multiple streams, if you need parallel processing.

CREATE PROCEDURE usp_ExportBinaryFiles(

    @DocID INT = NULL,

    @OutputFilePath VARCHAR(500) = 'C:\'

)

AS 

BEGIN

DECLARE @sql VARCHAR(8000)

IF @DocID IS NULL --Open Cursor to export all images

BEGIN

    DECLARE curExportBinaryDocs CURSOR FAST_FORWARD FOR

    SELECT 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 

        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 

        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'

    FROM dbo.Documents

    OPEN curExportBinaryDocs

    FETCH NEXT FROM curExportBinaryDocs INTO @sql

    WHILE @@FETCH_STATUS = 0

        BEGIN

            --PRINT @sql

            EXEC xp_cmdshell @sql,NO_OUTPUT

            FETCH NEXT FROM curExportBinaryDocs INTO @sql

        END

    CLOSE curExportBinaryDocs

    DEALLOCATE curExportBinaryDocs

END

ELSE --Export a single image

BEGIN

    SELECT @sql = 'BCP "SELECT Doc FROM [tempdb].[dbo].[Documents] WHERE DocId =' 

        + CAST(DocId AS VARCHAR(5)) + '" queryout ' + @OutputFilePath 

        + DocName + '.' + DocType + ' -S A70195\Dev -T -fC:\Documents\Documents.fmt'

    FROM dbo.Documents

    WHERE DocID = @DocID

    --PRINT @sql

    EXEC xp_cmdshell @sql,NO_OUTPUT

END

END

GO

The above stored procedure dynamically builds a BCP command.  Currently the stored procedure has two main parameters, DocID and OutputFilePath.  DocID is used when you want to export a single row, if no value is supplied the stored procedure will export all documents.  The outputFilePath is the directory where the files will be exported.  You will note that I have hard coded my server name and format file path.  You can add additional parameters if you need these attributes to be dynamic.  The next item on my list is to actually create the format file.  Create a .fmt file somewhere on your file system.   I put mine in the same folder as my other documents.  Copy the code below into the format file.

9.0   

1   

1       SQLBINARY     0       0       ""   1     Doc                                       ""  

You may be asking yourself, why the format file looks skimpy, or is lacking content.  The format file is lacking content because it only has what we need.  All we need in the format file is the VARBINARY(MAX) column and its data type. You can find more info on format files here, http://msdn.microsoft.com/en-us/library/ms191516.aspx.

Once the format file is in place, we can execute our stored procedure to process all documents.

EXEC dbo.usp_ExportBinaryFiles @OutputFilePath = 'C:\Documents\BCP_Out\'

That’s it!  It is that easy to output VARBINARY data onto the file system.  SQL Server 2005 has a slue of tools that make working with binary data very simplistic.  I hope that you have learned something new. Please stay tuned, as I plan to focus more on TSQL concepts and performance considerations.

Until next time happy coding.

SSIS – Importing Binary Files Into A VARBINARY(MAX) Column

Have you every had the need to import images, Word documents, Excel documents, or any other type of file into a SQL Server table?  If yes, you are in luck.  SQL Server 2005 gives us a powerful and scalable method to import binary files or documents, into a relational table.  I will be using the term binary file and document interchangeably throughout this post to describe a file on the file system.  I will be utilizing SSIS and the VARBINAARY(MAX) data type to import the document. Let us start by creating the sample DDL.  In this example, I will need a staging table and a table to house our binary data. 

USE [tempdb]

GO

IF OBJECT_ID('tempdb.dbo.[Doc_Stage]') IS NOT NULL

BEGIN

    DROP TABLE [dbo].[Doc_Stage];

END

GO

CREATE TABLE [dbo].[Doc_Stage](

DocId INT IDENTITY(1,1) PRIMARY KEY,

DocName VARCHAR(50) NOT NULL,

DocPath VARCHAR(1000) NOT NULL,

DocType VARCHAR(4) NOT NULL

);

GO

IF OBJECT_ID('tempdb.dbo.[Documents]') IS NOT NULL

BEGIN

    DROP TABLE [dbo].[Documents];

END

GO

CREATE TABLE [dbo].[Documents](

DocId INT IDENTITY(1,1) PRIMARY KEY,

DocName VARCHAR(50) NOT NULL,

Doc VARBINARY(MAX) NULL,

DocType VARCHAR(4)

);

GO

Next, I need to create the SSIS package.  The first step is to add an Execute SQL Task to the designer canvas.  Configure the Execute SQL Task, as shown below.

Note: You will need to create the source connection to the database where you created the DDL

 

The SQL Statement that I used is below.

TRUNCATE TABLE [dbo].[Doc_Stage] ;

Next I will create a variable called FilePath that is of the string data type.  Now I am ready to add a ForEach Loop Container to the canvas.  Add the container to the canvas and configure it as shown below.

Note: I am grabbing all file types. If you only want a specific type, change the File to include the extension that you want.  E.g. *.jpg

Next, I will have to create another Execute SQL Task, but this time, I have to drag the task into the ForEach Loop Container.  Configure the task as shown below.

The SQL Statement is presented below:

INSERT INTO [dbo].[Doc_Stage](DocName,DocPath,DocType) VALUES ('DocName','DocPath','jpg');

Now for the tricky part.  In this step, I have to build an expression to dynamically build an insert statement.  The insert will capture the document name, path, and type.  Click the expressions tab and create an expression, on the property SQLStatementSource (near the bottom).  Below is the code for the expression.

"INSERT INTO [dbo].[Doc_Stage](DocName,DocPath,DocType) VALUES ('" +

REPLACE(RIGHT(@[User::FilePath],FINDSTRING(Reverse(@[User::FilePath] ) ,"\\", 1)-1),RIGHT(@[User::FilePath],4),"")

+ "','" + @[User::FilePath] 

+ "','" + RIGHT( @[User::FilePath] ,FINDSTRING( REVERSE(@[User::FilePath] ),".",1)-1)

+ "');"

 

With that out of the way, we can press on.  The last task I will need is a Data Flow Task.  Drag the data flow task to the canvas.  Here is what my canvas looks like at present:

Open the Data Flow task and drag an OLE DB Source to the canvas and configure it as shown below.

 

Make sure to click the Columns tab to set the column mappings.  Next, I will drag a Import Column Transformation to the canvas and configure it as shown below.

Note: Make sure to take note of the output column’s LineageID.  You will need to take this ID and add it to the Input Column’s FileDataColumnID.

We are almost there!!! The last step is to add the OLE DB Destination.  I will add the OLE DB Destination and configure it as shown below. Do not forget to click the Mappings tab to map the columns.

That’s it!!!! Click the debug button and all of the components should light up green. I have successfully implemented a document library solution that allows the insertion of any document type into SQL Server 2005.  Stay tuned because I will show you how to export the images to the file system using BCP and TSQL.

Until next time happy coding.