通过

SQL Server Migration Assistant (SSMA): Export Data Type Mappings

  • 项目

Using the SQL Server Migration Assistant (SSMA), Database Migrations to various Microsoft Database targets (SQL Server Versions on premise and cloud) are possible. One aspect of the migration is the mapping between source data types and target data types.

During an Oracle-2-SQL-Migration project, it was necessary to send the used Data Type Mapping to the customer.

The mapping is editable in SSMA on different levels, e.g. Default Project Settings, Project Level, Schema Level, Object Level and is separated into four types:

  • Arguments type mapping
  • Columns type mapping
  • Local variables type mapping
  • Return values type mapping

If possible, keep the mapping as simple as possible, not creating different mappings on different levels, e.g. BOOLEAN -> BIT on Project Level vs. BOOLEAN -> INT for a special Schema or even table or procedure.

There is currently no way to export the mapping within SSMA. In this blog post, I'll show how to export the Project Level Data Type Mapping to a tab separated file.

This blog post is based on SQL Server Migration Assistant for Oracle, Version 7.4. Other SSMA versions may work (slightly) differently.

Project Level Data Type Mapping

The Data Type Mapping at the Project Level is accessible in SSMA by the menu item "Tools" -> "Project settings", tab "Type Mapping":

A single mapping may depend on the source precision, resulting in different data types and/or precisions (Button "Edit..."):

The mapping is stored in the file project-container.mappings, located in the root folder of the SSMA Project. The file is a XML file.

Sample (shortened to show all four types):

Export to tab separated values file

Using PowerShell and XSLT, the mapping could be transformed to tab separated values and stored as file:

 $inputMapping = "project-container.mappings"
$inputXsl = "ProjectMappings2TSV.xsl"
$outputFile = "project-container.mappings.tsv"

$xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
$xslt.Load($inputXsl);
$xslt.Transform($inputMapping, $outputFile);

The resulting output file project-container.mappings.tsv looks like:

 Mapping Oracle SQL-Server
Arguments type mapping BFILE VARBINARY(MAX)
Arguments type mapping BINARY_DOUBLE FLOAT[53]
Arguments type mapping BINARY_FLOAT FLOAT[53]
Arguments type mapping BINARY_INTEGER INT
...
Columns type mapping BLOB VARBINARY(MAX)
Columns type mapping CHAR CHAR
...
Local variables type mapping CHAR CHAR
Local variables type mapping CHAR[*..8000] CHAR[*]
Local variables type mapping CHAR[8001..*] VARCHAR(MAX)
...
Return values type mapping INT INT
Return values type mapping INTEGER INT
Return values type mapping LONG VARCHAR(MAX)
...

ProjectMappings2TSV.xsl, used for export:

 <?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="https://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
    <xsl:output method="text" indent="no"/>

    <!-- Output: CSV with three columns, tab separated-->
    <xsl:template match="/">
      <xsl:text>Mapping Oracle SQL-Server
</xsl:text>
      <xsl:for-each select="container/schema">
        <xsl:variable name="MappingType" select="@type" />
        <xsl:for-each select="mapping">
          <xsl:value-of select="$MappingType" />
          <xsl:text> </xsl:text>
          <xsl:call-template name="PrintType">
            <xsl:with-param name="TypeNode" select="source" />
          </xsl:call-template>
          <xsl:text> </xsl:text>
          <xsl:call-template name="PrintType">
            <xsl:with-param name="TypeNode" select="target" />
          </xsl:call-template>
          <xsl:text>
</xsl:text>
        </xsl:for-each>
      </xsl:for-each>
    </xsl:template>

  <!-- Print Type-->
  <xsl:template name="PrintType">
    <xsl:param name="TypeNode" />
    <!-- either non-parametrized (without @) or parametrized-->
    <xsl:choose>
      <xsl:when test="contains($TypeNode/@type-id, '@')">
        <xsl:value-of select="substring-before($TypeNode/@type-id, '@')" />
        <xsl:for-each select="$TypeNode/*">
          <xsl:text>[</xsl:text>
          <xsl:choose>
            <!-- Value exists => use value-->
            <xsl:when test="@value">
              <xsl:value-of select="@value"/>
            </xsl:when>
            <!-- Start and/or End exists => Interval {Start or *}..{End or *}-->
            <xsl:when test="@start or @end">
              <xsl:choose>
                <xsl:when test="@start">
                  <xsl:value-of select="@start"/>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:text>*</xsl:text>                  
                </xsl:otherwise>
              </xsl:choose>
              <xsl:text>..</xsl:text>
              <xsl:choose>
                <xsl:when test="@end">
                  <xsl:value-of select="@end"/>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:text>*</xsl:text>
                </xsl:otherwise>
              </xsl:choose>
            </xsl:when>
            <!-- No interval and also no value => * -->
            <xsl:otherwise>
              <xsl:text>*</xsl:text>
            </xsl:otherwise>
          </xsl:choose>
          <xsl:text>]</xsl:text>
        </xsl:for-each>
      </xsl:when>
      <!-- Non-parametrized => use type-id unmodified-->
      <xsl:otherwise>
        <xsl:value-of select="$TypeNode/@type-id" />
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

Further Reading