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.