When to use:
Excellent for large simple data sets (importing over 200,000 records at a time).
When not to use SQL insertion:
Most of the time. We no longer initially recommend this feature for new/large users.
With our new Import Wizard and the increasing power of CPU's, the speed of the Import Wizard rivals that of SQL Insertion.
With the updated Import Wizard - Excel and csv file imports (based on an i9, 32 GB RAM computer):
60,000 records imported in 45 seconds using the Import Wizard (not SQL Insertion).
An ACH file of 240,000 was created in 2 minutes.
The downsides of SQL Insertion (complexity of setup, feature subset and bypass of QA controls) no longer outweigh the speed benefits. This feature is scheduled to be deprecated.
The SQL Integration feature enables other software systems to push data directly into a Treasury Software SQL Server (not SQL Express) based platform. This eliminates the need for 'pass-thru' files.
This feature requires an advanced knowledge and skill set with SQL Server. It is best used for high volume and highly automated environments.
We highly recommend printing out these instructions for reference.
1. You will populate data into the Treasury Software holding table. The holding table exists in each SQL Server database.
2. Then, when Treasury Software is triggered, it will move the data from the holding table into a permanent table and create an ACH file.
1. Start the Software Integration Kit.
Select the Automation tab > Software Integration Kit.
Select an unused process and click Add/edit.
2. Basic tab:
--Check the 'Visible' box (described later) and name this process (example Daily Processing)
--Select 'SQL Insertion'.****
--Click the 'SQL Insertion - Select Fields' link *****
Note: As the mapping templates are not used, you can leave the default.
3. SQL Insertion tab
After you click the 'SQL Insertion - Select Fields' link, you will be brought to the SQL Insertion tab
Select the top selection 'SQL - Insertion'
When complete, select the Automation tab.
4. Automation tab
Select a non-zero command line group number (typically 1).
Select an action from the drop down list (Positive Pay File - Create and Transmit).
Copy the command line noted below. You will use this line to trigger moving this process.
Click 'Save' when complete.
Note: Do not make any other changes in the other tabs.
Specifically, leave the default file format to 'tabular' and do not enable archiving.
Populating data into the Treasury Software holding table.
The following fields exist in each Treasury Software created SQL Server database.
--All fields are text fields and located in the table tblHoldingGLIntegration.
--All are mandatory unless otherwise noted.
--You can add records as often as you want
--The table is automatically cleared (deleted) each time you trigger the Software Integration Kit to run.
--Strip out special characters (especially apostrophes) in the Description/Name.
IMPORTANT: Issued checks should have negative amounts. Voided checks should have positive amounts.
Issued checks should have negative amounts. Voided checks should have positive amounts. Regardless of sign, always include a decimal point as needed. Do not include commas or dollar signs.
The effective date, in MM/DD/YY format. Note: The month and day can be one or two digit, the year can be two or four digit.
International format? - Use your local regional settings.
Name of receiving party
After you have performed your one-time setup, and then populated your data, you can test your work.
Start the Software Integration Kit, select the process, and then click 'Run'. Complete the import wizard.
Upon a successful import, the Positive Pay file will be automatically created. The file can typically be found (by default) at:
C:\Users\Public\Treasury Software\To Bank
Note: Your data that you populated will be cleared out of the tblHoldingGLIntegration.
Need a test SQL script to populate the table?
Tip: Start SQL Server Management Studio and click on 'New Query' (make sure that you're in the right database). Then copy the script below.
INSERT into tblHoldingGLIntegration (tblHoldingGLIntegration.Amount, tblHoldingGLIntegration.Description, tblHoldingGLIntegration.Date, tblHoldingGLIntegration.CheckNumber) VALUES
('-123.45', 'ABC Company','12/31/2016', '1001')
Trigger the Software Integration Kit using the command line noted in the Automation tab.
--This feature requires SQL Server (not SQL Express), and access to the Software Integration Kit.
--This integration is designed to be run in an automated fashion. If you trigger the import manually via the menu structure as noted in testing - once you are at the review records screen, the records have been removed from the temporary holding table.