General Ledger (GL) Import
Data in the General Ledger (overhead expenses) of the Profitability module are imported via the SSIS GL import. Imported data includes, salary, transactions, expenses, and other sensitive data. The SSIS package can be run from the same server as Profitability, or it can be run remotely from a SQL server. The option depends on hardware and client preference, e.g. if you run the package locally on the web server then you need to have adequate RAM and CPU.
Importing the data can be done in one of the following ways:
You can also add individual GL accounts manually. Refer to instructions below.
Before setting up and running the SSIS GL Package, verify the following prerequisites:
- SSIS Time and Billing Import is done. GL Import relies on the data imported by the Time and Billing SSIS package
- Profitability module is configured correctly (two-factor authentication, permissions, and encryption password to be able to decrypt the data). Refer to Setting Encryption Password and Two-Factor Authentication
Option 1: Importing via SQL Server Management Studio
Setting GL (General Ledger) Import Parameters
The data (transaction, allocation, expense and SalarayComponent tables) on the Umbria schema for Profitability module is encrypted, therefore, you will need to run the Umbria Database Wrapper. The Database Wrapper extracts and decrypts a password from the Umbria Encryption Settings configuration file and applies it to the SSIS package.
Note: Prior to running the Database Wrapper, set up the database connection strings and the last run date:. The database wrapper does not store the password in plain text anywhere on the file system.
To set up DTSConfig file database connection strings:
- Open Windows File Explorer and navigate to the C:\Program Files\Prosperoware.Umbria\GLAccounting\folder.
- Open Settings (DTSConfig File).
- Enter connection string settings for Destination Database [DestinationDB] and Source Database [SourceDB].
To set the LastRun Date:
Open the Caliban.SSIScriptPaths table in SQL Server and set the LastRunDate to the desired value.
Note: If the SSIS GL import was not run previously, it should be set to '1800-01-01'.
(Optional & Recommended) To Stage a SQL Script:
This option provides the ability to set a date range for the data and thus, narrow the amount of imported data.
We recommend that only GL.Expense, GL.Income and GL.Transaction table scripts are customized.
- Open Windows File Explorer and navigate to the C:\Program Files\Prosperoware.Umbria\GLAccounting\folder.
- Open GLScripts\Load_Staging.
- Review the data ranges in the WHERE clauses of the query and update them accordingly. By default, all data will get imported without restrictions, which is a time consuming process.
To run the Database Wrapper:
You will need to run the Database Wrapper as an administrator.
- Open Windows File Explorer and navigate to the C:\Program Files\Prosperoware.Umbria\GLAccounting\folder.
- Open the Umbria.DatabaseWrapper\Umbria.DatabaseWrapper.exe config file and edit the necessary parameters (Umbria web application location, DTExec.exe file location, and DTExec.exe command line parameters).
- Run the Database Wrapper. It supplies the DTexec with the encryption password it retrieved from Umbria Encryption Configuration file and starts the import.
Option 2: Importing via the Profitability Module
Note: Prior to importing GL, make sure that the connection data to the GL database have been set. See instructions below.
To configure SSIS GL import settings:
- On the Profitability module, select Process .
- Click the SSIS GL Import Setting tab.
- In the appropriate fields, enter the following information:
GL Import Source Settings:
- SQL Server Name
- Database Name
- Run Package on: select Integrated or Sql
- User
- Password
SSIS Server & Path Information:
- Run Package on: select local or remote server
- Server Name
- Package Folder Path
- SQL Agent Proxy Name
- Click Save.
To import GL from the Profitability module:
On a monthly basis, the Finance department waits for the previous months’ books to be closed before running profitability. Once the books are closed and any FTE adjustments are made, complete the following:
- On the Profitability module, select Process > Process Engine tab.
Note: Prior to importing data, click the Pre-Flight Check button to allow the module to verify that necessary setup and configurations are done. A green check mark next to the field indicates that the setting is done and a red X indicates an error/setup was not implemented. Refer to Pre-Flight Check for more information.
- On the relevant period row, click the Action drop-down menu in the Actions column, select Import GL to import GL data for overhead expenses, revenue, FTE and timekeeper metadata (i.e. office, department, title by worker /period)
- You can re-import GL data. On the relevant period row, click the Action drop-down menu in the Actions column, select Re-Import GL .
After you import GL data, import the salary data. Refer to Importing Salary for information on next steps.
Refer to viewing data from the GL Import
