---
title: "Automated Assay Loader v3.0 User Guide"
canonical: "https://servicedesk.maxgeo.com/space/MAXGEO/1254752266/Automated%20Assay%20Loader%20v3.0%20User%20Guide"
format: markdown
---
**Automated **
**Assay Loader 3.0**
www.maxgeo.com
**User Guide**
> Macro (anchor)
Level 1, 25 Cantonment Street
North Fremantle, WA 6160
Phone: +61 8 9432 1777 - Fax: +61 8 9432 1778
www.maxwellgeoservices.com
Level 1, 25 Cantonment Street
North Fremantle, WA 6160
Phone: +61 8 9432 1777 - Fax: +61 8 9432 1778
www.maxwellgeoservices.com
# TABLE OF CONTENTS
> Macro (toc)
# > Macro (anchor)
Introduction
Maxwell has developed a SQL Server Integration Services package for the automation of assay loading. At client specified times, this package will import incoming assay files from laboratories into the DataShed incoming assay buffer. It uses the DataShed assay loading and validation routines and quarantines invalid data. The incoming assay file can either be retrieved via an email address or a folder location. Notification of the status of each assay file is done via email if retrieved by email or by a summary file in a specified folder location if retrieved by a folder location. A Management Admin Console windows program monitors the imported status of the assay files.
This manual covers the basic procedures for setting up Automated Assay Loading on a SQL server. The SSIS package has been tested in a SQL 2008 R2 and SQL 2012 environment, with a DataShed database v4.5.1 or later.
# > Macro (anchor)
Technical Requirements
## > Macro (anchor)
Software
SQL Server Integration Services needs to be installed on the SQL Server.
## > Macro (anchor)
SQL Permissions / Privileges
- Server level – sys admin
- Database level – db owner
## > Macro (anchor)
Provided File Checklist
- AutoRun.exe – Installer to execute the installer for the AutoAssayLoaderAdmin Management Console and to execute scripts on DataShed SQL database
- In Extras/SSIS Packages - contains the different SQL Server and MDS versions available
- AutoAssayLoader_2.2.0_Script.sql (in Extras folder)
#
> Macro (anchor)
Database Structure
## > Macro (anchor)
Additional Configuration Tables
System tables to be created are:
- tblSYSAutoAssayConfigurations
- tblSYSAutoAssaysEmails
- tblSYSAutoAssayAlertSettings
- tblSYSAutoAssayAttachments
- tblSYSAutoAssayImportRules
- tblSYSAutoAssayDirectorySettings
Library tables to be created:
- tblLIBAutoAssayActionTypes
- tblLIBAutoAssayImportRulesTypes
- tblLIBAutoAssayQAQCType
Use the provided SQL scripts to create and populate these tables. Make sure that the name of the database to be used is edited in the scripts before executing. Alternatively the scripts can be executed from within the AutoRun.exe, click the Update Database button to run the scripts.
## > Macro (anchor)
Scheduled Job to run Automated Assay SSIS package
A SQL job needs to be created to automatically run the Automated Assay Loading Package. In the same SQL Server that your SQL DataShed database resides use the appropriate package for the version of SQL Server. Setup as follows:
1. Copy the package to a location on the server.
2. In SQL Server Management Studio go to SQL Server Agent.
3. Right click New Job…
4. General tab
5. Name: SSIS Auto Assay Task
6. Owner: Database Manager
7. Category: [Uncategorised (Local)]
8. Steps tab
9. New
10. New Job Step page opens
11. Step Name: SSIS Package
12. Type: SQL Server Integration Services Package
13. Package Source: File System
14. Package: Select the SSIS package from the location copied to earlier.
15. Check in the Data sources tab – Check AutoAssayLoader and set the connection string to your database.
16. Eg Data Source=(local)\SQL_2014;Initial Catalog=MDS_v464_Demo;Integrated Security=True;
17. OK
18. Schedules tab
19. New
20. New Job Schedule page opens
21. Name: SSIS Schedule
22. Schedule Type: Recurring
23. Set up the Frequency as required.
24. OK
25. OK Executes & creates New Job – SSIS Auto Assay Task
# > Macro (anchor)
Administrator Console
A management console exists to monitor and manage the status of files that have been received with the Auto Assay loader package. The console allows the setup of the automated assay configurations via the Tools menu.
**This program is to be installed on a local computer running windows. The local computer and user running the console must have access to the SQL Server and the DataShed database to be able to configure and monitor the automated assay loading. As well as, the machine that houses the SQL Server.**
With the supplied files an AutoRun.exe installation file exists to install the admin console. Click the Install button to install the administrator console. This needs to be installed, it will ask for your DataShed database location the first time it is executed, enter the database server location and your database. This window is also accessible from the File Connect menu option.
The management console will look like this when running.
Setup and configuration is required, editing the settings can be done from within the Tools menu option. Email, Configurations, Import Rules and Directory Setup can be edited here. Data must be populated into these relevant data tables prior to running the package. The following are the tables that relate to the menu items.
- Tools Email (tblSYSAutoAssayAlertSettings)
- Tools Configuration (tblSYSAutoAssayConfigurations)
- Tools Import Rules (tblSYSAutoAssayImportRules)
- Tools Directory Setup (tblSYSAutoAssayDirectorySettings)
# > Macro (anchor)
Mail Server Settings
Email servers are required to be set up with a from email address which is used to send AAL status. For this version enhancements such as additional fields are available to configure SMTP for secured transfer.
# > Macro (anchor)
Configuration Tables
This is a summary of the configuration tables with the fields and what they need to be filled with.
**tblSYSAutoAssayConfigurations**
- **tblSYSAutoAssayConfigurationsID:** Auto filled
- **File Layout:** the name of the Import Layout that is used to import this assay, making sure that it matches exactly to the Layout name in tblSYSAssImportLayout.
- **Status Recipient Email:** the user’s email address who is to receive the status emails.
- **UsePopServer:** True when retrieving assay files from within an email attachment. False when using a file location to dump the assay file waiting to process.
- **Mail Server:** the name of the POP Exchange mail server (Must be set if UsePOPServer is set as True)
- **Incoming Email:** the unique email account for this unique layout. (Must be set if UsePOPServer is set as True) This is the email account that the Lab will send files to.
- **Incoming Password:** the password to the mail box (Must be set if UsePopServer is set as True). When an email password is changed due to company policy it will need to be changed within the configuration also.
- **Use Folder:** True when using a file location to dump the assay file waiting to process. False when retrieving assay files from within an email attachment.
- **Folder Path:** Folder location used for the layout being configured. Eg W:\Incoming_Assays\ABC_LABS_SIF (Must be set when Use Folder is True)
- **MailServerPort: **Port used to receive emails. Default POP3 non-encrypted port is 110. Can be configured to use a secure Port 995.
- **FromEmailAddress: **Allow SSIS Package to determine different sender (Labs) going into the same recipient email address.
- **ToEmailAddress:** Allow SSIS Package to be configured for a specific email address with multiple email alias coming from Labs.
- **File_Prefix:** Allow SSIS Package to determine the right attachment file for the selected import layout, if Assay files are being sent to the same email address.
## > Macro (anchor)
Folder Setup OPTION
For *each and every* lab file layout to be automatically imported, a unique folder location has to be set up for it. This is the way that the Automated Assay system determines which import layout to use to import a file as it is created in the relevant location. For example, if you receive assays from
- Genalysis in the formats CSV and SIF
- ALS in CSV
- ABC_Labs in SIF
If all these formats are required to be automatically loaded, there will need to be 4 separate folder locations created to receive the files into.
An example of folders would be
- W:\Incoming_Assays\Genalysis_CSV
- W:\Incoming_Assays\Genalysis_SIF
- W:\Incoming_Assays\ALS_CSV
- W:\Incoming_Assays\ABC_LABS_SIF
## > Macro (anchor)
Email setup OPTION
For each and every lab file layout to be automatically imported, a unique email account has to be set up for it. This is the way that the Automated Assay system determines which import layout to use to import a file as it comes in. For example, if you receive assays from
- Genalysis in the formats CSV and SIF
- ALS in CSV
- ABC_Labs in SIF
If all these formats are required to be automatically loaded, there will need to be 4 unique email addresses created to receive them on POP Exchange Mailbox.
# > Macro (anchor)
IMPORT Rules / Alerts
A number of rules or alerts can be defined and set up against your import layouts. If the rule passes successfully then the file import will continue, if the rule fails then the relevant error will be reported.
Examples of these checks can include:
- Batch No not supplied in file
- Lab Job Date not supplied in file
- Assay Result is null
- Lab Method does not exist in database
- Lab Element does not exist in database
*Detailed examples of rules that can be set up can be found in the file ‘Examples of Alerts.xlsx’*
# > Macro (anchor)
Directory Setup/Folder Settings
# > Macro (anchor)
Status emails or files
If receipt of assay files is from a POP server/email address then Emails are sent to the Status Recipient (in tblSYSAutoAssayConfigurations) once the package has been run. There are 3 different messages that come through and these are the Subject headings of the emails:
- Automated Assay Processing: Batch XXXXX Successful
- Automated Assay Processing: Batch XXXXX Incomplete
- Automated Assay Processing: Batch XXXXX Failed
If receipt of assay files is from a folder location via the Use Folder/Folder Path (in tblSYSAutoAssayConfigurations) once the package has been run. The status of the jobs will be saved into an appropriate folder as defined in the Folder Settings (see section 8).
## > Macro (anchor)
Examples of Messages contained in Emails
The following are example email messages for Successful, Incomplete and Failed assay files.
When Batch Incomplete email is received, it means most of the assay results have gone through but some have failed validation rules and have been quarantined in the tblSYSAssIncoming buffer table. These results can be accessed via Advanced Assay Tools in DataShed to rectify the problem and complete the merge.
When Batch Failed email is received, error messages within the email may give a hint as to what the problem might be. The assay lab file is sent back as an attachment and the best solution is to reload that file manually via DataShed.
Batch Successful
Batch Number: 1129.0/0702109
LabCode: GENALYSIS
From: ""Jena Gunther"
Attachment: 1129_0_0702109.S01
Total Samples: 155
Merged Samples: 155
Total Assays: 155
Merged Assays: 155
Importing Stage: Completed
Validation Stage: Completed
Ranking Stage: Completed
Batch Incomplete
Batch Number: 000_0_0000569
LabCode: GENALYSIS
From: "Jena Gunther"
Attachment: 000_0_0000569.CSV
Total Samples: 232
Merged Samples: 231
Total Assays: 464
Merged Assays: 463
Importing Stage: Completed
Validation Stage: Incomplete
Ranking Stage: Incomplete
*** Check for Unmerged Assay Results in DataShed ***
Batch Failed
Batch Number: OR07094662
LabCode: ALS_PTH
From: "Jena Gunther"
Attachment: OR07094662.csv
Total Samples: 505
Merged Samples: 120
Total Assays: 3447
Merged Assays: 798
Importing Stage: Completed
Validation Stage: Failed
Ranking Stage: -
Errors: ** Validating Imported Data **
Missing Lab Method Alias. Assays Affected : 122
# > Macro (anchor)
Attachments in lab emails
Only ‘SIF’ and ‘CSV’ attachment files will be processed by the auto assay loader package. It is recommended that the email address is used only for these Lab files. Attachments of other types will be ignored.
## > Macro (anchor)
Embedded Images
It should be noted that any embedded images in emails from the lab (containing the Lab Assay file), will be treated as attachments and tried to be imported via the Automated Assay Loading package. Status emails will be sent to the Status Recipient as ‘Batch Failed’ and a record of the attachment will be populated in tblSYSAutoAssayAttachments. There is currently a filter to stop files of type ‘jpg’,’gif’ and ‘doc’ from being processed, all other attachments are assumed to be lab files.
# > Macro (anchor)
Assay Loader – Normal DataShed Workflow
# > Macro (anchor)
Assay Loader – Automated Workflow
# > Macro (anchor)
Automated Assay Loader Configuration and Testing Summary
## > Macro (anchor)
Configuration Requires:
1. LAB Import layout setup in DataShed (Best that it is tested manually)
2. Additional tables to be Scripted into the database
3. Tables require configurations as follows
| | |
| --- | --- |
| Table | Configuration |
| tblSySAutoAssayConfigurations | Define the Import Layout and whether file is received from email or file location.
(Configuration Settings in Console Tool) |
| tblSySAutoAssaysEmails | Used if receiving assay files directly from email
(Alert Settings in Console Tool) |
| tblAutoAssayAlertSettings | |
| tblAutoAssayAttachments | Table where assay file is import (data is kept in an image data type) |
| tblAutoAssayImportRules | Additional rules and alerts to perform validation on the assay file. (Import Rules in Console Tool) |
| tblSYSAutoAssayDirectorySettings | Imported files locations dependant on result (Fail,Sucess,Suspect) (Directory Setup in Console Tool) |
| tblLIBAutoAssayActionTypes | > Macro (nested-table) |
| tblLIBAutoAssayImportRulesTypes | > Macro (nested-table) |
| tblLIBAutoAssayQCQCType | > Macro (nested-table) |
1. Test Lab imports into datashed manually
2. Trial the SSIS package with the Execute Package utility – Manually set dtsx and connection string correctly. Steps are:
- tskFolderPickup
- tskLoadAssays
- tskPOPMail
- tslRankAssays
- tskSendStatusEmails
- tskValidateAssays
1. Automate SSIS Package with SQL Agent Job schedule.
2. Trial with SQL Agent Job – check log and Agent Status.