In this article I will show two ways of placing into files data that is stored in varbinary(max) columns. It is typical to have an application provide the interface through which to get files in and out of the database. However, what if you want to extract a bunch of those files to a folder? The first method uses FileTables; the second uses the Export Column transformation in SSIS.
Method 1: Using FileTables
New in SQL Server 2012, and building on FILESTREAM technology, FileTables allow manipulating files through both the file system and with TSQL in a manner transparent to the other. Previously with FILESTREAM, the files where stored in a regular Windows folder but they could only be operated on with a special API in order to maintain the synchronization with the database. For example, with FileTables you may add a file to a database table by dropping it in its designated Windows folder or by using TSQL DML statements. Either way, the file appears to be both in the folder and in the table but it is really physically stored only in the folder. To configure FileTables, please refer to this MSDN article.
The strategy is to move the varbinary(max) contents of the existing table to a FileTable, and then the corresponding files will appear in the folder associated with the FileTable.
The first step is to create the FileTable, like this:
CREATE TABLE MyFileTable AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'MyExportedFiles'
)
GO
The FILETABLE_DIRECTORY indicates the subfolder in which the files associated with MyFileTable will be stored. By the way, to quickly navigate to this subfolder, right-click on the newly-created table and select Explore FileTable Directory.
Now simply insert the data into the filetable. For example, there are two tables in the AdventureWorks2012 database that store varbinary(max) data. These two INSERT statements do the trick:
insert into [dbo].[MyFileTable] ([file_stream],[name])
select [Document], [FileName]
from [Production].[Document]
where [Document] is not null;
insert into [dbo].[MyFileTable] ([file_stream],[name])
select [LargePhoto], [LargePhotoFileName]
from [AdventureWorks2012].[Production].[ProductPhoto];
After this, the subfolder will the populated with about 110 files:
Method 2: Using the Export Column Transformation in SSIS
The second method is to export the data to files with an SSIS package, using the Export Column transformation. In this transformation, the basic setup is to indicate the column with the data to export and the column with the full name for the file.
I get the data from the two tables by using two OLE DB source transformations and then add a FilePath column to provide the path and name for the output file. The resulting data flow looks like this:
By the way, both methods work similarly well with image columns.
Thanks Adolfo for a great article!