Use Migrate Module to Format Incoming Data

This week's post will be brief and focus on a simple example of formatting some data on it's way through the Migrate module.

In my situation, I will be using Migrate for sync'ing some data from a MS SQL server db into a couple of Vocabularies in my Drupal environment. Unfortunately, my limitations on this db are such that I can't write a view to do this work for me, and I won't dare "clean" up some data for my needs.

So, I am sitting on a few rows of data that do not come across due to the use of a n-dash character which causes an error when attempting to save into the MySql db. My solution is to address this data issue in one of my Migration classes to clean the data on it's way in. I will be inserting the prepareRow function into my class as noted in the drupal docs:

http://drupal.org/node/1132582

I will be sure to highlight my big mistake because I overlooked a key detail in the article:

"The argument $row is a stdClass object containing the data as provided by the source"

When I first wrote my code, I was referencing the destination field name which never yielded any errors. I just didn't do anything. I didn't realize my folly until I was stepping through the code and I inspected the values directly. So remember, use the source column names! Last but not least, I expanded beyond just the n-dash character and replaced for a larger set of unfriendly characters:

 function prepareRow($row) {

   $row->Category = strtr(

     $row->Category,

     array(

       "\x80" => "e",  "\x81" => " ",    "\x82" => "'", "\x83" => 'f',

       "\x84" => '"',  "\x85" => "...",  "\x86" => "+", "\x87" => "#",

       "\x88" => "^",  "\x89" => "0/00", "\x8A" => "S", "\x8B" => "<",         "\x8C" => "OE", "\x8D" => " ",    "\x8E" => "Z", "\x8F" => " ",

       "\x90" => " ",  "\x91" => "`",    "\x92" => "'", "\x93" => '"',

       "\x94" => '"',  "\x95" => "*",    "\x96" => "-", "\x97" => "--",

       "\x98" => "~",  "\x99" => "(TM)", "\x9A" => "s", "\x9B" => ">",

       "\x9C" => "oe", "\x9D" => " ",    "\x9E" => "z", "\x9F" => "Y"));

 }

Hopefully the simplicity of this post doesn't disappoint, but this solution took me longer than it ever should have. I hope this post helps someone else from making the same mistake!

Using the Migrate module with MS SQL Server

This is officially the kickoff article for my blog. It has been something I wanted to start in the past, but have only found the motivation to do so recently. Now to the good stuff.

The topic of this article covers a project I'm involved in where we have to migrate data from a couple MS SQL Server DB's into our Drupal 7 install. We've decided to go with the Migrate module to knock this out. No big deal, right?

Upon a deeper dive, I've discovered in the documentation a subtle detail that Microsoft's sqlsrv module only functions on a Windows based Drupal install, which is not going to work for us. After tinkering around for a couple of days, I've come up with a rough workaround that I hope saves some time for others.

After some search, I was able to find an extremely helpful blog article that will gets the process started:

http://grover.open2space.com/content/use-php-and-pdo-connect-ms-sql-server

Read it carefully and follow the instructions up until the code examples, those won't totally help us, but I suppose they don't hurt.

At this point one additional modification will need to be made to freetds.conf. You should be able to find this in your /etc/freetds folder. When I started migrating the data I found that ntext, nvarchar(max) and varchar(max) were not compatible with freetds out of the box. So, we need to modify this conf in the global settings I add the following:

# TDS protocol version

tds version = 7.0

client charset = UTF-8

Now, let's go get the sqlsrv module, I'll do so via drush:

drush dl sqlsrv

Then copy the sqlsrv subfolder (not the module folder, but the child folder with the same name) to drupal/includes/database/ directory. This is where I'm going to be a bit lazy and retain the same name of Microsoft's module, so everything going forward will use the same name.

Next up, pop open your settings.php file to add your connection details:

 

$databases['default']['my_source_db'] = array(

 'driver' => 'sqlsrv',

 'database' => 'mydb',

 'username' => 'myuser',

 'password' => 'mypass',

 'host' => 'myhost',

 'prefix' => '',

);

Now, we have to update the database.inc file in the includes/database/sqlsrv/ folder to get this to work.

First change is in the $options array on line 52 to correctly apparent the port number for our OS:

 

   // Build the DSN.

   $options = array(

     'host=' . $connection_options['host'] . (!empty($connection_options['port']) ? ':' . $connection_options['port'] : ''),

     'dbname=' . $connection_options['database'],

   );

Then, directly following that, the first argument of the parent constructor needs to be modified to make use of dblib instead of sqlsrv per the previously linked article:

         

   // Launch the connection to the server.

   parent::__construct('dblib:' . implode(';', $options), $connection_options['username'], $connection_options['password'], array(

     /*PDO::SQLSRV_ATTR_DIRECT_QUERY => TRUE,*/

   ));

Don't forget to also comment out (or delete) that PDO::SQLSRV_... constant above.

One more change later in the file, around line 402 for me at this point. I comment out the section of code there that references the getColumnMeta function. I'll come back and update this if I come up with a better fix, but right now I don't have time for a real fix so binary columns are not going to bind correctly:

 

   /*

   $null = array();

   for ($i = 0; $i < $statement->columnCount(); $i++) {

     $meta = $statement->getColumnMeta($i);

     if ($meta['sqlsrv:decl_type'] == 'varbinary') {

       $null[$i] = NULL;

       $statement->bindColumn($i + 1, $null[$i], PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);

     }

   }

   */

This should complete your setup to begin using your Migrate module. There is one more point of interest here that was a gotcha for me. I got an error on the import as it couldn't locate the mapping tables on the source server b/c we are running against a different server (duh?). So, you will need to set the 'map_joinable' option in the MigrateSourceSQL constructor as such:

 

$this->source = new MigrateSourceSQL($query, $query->getFields(), null, array('map_joinable' => false));

Hopefully, someone else finds this helpful for them! Feel free to leave any comments, questions, or corrections.

Thanks for reading!