Executing the SSMA console (AccessToSQL)

Microsoft provides you with a robust set of script file commands and command line options to execute and control SQL Server Migration Assistant (SSMA) activities. The ensuing sections detail the same.

Project script file commands

The Project commands handle creating projects, opening, saving, and exiting projects.

create-new-project

Creates a new SSMA project.

Syntax example

<create-new-project
  project-folder="<project-folder>"
  project-name="<project-name>"
  overwrite-if-exists="<true | false>"
  project-type="<project-type>"
/>

open-project

Opens an existing project.

Syntax example

<open-project
  project-folder="<project-folder>"
  project-name="<project-name>"
/>

save-project

Saves the migration project.

Syntax example

<save-project/>

close-project

Closes the migration project.

Syntax example

<close-project
  if-modified="<save/error/ignore>"   (optional)
/>

Attribute if-modified is optional, ignore by default.

Database connection script file commands

The Database Connection commands help connect to the database.

The Browse feature of the UI isn't supported in console.

The windows-authentication and port parameters aren't applicable when connecting to SQL Azure.

For more information, see Creating script files.

connect-source-database

Performs connection to the source database and loads high-level metadata of the source database but not all of the metadata.

If the connection to the source can't be established, an error is generated and the console application stops further execution

The server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file.

Syntax example

<connect-source-database server="<server-unique-name>"/>

load-access-database

Used to load access database files.

Syntax example

<load-access-database  database-file="<Access-database>"/>

Or:

<load-access-database>
  <access-database database-file="<Access-database1>"/>
  <access-database database-file="<Access-database2>"/>
</load-access-database>

force-load source or target database

Loads the source metadata. Useful for working on migration project offline.

If the connection to the source or target can't be established, an error is generated and the console application stops further execution.

Requires one or more metabase nodes as command line parameter.

Syntax example

<force-load
  object-name="<object-name>"
  metabase="<source/target>"/>

Or:

<force-load>
  <metabase-object object-name="<object-name>"/>
</force-load>

reconnect-source-database

Reconnects to the source database but doesn't load any metadata unlike the connect-source-database command.

If (re)connection with the source can't be established, an error is generated and the console application stops further execution.

The server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file.

Syntax example

<reconnect-source-database server="<server-unique-name>"/>

connect-target-database

Connects to the target SQL Server or SQL Azure Database and loads high-level metadata of the target database but not the metadata entirely.

If the connection to the target can't be established, an error is generated and the console application stops further execution.

The server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file.

Syntax example

<connect-target-database  server="<server-unique-name>"/>

reconnect-target-database

Reconnects to the target database but doesn't load any metadata, unlike the connect-target-database command.

If (re)connection to the target can't be established, an error is generated and the console application stops further execution.

The server definition is retrieved from the name attribute defined for each connection in the server section of the server connection file or the script file.

Syntax example

<reconnect-target-database server="<server-unique-name>"/>

Report script file commands

The Report commands generate reports on the performance of various SSMA Console activities

generate-assessment-report

Generates assessment reports on the source database.

If the source database connection isn't performed before executing this command, an error is generated and the console application exits.

Failure to connect to the source database server during the command execution, also results in ending the console application.

Script

• assessment-report-folder: Specifies folder where the assessment report can be stored. (optional attribute)

• object-name: Specifies the objects considered for assessment report generation (It can have individual object names or a group object name).

• object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type is "category").

• assessment-report-overwrite: Specifies whether to overwrite the assessment report folder if it already exists.

  Default value: false. (optional attribute)

• write-summary-report-to: Specifies the path where the report is generated.

  If only the folder path is mentioned, then file by name AssessmentReport<n>.xml is created. (optional attribute)

  Report creation has two further subcategories:

  • report-errors (="true/false", with default as "false" (optional attributes))
  • verbose (="true/false", with default as "false" (optional attributes))

Syntax example

<generate-assessment-report
  object-name="ssma.Procedures"
  object-type="category"
  write-summary-report-to="<file>"             (optional)
  verbose="<true/false>"                       (optional)
  report-errors="<true/false>"                 (optional)
  conversion-report-folder="<folder>"          (optional)
  conversion-report-overwrite="<true/false>"   (optional)
/>

Or:

<generate-assessment-report
  conversion-report-folder="<folder>"            (optional)
  conversion-report-overwrite="<true/false>"     (optional)
>
    <metabase-object object-name="ssma.Procedures"
        object-type="category"/>
</generate-assessment-report>

Migration script file commands

The Migration commands convert the target database schema to the source schema and migrates data to the target server.

The default console output setting for the migration commands is 'Full' output report with no detailed error reporting: Only summary at the source object tree root node.

convert-schema

Performs schema conversion from source to the target schema.

If the source or target database connection isn't performed before executing this command, or the connection to the source or target database server fails during the command execution, an error is generated and the console application exits.

Script

• conversion-report-folder: Specifies folder where the assessment report can be stored. (optional attribute)

• object-name: Specifies the source objects considered for converting schema (It can have individual object names or a group object name).

• object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type is "category").

• conversion-report-overwrite: Specifies whether to overwrite the assessment report folder if it already exists.

  Default value: false. (optional attribute)

• write-summary-report-to: Specifies the path where the report is generated.

  If only the folder path is mentioned, then file by name SchemaConversionReport<n>.xml is created. (optional attribute)

  Report creation has two further subcategories:

  • report-errors (="true/false", with default as "false" (optional attributes))
  • verbose (="true/false", with default as "false" (optional attributes))

Syntax example

<convert-schema
  object-name="ssma.Procedures"
  object-type="category"
  write-summary-report-to="<filepath/folder>"     (optional)
  verbose="<true/false>"                          (optional)
  report-errors="<true/false>"                    (optional)
  conversion-report-folder="<folder>"             (optional)
  conversion-report-overwrite="<true/false>"      (optional)
/>

Or:

<convert-schema
  conversion-report-folder="<folder>"         (optional)
  conversion-report-overwrite="<true/false>"  (optional)
>
  <metabase-object object-name="ssma.Procedures"
    object-type="category"/>
</convert-schema>

migrate-data

Migrates the source data to the target.

Script

• object-name: Specifies the source objects considered for migrating data (It can have individual object names or a group object name).

• object-type: specifies the type of the object specified in the object-name attribute (if object category is specified then object type is "category").

• write-summary-report-to: Specifies the path where the report is generated.

  If only the folder path is mentioned, then file by name DataMigrationReport<n>.xml is created. (optional attribute)

  Report creation has two further subcategories:

  • report-errors (="true/false", with default as "false" (optional attributes))

  • verbose (="true/false", with default as "false" (optional attributes))

Syntax example

<migrate-data
  write-summary-report-to="<filepath/folder>"
  report-errors="true" verbose="true">
    <metabase-object object-name="ssma.TT1"/>
    <metabase-object object-name="ssma.TT2"/>
    <metabase-object object-name="ssma.TT3"/>
    <data-migration-connection
      source-use-last-used="true"/source-server="<server-unique-name>"
      target-use-last-used="true"/target-server="<server-unique-name>"/>
</migrate-data>

Or:

<migrate-data
  object-name="ssma.Tables"
  object-type="category"
  write-summary-report-to="<filepath/folder>"
  report-errors="true" verbose="true"/>

link-tables

This command links the source (Access) table to the target table.

Syntax example

<link-tables>
  <metabase-object object-name="AccessDatabase.table1" object-type="Tables"/>
  <metabase-object object-name="AccessDatabase.table2" object-type="Tables"/>
</link-tables>

Or:

<link-tables>
  <metabase-object object-name="AccessDatabase.Tables" object-type="category"/>
</link-tables>

unlink-tables

This command unlinks the source (Access) table from the target table.

Syntax example

<unlink-tables>
  <metabase-object object-name="AccessDatabase.table1" object-type="Tables"/>
  <metabase-object object-name="AccessDatabase.table2" object-type="Tables"/>
</unlink-tables>

Or:

<unlink-tables>
  <metabase-object object-name="AccessDatabase.Tables" object-type="category"/>
</unlink-tables>

Migration Preparation script file commands

The Migration Preparation command starts schema mapping between the source and target databases.

map-schema

Schema mapping of source database to the target schema.

Script

• source-schema specifies the source schema we intend to migrate.
• sql-server-schema specifies the target schema where we want it to be migrated.

Syntax example

<map-schema source-schema="source-schema"
            sql-server-schema="target-schema"/>

Manageability commands

The Manageability commands help synchronize the target database objects with the source database.

The default console output setting for the migration commands is 'Full' output report with no detailed error reporting: Only summary at the source object tree root node.

synchronize-target

Synchronizes the target objects with the target database. If this command is executed against the source database, you get an error.

If the target database connection isn't performed before executing this command, or the connection to the target database server fails during the command execution, an error is generated and the console application exits.

Script

• object-name: Specifies the target objects considered for synchronizing with target database (It can have individual object names or a group object name).

• object-type: Specifies the type of the object specified in the object-name attribute (if object category is specified then object type is category).

• on-error: Specifies whether to specify synchronization errors as warnings or error. Available options for on-error:

  • report-total-as-warning
  • report-each-as-warning
  • fail-script

• report-errors-to: Specifies location of error report for the synchronization operation (optional attribute)

  If only the folder path is given, then a file by the name of TargetSynchronizationReport.xml is created.

Syntax example

<synchronize-target
  object-name="ots_triggers.dbo"
  on-error="<report-total-as-warning|
             report-each-as-warning|
             fail-script>"              (optional)
  report-errors-to="<file-name>"        (optional)
/>

Or:

<synchronize-target
  object-name="ssma.dbo.Procedures"
  object-type="category"/>

Or:

<synchronize-target>
  <metabase-object object-name="ssma.dbo.TT1"/>
  <metabase-object object-name="ssma.dbo.TT2"/>
  <metabase-object object-name="ssma.dbo.TT3"/>
</synchronize-target>

refresh-from-database

Refreshes the source objects from database. If this command is executed against the target database, an error is generated.

Script

Requires one or several metabase nodes as command line parameter.

• object-name: Specifies the source objects considered for refreshing from source database (It can have individual object names or a group object name).

• object-type: Specifies the type of the object specified in the object-name attribute (if object category is specified then object type is category).

• on-error: Specifies whether to specify refresh errors as warnings or error. Available options for on-error:

  • report-total-as-warning
  • report-each-as-warning
  • fail-script

• report-errors-to: Specifies location of error report for the synchronization operation (optional attribute)

  If only the folder path is given, then a file by the name of SourceDBRefreshReport.xml is created.

Syntax example

<refresh-from-database
  object-name="ssma.TT1"
  on-error="<report-total-as-warning|
             report-each-as-warning|
             fail-script>"              (optional)
  report-errors-to="<file-name>"        (optional)
/>

Or:

<refresh-from-database
  object-name="ssma.Procedures"
  object-type="category"/>

Or:

<refresh-from-database>
  <metabase-object object-name="ssma.TT_1"/>
</refresh-from-database>

Script Generation script file commands

The Script Generation commands help save the console output in a script file.

save-as-script

Used to save the scripts of the objects to a file mentioned when metabase=target. This is an alternative to the synchronization command, in which we get the scripts and execute the same on the target database.

Script

Requires one or several metabase nodes as command line parameter.

• object-name: Specifies the objects whose scripts are to be saved. (It can have individual object names or a group object name).

• object-type: Specifies the type of the object specified in the object-name attribute. if object category is specified, then object type is category.

• metabase: Specifies whether it's the source or target metabase.

• destination: Specifies the path or the folder where the script has to be saved. If the file name isn't given, then a file name in the format (object_name attribute value).

• overwrite: If true then it overwrites if same filenames exist. It can have the values (true/false).

Syntax example

<save-as-script
  metabase="<source/target>"
  object-name="ssma.dbo.Procedures"
  object-type="category"
  destination="<file/folder>"
  overwrite="<true/false>"             (optional)
/>

Or:

<save-as-script
  metabase="<source/target>"
  destination="<file/folder>"
>
    <metabase-object object-name="ssma.dbo.Procedures"
                     object-type="category"/>
</save-as-script>

Related content

• Command-line options in the SSMA Console
• Working with the Sample Console Script FilesExecuting the SSMA Console
• Managing Passwords
• Generating Reports
• Troubleshooting