I recently encountered a project where I had to import Blob data from MySQL server, but I did not have access to the MySQL file system. After exhausting all my MySQL options like workbench, Toad and MySQL dumps, I decided to import data into MSSQL and extract the file from there. Below you will find the approach I used to make this happen.

First, we will extract data from the MySQL database and import it into a SQL Server. We can use an SSIS package to perform this tasks.

Start by creating a Data Flow tasks with the following components shown in the image. 

You can install the MySQL drivers (connectors) for the SSIS ADO NET source using this link: http://dev.mysql.com/downloads/connector/odbc/

We use a Data Conversion component to make sure the MySQL and SQL Server data sources match. You can get details of compatible data type mappings below:

• MySQL -> MSSQL data type mappings - https://www.mssqltips.com/sqlservertutorial/2203/mysql-to-sql-server-data-type-comparisons/
• SSIS -> MSSQL data type mappings - https://msdn.microsoft.com/en-us/library/ms141036.aspx

Follow the two links above and you will find that MySQL BLOBs map to VARBINARY in SQL Server and [DT_IMAGE] in SSIS. This is shown below.

We use a SQL Server destination and use the option ‘fast load’. (MySQL BLOB -> SQL Server VarBinary(800) datatype).

Blog data can be big and might take some time to complete. Once this job completes you will be able to see the Blob data in your SQL Server table, as shown below.

Next, we use BCP to extract these attachments to the Windows file share. BCP is run from a command line to check and make sure all the permissions are correct and you are able to extract files.

C:\Program Files\Microsoft SQL Server\120\Tools\Binn>bcp "SELECT top 1 content FROM [MAC].[dbo].[Attachment]" queryout "<file_share>\testing.msg" -fbcp.fmt –S<Server_name>-d<database> –U<user> -P<password>

Note that it is important to use a format file because BCP prefixes length of characters which can sometimes corrupt the document. This length prefix is useful when the field contains NULL data values. In our case we can change the value to 0 to avoid extra length characters. Refer the below link for more details about length prefix in bcp: https://msdn.microsoft.com/en-us/library/ms190779.aspx

This is an example of my format file.

We can manually run these commands if we need to exact a couple of documents. I had to extract over 1500 documents and I had to script it out. Below is a SP I create to perform this task.

  ----------------------------------------Script Begins------------------------------------------------------------------------------------------
  CREATE PROCEDURE [dbo].[ASP_Export_Attachments] 
  AS
  BEGIN
  SET NOCOUNT ON;
  DECLARE @ID bigint,@SQLcommand varchar(8000) ,@setname varchar(5000)
  --truncate table [dbo].[TransferredLogs]
  DECLARE db_cursor CURSOR LOCAL FOR  
  SELECT ID from mac.dbo.Attachments 
  OPEN db_cursor   
  FETCH NEXT FROM db_cursor INTO @ID  
  WHILE @@FETCH_STATUS = 0   
  BEGIN   
  --Setting filename from a different column 
   SELECT @setname = convert(varchar(50),ID)+'_'+Filename FROM MAC.dbo.Attachments where ID=@ID
  set @SQLcommand = 'bcp "SELECT content FROM mac.dbo.Attachments
    where ID='+convert(varchar(50),@ID)+'" queryout "\\server_name\BCP_Out\Attachments\'+@setname+'" 
    -f\\servername\bcp_out\bcp.fmt -Sserver_name.cable.comcast.com -dmac -Usqldts -P3ncrypt!'
  exec master.dbo.xp_cmdshell @SQLcommand
  --Inserting a successfully extracted column so that we that info in the table.
  --We won't have to extract these incase the Procedure timesout
  INSERT into [dbo].[TransferredLogs] SELECT ipimid FROM mac.dbo.Attachments  where ID=@ID 
         FETCH NEXT FROM db_cursor INTO @id   
  END   
  CLOSE db_cursor   
  END
  GO
  ------------------------------------End of Script---------------------------------------------------------------

Enjoy the extracted documents.

Summary

We were able to export BLOBs using a variety of tools provided by SQL Server such as SSIS, BCP and MSSQL Database. This method can be used to extract data from various sources by using SSIS connectors.