In this blog, I would like to discuss about how we can perform a silent installation (without constant interaction or prompts) of SQL Server Migration Assistant (SSMA). Please note that this method is not a documented and hence this post is provided "AS IS" with no warranties, conferring no rights. / supported scenario.
As you would know, Microsoft SQL Server Migration Assistant (SSMA) is a tool to automate migration from Microsoft Access database(s) / Oracle database / MySQL database / Sybase ASE / DB2 databases to SQL Server and Azure SQL DB. SSMA automates all aspects of migration including migration assessment analysis, schema and SQL statement conversion, data migration as well as migration testing to reduce cost and reduce risk of your database migration project.
Now let us come back to the topic, the following are the download pages (Please check online for the most recent versions available)
Download SQL Server Migration Assistant (SSMA) v.6.0:
Microsoft SQL Server Migration Assistant v6.0 for Access: http://www.microsoft.com/en-in/download/details.aspx?id=43690
Microsoft SQL Server Migration Assistant v6.0 for Oracle: http://www.microsoft.com/en-in/download/details.aspx?id=43689
Microsoft SQL Server Migration Assistant v6.0 for MySQL: https://www.microsoft.com/en-us/download/details.aspx?id=43688
Microsoft SQL Server Migration Assistant v6.0 for Sybase: https://www.microsoft.com/en-us/download/details.aspx?id=43691
Microsoft SQL Server Migration Assistant v6.0 for DB2: https://www.microsoft.com/en-in/download/details.aspx?id=45296
All the documented methods for silent installation uses the Microsoft Windows Installer (MSIEXEC.EXE) executable program that interprets the Installer MSI packages and installs products. Msiexec enables you to specify property values from the command line. In this blog, we are using SSMA for Oracle as an example. The catch here is that the SSMA download page installation comes with SSMA for Oracle.6.0.0.exe & SSMA for Oracle.6.0.0.ExtPack.exe packages only and no MSI files. The following method includes the way in which you can extract the MSI file from the EXE installer executable program and then perform a silent installation using Microsoft Windows Installer (MSIEXEC.EXE) with property values from the command line.
Extract the MSI File from the SSMA for Oracle.6.0.0.exe Installer EXE program:
There are numerous methods discussed online to achieve this, but the one that we had tested and was working is as below,
In this method, we are using the fact that most of the installers extracts the .MSI file to the temp folder during their installation process.
1. Start the SSMA for Oracle.6.0.0.exe.
2. Now when you see the above window, please do not click Next in this window and do not close it.
3. Start the Run command and traverse to the temp folder. You can type in %temp% and hit enter. (C:\Users\<LoggedInUser>\AppData\Local\Temp)
4. Now inside the temp folder, sort the folder by Date Modified field.
5. Now check for any file with the .TMP file format. Note this file will have a name like SS***.tmp file.
6. Copy this file back to any other local folder and then rename the file extension format from .TMP to .MSI fil
7. Now we have the MSI file ready with us. (You can verify that the file is intact by double-clicking it. Also make sure that you close it after verification)
Use MSIEXEC with property values to perform the silent installation:
This is a simple straight forward method. The entire set of command-line options are available in the following articles,
Msiexec (command-line options): https://technet.microsoft.com/en-in/library/cc759262(v=ws.10).aspx
Standard Installer Command-Line Options: https://msdn.microsoft.com/en-us/library/windows/desktop/aa372024(v=vs.85).aspx
In our case, we just need to run the following command.
1. From an elevated command prompt window, please traverse to the local folder where you had stored the local copy of *.msi file.
2. Please execute the following command, msiexec /I SSM8AED.msi /qn /Lv* "C:\SSMA\installlog.txt"
Command: msiexec /I SSM8AED.msi /qn /Lv* "C:\SSMA\installlog.txt"
Command-line properties used:
/I - </package | /i> <Product.msi>
Installs or configures a product
/qn - /q[n|b|r|f]
Sets user interface level
n - No UI
b - Basic UI
r - Reduced UI
f - Full UI (default)
i - Status messages
w - Nonfatal warnings
e - All error messages
a - Startup of actions
r - Action-specific records
u - User requests
c - Initial UI parameters
m - Out-of-memory or fatal exit information
o - Out-of-disk-space messages
p - Terminal properties
v - Verbose output
x - Extra debugging information
+ - Append to existing log file
! - Flush each line to the log
* - Log all information, except for v and x options
3. As mentioned in the previous step, the /Lv* option is for logging and if you open & look into the log file, you would see the following logs that confirms that the product is successfully installed.
MSI (s) (3C:48) [14:04:50:414]: Windows Installer installed the product. Product Name: Microsoft SQL Server Migration Assistant for Oracle. Product Version: 6.0.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Installation success or error status: 0
If you are still having any issues with the installation, please leave your comments below.
Happy Migration !
NOTE: This method is not documented and hence this post is provided "AS IS" with no warranties conferring no rights/supported scenario.
Author: Krishnakumar Rukmangathan, Technical Advisor, SQL Server BI-ONE Developer team, Microsoft
Reviewed by: Sunil Kumar B.S, Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft.