When migrating directly from Microsoft SQL Server database, and 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
The constructor takes five arguments, all but the last required.
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.
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.
A SQL query to count the rows in the source data.
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.
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.
$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', ''), $query, $count_query, $fields);
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 FreeDTS.