Last updated January 21, 2013. Created by mikeryan on May 9, 2011.
Log in to edit this page.
Requirements
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 restartUsage
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.
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:
<?php
$conf['sql_server_connection'] = array(
'servername' => 'sqlserver.example.com',
'username' => 'readonlyuser',
'password' => 'Gwa$hl45',
'database' => 'prod.example',
);
?>In your migration constructor:
<?php
$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);
?>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 FreeDTS.
Comments
Help
I'd really love to read some documentation here... Yes, I will read the source code, but nice documentation still makes things much easier.
Hello
Hello i am using module migrate to fetch data from a Mssql Server. I am using odbc_connect() to fetch data from Mssql server. But it doesn't seem to work when i write code in Mssql.inc Has anyone same problem? Anyone know how i solve this?
Thanks