Export Image/Blob column using SSIS

Few days back, my team found that DB response is very poor. CPU Utilization was almost 100% due to image read write operation while showing images on .NET Application. We decided to move binary images to File System. So here is the fastest solution to generate image using SSIS package.

Step 1: Create New Project Integrated Services Project using BIDS (SQL Server Business Intelligence Development Studio)

Step 2: Add OLE DB Source Control in Data Flow tab

Step 3: Double click on OLE DB Source then Select from the existing or create a new OLE DB Connections

Step 4: After selecting the connection manager, select the data access mode and suitable input for extracting data, having binary data column and file path for it.

Step 5: Now drag Export Column control under OLE DB Source in Data Flow tab.  This control is to extract blob/images/documents stored in the database, or for saving them as files or to move them from point to point between data flows

Step 6: Specify Extract Column and File Path Column names.

Check Allow Append: Will create a new file if none exists, otherwise it will add the data to the end of an existing file

Check Force Truncate: This will overwrite if file already exists

Check Write Byte-order mark: Specify whether to write a byte-order mark (BOM) to the file. A BOM is only written if the data has the DT_NTEXT or DT_WSTR data type and is not appended to an existing data file.

Step 7: Execute using F5 or Debug Controls

Check the file created files in the file path we mentioned in the code J