MigrateSourceMSSQL

Requirements

When migrating directly from a Microsoft SQL Server database, which is accessible to the destination server where you're running Migrate, use the MigrateSourceMSSQL source class. To use this class, you must have the mssql extension installed and enabled in PHP. For example, if you use yum in your development environment:

$ sudo yum install -y php-mssql.i686
$ sudo apachectl restart

Or if you use Debian/Ubuntu with apt-get:

$ sudo apt-get install php5-sybase

Usage

The constructor takes five arguments, all but the last required.

Configuration array

The first argument contains the connection info for your source database. The array elements are:

servername: The hostname or IP address of the database server.

username: The username to connect under.

password: That user's password.

database (optional): A database to select after connecting.

query

The SQL query to obtain the source data. It is important to understand that the query you use as your source must return a single row for each object to be created. While it may seem natural at first blush to join to, say, a "category" table which has multiple rows for a given content item, that will produce multiple rows in the source query - see the advanced topic Multiple source data rows for suggestions on dealing with such situations.

Be sure to include :criteria in your query's WHERE clause - this is necessary for the plugin to handle highwater marks or idlists. So, if you have a query like

SELECT * FROM accounts

modify it to

SELECT * FROM accounts WHERE :criteria

If your query already has a WHERE clause

SELECT * FROM accounts WHERE status=1

AND it with :criteria:

SELECT * FROM accounts WHERE status=1 AND :criteria

count_query

A SQL query to count the rows in the source data.

fields

An array describing the data returned by the query, keyed by the column name (case-sensitive) with a description of the field contents as the value.

options (optional)

An array of options affecting the behavior of the source class:

batch_size: The mssql extension fetches rows from a query in bulk rather than one-at-a-time. The MigrateSourceMSSQL class hides this from you by buffering the results and returning one row at a time, with a default batch size of 500, but you may adjust the batch size (the more memory you have, the larger the batch size you can use, with some improvement in performance).

The standard source options are also supported.

Example

In settings.php:

$conf['sql_server_connection'] = array(
  'servername' => 'sqlserver.example.com',
  'username' => 'readonlyuser',
  'password' => 'Gwa$hl45',
  'database' => 'prod.example',
);

In your migration constructor:

$query = "SELECT Id, Title, Body FROM MyContent";
$count_query = "SELECT COUNT(*) FROM MyContent";
$fields = array(
  'Id' => t('Unique Content ID'),
  'Title' => t('Content title'),
  'Body' => t('Content contents'),
);
$this->source = new MigrateSourceMSSQL(variable_get('sql_server_connection', array()), $query, $count_query, $fields);

FreeTDS configuration

If you see an error like:

Migration failed with source plugin exception: mssql_query(): message: Unicode data in a Unicode-only collation or [error]
ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier. (severity 16)

see Stack Overflow for information on properly configuring FreeTDS.