Repeat SSMA Data Migration Using Command Line

Question

I am converting my Access 2007 backend database to SS2k5. Been doing it for a long time. I've now gotten to the point where I'd like to refresh the SS2k5 data periodically during the day so testing goes smoothly. I figured out how to use SSMA GUI with a saved already-executed project to migrate the tables only. (I'm migrating about 95 of the 100 tables.) I'd like to cast that process into a command line so I can schedule it to run a couple times a day automatically.

You SSMA experts.... What are the basic steps?

---

Jim

Answer 1

Hi Jim

 

try using SSIS features, you can directly refresh target data with Access 2007 data.

 

-Giri

---

Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Answer 2

Giri,

SSMA has already mapped data types, etc. SSIS has been difficult for me, but when I got it going, I realized that a number of data type issues remained. SSMA had already taken care of those, and has the mappings in its project file.

---

Jim

Answer 3

Hi JimS-Indy

 

try this microsoft link might help http://msdn.microsoft.com/en-us/library/hh313078.aspx

 

-Giri

---

Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker

Answer 4

Thanks, Giri

  The link you gave me is roughly the same as the help file on the SSMA module. It's pretty good at syntax, but pretty short on examples, particularly examples using exising projects.

---

Jim

Answer 5

Thanks, Giri

  The link you gave me is roughly the same as the help file on the SSMA module. It's pretty good at syntax, but pretty short on examples, particularly examples using exising projects.

---

Jim

Hi Jim,

Please refer to the blog of SSMA team. It has detailed video examples for migrating Oracle databse which is similar with Access in the script format. You just need to customize the XML file by the Command Line Options in SSMA Console (Access) and Executing the SSMA Console (Access)

Hope this helps.

   

---

Best Regards,
Peja

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 6

I'm a little further along, but still not getting far.

If I use "open-project", do I still have to explicitly define the source and target, or does the project handle that?

---

Jim
Further...I've got it going a ways. Now he gets a connection failure on the SQL Server side:

<?xml version="1.0" encoding="utf-8"?>
<!--
		Script file for SSMA-v4.2 Console for Access.
		Commands execution order - from top to bottom.
		Command Processor distinguishes each command by element name. 
		The element name is invariable! Never modify it! 
		Use this file name as the parameter to SSMA-v4.2 Console for Access with mandatory 
		option -s[cript]. See the documentation for SSMA-v4.2 Console for more information.
-->
<ssma-script-file xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="C:\Microsoft SQL Server Migration Assistant for Access\Schemas\A2SSConsoleScriptSchema.xsd">
 <!-- Variable values should mandatorily start and end with "$". 
    These values can be defined in a separate variable value file
    (See :VariableValueFileSample.xml)
    
    **********************************************************************
    ********** Set the variable values used by this sample   **********
    ********** file in the corresponding Variables Value File **********
    **********************************************************************
    
    -->
 <!-- The escape character for “$” is “$$”.If the value of a static value of a parameter begins with “$”,
    then "$$" must be specified to treat it as a static value instead of a variable. -->
 <!-- Optional section with console configuration options-->
 <!-- Optional section with server definitions -->
 <!-- Note: Server definitions can be declared in a separate file 
       or can be embedded as part of script file in the servers section (below)-->
 <script-commands>
  <!--Load Up an Exisitng Project-->
  <open-project project-folder="$project_folder$" project-name="$project_name$" />
  <!-- Connect to source database (access).Add *.mdb files to source metabase tree.-->
  <!-- $AccessDbFolder$ is the variable which has the value defined in the Variable value file -->
  <!--Example 2: Use *.mdb in database-file to add all the databases available inside a folder.-->
  <!--<load-access-database>
   <access-database database-file="$AccessDbFolder$\*.mdb"/>
  </load-access-database>-->
  <!-- Connect to target database -->
  <!-- • Server(id) needs to mandatorily be defined in the servers section of the 
     script file or in the Servers Connection File-->
  <migrate-data object-name="$AccessDatabase$.Tables" object-type="category" write-summary-report-to="$SummaryReports$" report-errors="true" verbose="true" />
  <!--alternative convention for Data Migration Command-->
  <!--Example2: Data Migration of specific tables with no object-type attribute & write-summary-report-to with a file name -->
  <!--<migrate-data write-summary-report-to="$DataMigrationReports$\datamigreport.xml" 
           verbose="true">
     <metabase-object object-name="$AccessDatabase$.TblTest" />
    </migrate-data>-->
  <!-- Link tables -->
  <!-- Linking the migrated tables in target server to the Access source database-->
  <!-- • object-name specifies the object(s) considered for data migration .
      (can have indivdual 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 will be "category")-->
  <!-- Example1: Link all the tables in the database-->
  <close-project if-modified="save" />
 </script-commands>
</ssma-script-file>

Answer 7

I have simplified my xml file. It begins the migration process, but immediately fails with an error that says: "Attempt to reconnect target failed". My simplified xml file makes no reference to the target, assuming it's referenced in the Project files. Indeed, I believe it is, because when I open the target in the GUI, it automatically shows the target DB (I have moved over to my client's system now, using NT authentication, and it works in the GUI.)  Here's the script:

<?xml version="1.0" encoding="utf-8"?>
<!--
		Script file for SSMA-v4.2 Console for Access.
		Commands execution order - from top to bottom.
		Command Processor distinguishes each command by element name. 
		The element name is invariable! Never modify it! 
		Use this file name as the parameter to SSMA-v4.2 Console for Access with mandatory 
		option -s[cript]. See the documentation for SSMA-v4.2 Console for more information.
-->
<ssma-script-file xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="f:\Schemas\A2SSConsoleScriptSchema.xsd">
 <config />
 <servers>
 <!-- Server definition for Sql server target server-->
 <sql-server name="CPASD">
  <sql-server-authentication>
  <server value="$SQLServerName$" />
  <database value="$SQLServerDb$" />
  <user-id value="$SQLServerUsrID$" />
  <password value="$SQLServerPassword$" />
  <encrypt value="true" />
  <trust-server-certificate value="true" />
  </sql-server-authentication>
 </sql-server>
 </servers>
 <script-commands>
 <!--Load Up an Exisitng Project-->
 <open-project project-folder="$project_folder$" project-name="$project_name$" />
 <!--Migrate the Data for all tables-->
 <migrate-data object-name="$AccessDatabase$.Tables" object-type="category" write-summary-report-to="$SummaryReports$" report-errors="true" verbose="true"></migrate-data>
 <close-project if-modified="save" />
 </script-commands>
</ssma-script-file>

I will admit that I do refer to a sql server db in the "servers" element, but I never refer to the "servers" element, or the CPASD server in my other element, so it's not used at all.

If I use the CPASD server element with a "reconnect" directive, I get a message saying it can't connect because SSMA is already connected.

This should be a simple thing. Help!

---

Jim
PS, I took out the entire "servers" element and got exactly the same result.

Answer 8

To close this out, I spent many, many hours with an engineer at MS. SSMA is a wonderful tool, but its documentation is still poor, as is its support. We finally figured out together that in order to connect to the target database, we had to issue a "reconnect" directive (as opposed to a "connect" directive.) That made my work much easier, but then we ran into a limitation of the command line interface....

SSMA command line is unable to migrate data from a subset of the "tables" group of source objects. There is no syntax to accomplish it, and there is no way for the command line interface to glean the desired subset from the existing project. As a result, the command line interface tried to migrate linked tables from my Access front end that were already linked to SQL Server (a different database...) and linked to Sharepoint tables. Needless to say, this created a lot of error messages, though in the end, I got most of what I needed.

MS is considering changing SSMA to allow subsets, but has not done so yet.

---

Jim

Answer 9

Jim, were you ever able to get any further with this?  I'm needing to automate the process from Access 97 to SQL 2008 R2.  I'm even having trouble re-running the SSMA file I saved.

Answer 10

@jdouthit,

  The process of "rerunning" SSMA conversions is both simple and horribly complex. If you just want to run what you ran last time, meaning you do all three steps -- analyze, convert, load -- it can be easy enough, but never straightforward. MS seems to have created the desktop scripting agent as an afterthought for its own develpers, and was caught somewhat less than prepared when I wanted to use it (and perhaps many others....) In any case, most of the features are documented, just in a rather disjointed way. A few of them are missing entirely, like the ability to convert less than the entire source db. Subsets work in the GUI model, but are literally not supported in the command line model.

  If you're having a specific problem, start a new thread and MS will take notice. They have a vested interest in making it easy to convert from Access ($150) to SQL Server ($Lots), and not making it particularly easy to convert to Oracle ($0). Simple math.

  I haven't touched this in months (finished my conversion), but I'd be happy to provide any insight I can. Let's start with a new thread on this forum, but I can ultimately get you my email address for private discussions if that becomes necessary.

---

Jim