Extract and Load via SSMT
The SSMT tool is an Allscripts provided tool that allows clinics to extract and load the data in their TouchWorks system. Three common uses are:
- To move data from one database to another
- To load data from a spreadsheet to a database
- To extract data, edit it, and then load it back
This tool should be used by a person who understands how to use it and you should be sure to follow the Allscripts documented process when utilizing the tool.
Warning: Ensure that you complete a database SQL Backup prior to loading any data through SSMT. The tool is pretty robust, but human error or progam bugs could create a mess. Completing a backup first takes only a few minutes and can save hours of time if something does happen.
- Navigate to the SSMT site through Internet explorer. The address will be:
When using a virtual server, search from within the virtual server and use:
(When using to access client systems use what for server name?) (add explaination and where to find for the login values)
- Login to SSMT. Make sure that you are logging into the correct SQL database.
- Clinical DB Server:(explaination/source?)'galentest'
- Clinical DB: (explaination/source?)'Works'
- Clnical DB User: (explaination/source?)'sa'
- Clinical DB Password: (explaination/source?)
- FW DB Server:(explaination/source?)'galentest'
- FW DB: (explaination/source?)'Works'
- FW DB User: (explaination/source?)'sa'
- FW DB Password: (explaination/source?)
- Verify the Headers and make sure that you are in the correct Database and Framework.
- Select the content category you will be extracting from the drop down menu. The name here should match the name in the Build Workbook (BW)
- The Show Database calls box should remain unchecked unless you are using it for troubleshooting.
- ALWAYS check show headers for extracting data
- Extract Data by clicking on the Extract data button in the lower left hand corner of the screen.
- This is what your screen will look like
- keep this window open and open Excel.
- WARNING Before you put ANY information into Excel:
- click in the upper right hand corner of the screen to select all cells
- right click and select format cells
- In the Format cells Dialog box select “Text” and click on OK
If you fail to follow the steps outlined above, it will seriously hurt your data by stripping all of the leading zeros and making parts of the data meaningless to Touchworks. The only exception to this rule is when working with menus and text templates, you want to look through the data to see if you see any pound symbols ex. ###. If these show up it means that the data is too large for the cell. In that case you want to highlight the entire column and format those cells to “general”
- Keeping Excel open, Navigate back to SSMT.
- Click inside the large data field and use CTRL-A to SELECT ALL (do not click and drag to select all because you could miss something.
- use CTRL-C to copy
- Navigate back to Excel.
- Put your cursor in the very first cell of the Excel spreadsheet
- Use CTRL-V to paste the data into Excel.
Now your Data appears in columns with headers that are meaningful.
TO LOAD DATA
- Select the content category you will be loading from the drop down menu. If loading from the PreConfig Excel Workbook, The name here should match the name in the PreConfig Excel Workbook.
- In the SSMT Window, use CTRL-A to select all, then hit delete. This ensures that there are no empty spaces in the SSMT data window that could throw off the Data you are loading.
- Uncheck “extract headers”
- Navigate back to your excel document. In Excel, you want to select all of your Data, but not the headers and no empty column. Go to Column A and grab starting from cell A2, even if there is no data in that cell and drag to grab all columns with data and NO MORE THAN 1000 ROWS of DATA
- Use Ctrl-C to copy the Data
- Navigate back to SSMT
- put cursor into the SSMT Data Field
- Use CTRL-A to select all
- Use CTRL-V to paste data from excel.
Note: using Ctrl-A then Ctrl- V helps ensure that you do not have any blank spaces in the data field that will distort your data.
- click on Load Data
- When loading menus the Server IIS Services MUST be restarted after the load for menus to appear and full privileges must be given to 'twappadmin'.
The SSMT tool returns various error messages. Here is a page dedicated to SSMT Error Messages
Below are a list of some the spreadsheets used to to migrate or update data via SSMT. Select the Spreadsheet name for a more in-depth description.
- SSMT: Users / Providers - This is the spreadsheet you use to load and manger user and provider accounts.
- SSMT: User Security Classifications - This is the spreadsheet that allows you to assign security classifications to a user.
- SSMT: RID - Resultable Item Dictionary - This is the spreadsheet that allows you to load the result definitions from your various lab vendors.
- SSMT: OID - Orderable Item Dictionary - This spreadsheet allows you to load you Order Level items for your various vendors.
- SSMT: Order Performing Facility Identifiers - This spreadsheet is where you synchronize multiple vendors.
- SSMT: OID - Order Defaults - Req Perf Location / Site - This is the spreadsheet that allows you to set defaults on a Site or Requested Performing Location level. This can set various default behaviors such as charge behavior, order detail, and much, much more.
- SSMT: OID - Order Defaults - Insurance/PatientLocation/Site - This allows you to specify orderable behavior on the insurance, patient location, or site level. It is used to set defaults such as the Default Requested Performing Location, Requested Performing Location Picklist, Internal/External Required behavior, Referred to Vendor Org required behavior, Referred to Location Site Required behavior, and Referred to Provider Required behavior.