Umbraco (asp.net cms) to Drupal migration

Requirement:

- sql server management studio

- mdb viewer plus / MS access

- mdb2sqlite

- java opensdk-jdk

1. download mdb viewer plus

open mdb view plus , create a blank mdb file (test.mdb) without password.

2. open sql server management, cmd > ssms.exe    

3. right click on database you want to export > tasks > export data .

4. select data source > sql server native client 11.0 > fill in auth information and make sure connection is working.

5. click next > select destination > Microsoft access (microsoft jet database engine) > select test.mdb

6. select all tables and all data will be exported to test.mdb.

7. download https://code.google.com/p/mdb-sqlite/  >

wget https://mdb-sqlite.googlecode.com/files/mdb-sqlite-1.0.2.tar.bz2

tar -zxvf mdb-sqlite-1.0.2.tar.bz2

cd mdb-sqlite-1.0.2/

8. install opensdk > apt-get install openjdk-6-jdk

9.  upload your test.mdb to mdb-sqlite-1.0.2 directory.

10.  java -jar dist/mdb-sqlite.jar test.mdb test.sqlite

umbraco database structure

Example :  article content type migration:

<?
/**
 * @file
 * test_migrate.module
 */
/**
 * Sqlite db connection helper.
 */
function _test_migrate_sqlite_connect() {
  $other_database = array(
    'database' => '/home/test/test.sqlite',
    'host' => 'localhost',
    'driver' => 'sqlite',
  );
  $key = md5('testmigrate');
  Database::addConnectionInfo($key, 'default', $other_database);
  return $key;
}
/**
 * Implements hook_menu().
 */
function test_migrate_menu() {
  $items = array();
  $items['admin/test_migrate'] = array(
    'title' => 'test data migration',
    'page callback' => 'test_migrate_page_test',
    'access arguments' => array('administer site configuration'),
    'type' => MENU_NORMAL_ITEM,
  );
/**
 * Migrate entry.
 * Get all node data from umbraco
 */
function test_migrate_page_test() {
  $key = _test_migrate_sqlite_connect();
  db_set_active();
  db_set_active($key);
  // Dsm all data.
   $sql = db_query('
select * from cmsPropertyData as cpd
inner join (select * from cmsDataType as dt left join cmsPropertyType as cpt on cpt.dataTypeId = dt.nodeId) as data on data.id = cpd.propertytypeid left join
cmsDocument as cd on cd.versionId = cpd.versionId left join
cmsContentVersion as ccv on ccv.versionId = cd.versionId left join
umbracoNode as ubn on ubn.id = ccv.ContentId inner join
cmsContentXml as ccx on ccx.nodeId = ubn.id left join
cmsContent as cc on cc.nodeId = ubn.id left join
cmsContentType as cct on cct.nodeId = cc.contentType left join
cmsDocumentType as cdt on cdt.contentTypeNodeId = cct.nodeId left join
cmsTemplate as ct on ct.nodeId = cdt.templateNodeId
where
cd.newest = -1 and
order by cpd.contentNodeId asc
limit 0, 4000');
  db_set_active();
  foreach ($sql as $k => $v) {
    dsm($v); // dsm all whole record.
    $nids[$sql_v->nid]['xml'] = $sql_v->xml; // XML node from umbraco.
  }
  foreach($nids as $nid_k => $nid_v) {
    debug = 1; // Debug mode.
    // Actually we can grab all node data from xml
    $doc = new DOMDocument();
    $doc->recover = TRUE;
    $xml = $nid_v['xml'];
                                                                                                                    
    $doc->loadXML($xml);
    $xpath = new DOMXPath($doc);
    $article = $xpath->query("http://Article");
    $creatorId = $article->item(0)->getAttribute('creatorId');
    $sortOrder = $article->item(0)->getAttribute('sortOrder');
    $createDate = $article->item(0)->getAttribute('createDate');
    $updateDate = $article->item(0)->getAttribute('updateDate');
    $nodeName = $article->item(0)->getAttribute('nodeName');
    $writerName = $article->item(0)->getAttribute('writerName');
    $pageTitle = $xpath->query("http://Article/pageTitle");
    $metaDescription = $xpath->query("http://Article/metaDescription");
    $metaKeywords = $xpath->query("http://Article/metaKeywords");
    $articleTitle = $xpath->query("http://Article/articleTitle");
    $articleImage = $xpath->query("http://Article/articleImage");
    $bodyText = $xpath->query("http://Article/bodyText");
    $articleDate = $xpath->query("http://Article/articleDate");
    $author = $xpath->query("http://Article/author");
    $articleSynopsis = $xpath->query("http://Article/articleSynopsis");
    $articleTags = $xpath->query("http://Article/articleTags");
    $issueNumber = $xpath->query("http://Article/issueNumber");
    $node->title = $articleTitle->item(0)->nodeValue;
    $node->type = 'article';
    $node->uid = 1;
    $node->status = 1;
    $body = $bodyText->item(0)->nodeValue;
    $node->body['und'][0]['value'] = $body;
    $node->body['und'][0]['format'] = 'full_html';
    $p_w_picpathpath = $articleImage->item(0)->nodeValue;
    $img = _test_migrate_create_img($p_w_picpathpath, $debug);
    if (!empty($img)) {
      $node->field_p_w_picpath[LANGUAGE_NONE][] = (array) $img;
    }
    if (!$debug) {
      node_save($node);
    }
    else {
      dsm($node);
    }
  }
  return '';
}
/**
 * Create p_w_picpath by file path.
 * requirement: file entity module.
 *
 * @Return file obj.
 */
function _test_migrate_create_img($imgpath, $debug) {
  if (empty($imgpath)) {
    return 0;
  }
  $filename = str_replace('\\', '/', $imgpath);
  $file->uri = 'private:/' . $imgpath;
  $target_dir = 'public://articles' . dirname($filename);
  if (!file_exists(drupal_realpath($file->uri))) {
    return 0;
  }
  file_prepare_directory($target_dir, FILE_CREATE_DIRECTORY);
  $file->filemime = file_get_mimetype($file->uri);
  $file->status = 1;
  $file->uid = 1;
  $file->type = 'p_w_picpath';
  $file->display = 1;
  if (!$debug) {
    $file = file_copy($file, $target_dir, FILE_EXISTS_REPLACE);
    return $file;
  }
  else {
    dsm($file);
    return 0;
  }
}