Importing and Syncing Data

Set Up Your Data Source

Warning: Set up the Data Source with this method ONLY if you are planning to import using the Umbria UI functionality rather than importing through the package manually or through a scheduled task. This requires SSIS to be installed on the web server.

Configure the SSIS Package

SSIS uses a package file (dtsx), multiple config files, and a batch bat file to run the import. You will need to modify the config and bat files.

SSIS Package and Related Import Scripts

All files required for importing time and billing data are installed to ...\Prosperoware.Umbria\SSIS.

File
CommandLineRun.bat
ImportTimeBillingData.dtsx
processdb.xml
Settings.dtsConfig
SSAS_Settings.dtsConfig
UpdateCubeDatabase.dtsx

SSIS Tables

The following tables in the Umbria database are used by the ImportTimeBillingData SSIS package during an import.

Table	Description
Caliban.SSISScriptPaths	Specifies which import scripts to run and the order in which to run them.

You can manually change these times and whether scripts are enabled or not (or one of the other options via SQL or within the UI). Go into the Admin Panel. If you are using CMS Aderant, go to Aderant Expert Settings (as shown in the screen shot below). If you are using Elite Enterprise or 3E, go to Elite Enterprise Settings. Under SSIS Sync, you will see a list of script names, last sync times, and whether each script is enabled. Double-click a script to edit it.

There are multiple steps that occur with each sync:

1. Staging tables get their indexes removed and are truncated.
2. The SQL script files are brought in.
3. A loop goes through the Staging script files to make sure files are present.
4. For each Staging script file, we load the data from the time and billing system into the appropriate Staging table. We bring all data into Staging.
5. Indexes are added to the Staging tables (for performance).
6. We get the table names from Umbria that need to be imported.
7. Another loop goes through the Production script files to ensure they are present.
8. Each enabled script then loads data into Production.
9. Finally, a built-in validation compares the count of rows imported into the Staging tables to the count imported into Umbria.

Caliban.JobStepsDetails gives a lower level view of the JobSteps, including the run time for each of the job step portions. For example, you can see where we are truncating the Staging.[Matter] table at the beginning of the process, or when we are processing Employee.sql on the Production load step.

If there happens to be an error during the import, the error is captured in 1 or 2 tables, depending on the error and the part of the import. For specific errors on scripts or import inserts, the error is in the SSISJobStepDetails folder on the line that it failed on, as well as in the SSISErrors folder. SSIS errors, SQL errors, and system failure errors that occur before the sync actually starts are found only in the SSISErrors table.

The SSISErrors table contains information on the error date, package name that was running, source name (step of the import), task, error code, and error description.

Caliban.SSISDataValidation contains the validation count results for the different tables during the import, with the amount processed and not processed for comparison.

Modifying SSIS Configuration Files

Make sure the SSIS prerequisites from the Umbria installation are installed. By default, we ask that SSIS is installed on the Web server and SQL server, but some firms also install it on the Agent server — this gives flexibility when running the import from multiple machines or on the one with the best resources available.

To modify SSIS configuration files:

1. Make any modifications to the Staging files if needed, and copy/replace all the files from your relevant time and billing Staging table to the Load_staging folder. Make any changes to the update_production scripts if needed as well.
2. Either in SQL from the Caliban.SSISScriptPaths folder or from the Umbria UI, configure and enable the SSIS scripts you want to import.
3. From the root level of the NewSSIS folder, edit the commandlinerun.bat file:
   1. Right click and open with Notepad or a similar tool.
   2. Make sure that the path of the DTExec file is correct:
      1. Those with 64 bit SQL, need to remove the x86 part of the path.
      2. The number of the folder changes depending on the version of SQL installed.

         SQL Server Version Folder Number 2016 130 2014 120 2012 110 2008 100

   3. Change the path for the ImportTimeBillingData.dtsx file (e.g., C:\Program Files\Prosperoware.Umbria\NewSSIS\ImportTimeBillingData.dtsx).
   4. Change the path for the Settings.dtsConfig file (e.g., C:\Program Files\Prosperoware.Umbria\NewSSIS\Settings.dtsConfig) and click Save.
4. Edit the Settings.dtsconfig file from the same root level folder.
   1. Right click and open with Notepad or a similar tool.
   2. On the second line, the DestinationDB connection string, put the server name in for Source, the Umbria database in for the initial catalog. If you use SQL authentication, modify the integrated security line and add in user= and password= portions.
   3. On the third line, the SourceDB connection string, put the server name in for Source, the time and billing system database name to be synced from for the initial catalog. If you use SQL authentication, modify the integrated security line and add in user= and password= portions.
   4. On the fourth line, the SourceFilePath connection string, point to the file path of the SSIS scripts (e.g., C:\Program Files\Prosperoware.Umbria\NewSSIS\SSISScripts\).
   5. Setup the email fields only if you want to use the feature and it is enabled in SQL, then click Save.

Setting Up Daily Incremental Syncs

Below are high-level points regarding how the daily incremental syncs work.

• We need an always-available temp table on the same SQL server as the firm’s time and billing database (CMS, Elite, 3E) with read/write access. Optimally, the firm should allow use of their time and billing database for this purpose; otherwise it needs to be on a database on the same server.
• We append to this table rather than recreating it every day. This way, we always pick up changes to existing matters in the Umbria database.
• We add a script to pull all the matters, within scope, for each client and add matters to the temp table each day.
• We modify our client, matter, time entry, matter invoice, invoice payment, and internal costs scripts to join on the temp table to pull data for matters.

Confirming Import Status

Initiating an SSIS Import

An SSIS import can be started from either the command line or directly within Umbria. Importing through Umbria is a slightly slower process, as it uses IIS Manager.

Tip: When an import is started from the command prompt, the import will continue even if you log off of your computer. However, if importing through Umbria, you are unable to log off the computer.

Service Agents

Import Background Tasks

This section covers what processes run in the background during the initial import of data as well as during incremental updates.

Indexing:

Umbria creates indexes for searching and for user interface interaction with the data, and also sets security on items.

• If Command SQL is enabled on imports, then indexing and update stats run automatically.
• For incremental imports, the SSIS package and indexing look at the checksum columns (a combination of fields imported) and compare them with the columns calculated on import. If there are any discrepancies, the import and indexing run again.

Update Statistics:

Refer to the Update Statistics section for more information about this process.

Logging

Update statistics are logged in two places: the Command Log and Application Log.

• Command Log: An entry appears for updating statistics. The Log tab contains the log traces on progress. This log stops populating after approximately 100 log entries; see the Application Log for more complete information. Possible statuses include the following:
  • Executing: stats process is running.
  • Canceled: a user canceled the process.
  • Interrupted: a system-level process, connection/locking issue, or timeout caused the sync to stop.
  • Success: job completed successfully (there may still be errors on individual entries in the log).

• Application Log: An overview of each log entry in the command log is logged here until the completion of the process. All entries are shown.

To resolve this error, there are two options:

Use the Windows Command Line

1. Change the file path of DTExec from 64 bit to 86 bit in the batch file i.e. CommandLineRun.bat
2. The default path in the batch file is C:\ Program Files \Microsoft SQL Server\130\DTS\Binn\DTExec.exe change this to C:\ Program Files (x86) \Microsoft SQL Server\130\DTS\Binn\DTExec.exe

OR

Use the SQL Server Integration Services Package

When you configure SQL 2016 job for import using the SQL Server Integration Services package, check the box for Use 32 bit runtime

There are two options available to import data from your billing system into Umbria using the Umbria web interface.

1. Data can be imported during installation from the Umbria Web Import page (http://<Server Name>/import)OR
2. Data can be imported post installation from the Administration>Admin node>Import Jobs node

The instructions to update data from both the pages are similar. Please refer Admin>Import Jobs page for complete details to import Time & Billing information via the Umbria UI.