SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Importing Blobs from a MySQL Database and Export Them

By Abhishek Chaturvedi,

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:

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. 

 
Total article views: 440 | Views in the last 30 days: 2
 
Related Articles
FORUM

Migrate MYSQL into SQL Server 2008

MYSQL to SQL Server

ARTICLE

Linked server creation to MySQL Server

Here is a step by step procedure to create a linked server to MySQL from SQL Server.

BLOG

MySQL TinyInt treated as Boolean

When working with a MySQL database as a source you may experience some unexpected results in your da...

FORUM

Cursor

cursor

FORUM

MYSQL server service faild to start.

After reboot server mysql service not starting.

Tags
bcp    
blob    
extraction of blob data    
integration services (ssis)    
mssql    
mysql    
sqlserver    
varbinary    
 
Contribute