In this article we will go through how we can extract images from a table in SQL Server and copy it on a folder. To achieve this we need to use SQL Server Integration Service. Let’s go over the process step by step.

Step 1: Create a new SSIS project in BIDS

Step 2: Drag and drop the Data Flow Task from the SSIS Toolbox to the design surface

Step 3: Go to the Data Flow tab. Drag and Drop the following on the design surface.

Step 4: Right click on the connection manager area and create a new OLEDB connection. We will create a connection for the AdventureWorks2012 database and the Production.Photo table.

Step 5. Use the following command to select the data from the table

declare @Ipath varchar(100)= 'C:\document\'
SELECT [ThumbNailPhoto],
@Ipath+[ThumbnailPhotoFileName] AS Imagepath
FROM [AdventureWorks2012].[Production].[ProductPhoto]

Step 6. Double click on the export column transformation editor and select the following values

Step 7. Build the package and execute. You should find all the images in the selected folder

Tagged: Export column tranformation editor, Extact Images, SQL Server 2012, SQL Server Integration Services, SSIS, SSIS Tasks