S2.10.1 - SSIS Tutorial - Mail Sortation

Installation

During installation of matchIT SQL, ensure that you have the Mail sortation components box checked. By checking this, the MailSortation SSIS task will be installed.

Usage

Mail Sortation is performed by separate software called Sort and Save, by BBS Ltd. This software is installed and configured via the matchIT SQL MailSortation SSIS task. The Mail Sortation task must be connected to a GenerateKeys task in order access the data being used for the sortation.

When the task runs as part of your SSIS package, your SQL data is extracted and passed to Sort and Save, for sortation. Sort and Save will generate various Mail Sortation data fields as well as the reports that are required by the Mail Sortation carriers.
Upon opening the MailSortation task, you will see the following options. These must be configured before the task can be run as part of your SSIS package:

• Source: There must be a preceding task within your SSIS package, where the SQL source data table(s) are specified and the address and postcode fields are mapped. This can either be a GenerateKeys task or a GenerateCorrectedAddresses task. This Source dropdown menu is used to specify the preceding task. Note – if the data source connects to a SQL table on a remote server, it is essential that a Preferred Job File Directory is specified within the Mail Sortation task, and that this directory can be accessed by both the local PC that will run the SSIS package and by the remote server, where the SQL table is stored.
  
  
• Job Name: Job files (text files with a .job extension) are used to store Mail Sortation configuration settings, E.g. item weight, mail format, delivery speed, etc. You can use the Job Name option either to select an existing job file or to create a new one.
  
  To create a new job file, simply type the name of the file into the Job Name text box (no file path required) and click on the Configure Job button. If you have not yet specified the location of your Sort and Save installation, you will be prompted to do so before your job file can be created.
  
  
• Configure Job: This will open the current job file in Sort and Save’s Job Configuration mode. Here you can configure all options specific to the job, E.g. item weight, mail format, delivery speed, etc. You can also specify the carrier, E.g. Royal Mail or a Downstream Access carrier, as well as reporting and Mailmark options. See Advanced Automation for information on updating job files manually.
  
  
• Mailmark Controller: This will launch the Sort and Save Mailmark Controller. The Mailmark Controller gives you full control over any Mailmark jobs that have been run. These jobs can be uploaded, split, rescheduled or cancelled, as required.
  
  
• Install Sort and Save: This will launch the Sort and Save Installer, which will download and install the latest version of the Sort and Save software onto your PC.
  
  
• Sort and Save Installation: Here, you can map your installation of Sort and Save. This option is automatically populated when you use the Install Sort and Save button to perform the install. If you already have Sort and Save installed, you can click on the selection button to map this installation.
  
  
• Preferred Reports Directory: This option lets you choose where the generated Mail Sortation reports are copied to after each Mail Sortation job is run. Click the Clear button to clear this text box and select a new location. The default location is C:\matchIT SQL\SSIS\mailSortation\reports.
  
  
• Preferred Job File Directory: This option lets you choose where to generate and store job files, or map existing job files. This can be in a shared location, accessible from other PC’s on a network. Click the ellipses button to select an existing directory on a mapped drive. To specify a new directory or a location that is not on a mapped drive (e.g. a share on another PC), you must manually type or paste the path into the text box.  Click the Clear button to clear this text box. With this text box empty, job files will be generated in the Sort and Save PARAMS directory. Note – when using this task to perform a Mail Sortation on data on a remote server, it is essential that a Preferred Job File Directory is specified, and that this directory can be accessed by both the local PC that will run the SSIS package and by the remote server, where the SQL table is stored.
  
  
• Output Table Extension: When a Mail Sortation job is run, the sortation data, E.g. selection codes, barcodes, Mailmark barcodes, bag breaks, etc, are copied into a new SQL table known as the Mail Sortation output table. The output table is created in the same SQL database as the source table (the table mapped in the preceding GenerateKeys or GenerateCorrectedAddresses task), with the same name as the source table, but with an additional table name extension. This option lets you modify that extension, which by default is “_MAIL_SORTATION”.
  
  
• System Options: This launches the Sort and Save System Options. Here, you can configure advanced settings for the available carriers as well as your Mailmark login details (required in order to perform a Mailmark sortation).
  
  
• Run Sort and Save Silently: Check this if you do not wish for Sort and Save to be displayed on screen when it is sorting your data.

Advanced Automation

In some scenarios, particularly where lots of jobs with different requirements are being run in succession, you may find it beneficial to implement a system that dynamically configures a job file that is permanently specified in the SSIS task, before running your SSIS package. This would prevent you having to manually configure the job file between sortations, making the whole Mail Sortation process far more time efficient. For detailed information on the layout of job files, please refer to the Sort and Save documentation, located in the SYSTEM folder of your Sort and Save installation.
In addition to dynamically configuring the job file, you could potentially load the source data into a generic SQL table that is permanently mapped in the GenerateKeys or GenerateCorrectedAddresses task that precedes your Mail Sortation task. This would further reduce the need for manual configuration between jobs.