SIK - SQL Insertion - ACH
When not to use SQL insertion
Most of the time. We no longer recommend this feature for new/large users.
With the increasing power of CPU's, the speed of file imports rivals that of SQL Insertion.
Excel and csv file imports (based on an i9, 32 GB RAM computer):
60,000 records imported in 45 seconds. 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.
The SQL Integration feature enables other software systems to push data directly into a Treasury Software SQL Server-based (not SQL Express) 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'.
Leave the Transaction Code option to the default Populating CHK/SAV field only, unless you are a Financial Institution.
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 (ACH 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.
Due to our existing system processes, please note that not all transaction types and fields are available for insertion.
Can import as signed or absolute amounts, depending on whether or not you are supplying the Transaction Type codes. Regardless of sign, always include a decimal point as needed. Do not include commas or dollar signs.
Tip: Typically you will sign the amount as positive when you are collecting funds, and negative when you are sending out funds.
1. If Transaction codes are supplied in the ACH_CheckingorSavings field, enter all amounts in absolutes (positive).
The effective date, in MM/DD/YY format. Note: The month and day can be one or two digits, the year can be two or four digits.
International format? Use your local regional settings.
Name of receiving party (no apostrophes or special characters).
Identification (ID#) of receiving party. Optional field.
Bank Account number of receiving party
Routing number of receiving party
Standard Entry Class code (PPD, CCD, WEB, TEL, etc.). Does not support all SEC codes or codes with multiple addenda records (IAT, CTX). Optional field.
Numeric entry only. Either enter:
1. Simple checking (0) or savings (1) indicator
2. Transaction codes (22, 27, 32, 37, etc.)
Using multiple origination accounts on-the-fly (not profiles)?
For Company Name - import into fldOriginationAccountText
For Company ID - import into Comp_ID_Resend
Note: Yes, these are odd sounding field names, but they are not typos.
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 ACH 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.
No valid records? Remember that the software will filter out invalid ABA numbers. Make sure that you use valid ABA numbers in testing.
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.Text1, tblHoldingGLIntegration.ACH_Account, tblHoldingGLIntegration.ACH_Routing_ABA, tblHoldingGLIntegration.ACH_CheckingorSavings, tblHoldingGLIntegration.Class_3_Letters, tblHoldingGLIntegration.Date) VALUES
('123.45', 'ABC Company', 'ABC01', '88888888','063000047', '0', 'CCD', '12/31/2022')
Script for Multiple Origination Accounts On-The-Fly (no profiles)
INSERT into tblHoldingGLIntegration (tblHoldingGLIntegration.Amount, tblHoldingGLIntegration.Description, tblHoldingGLIntegration.Text1, tblHoldingGLIntegration.ACH_Account, tblHoldingGLIntegration.ACH_Routing_ABA, tblHoldingGLIntegration.ACH_CheckingorSavings, tblHoldingGLIntegration.Class_3_Letters, tblHoldingGLIntegration.Date, tblHoldingGLIntegration.fldOriginationAccountText, tblHoldingGLIntegration.Comp_ID_Resend) VALUES
('123.45', 'ABC Company', 'ABC01', '88888888','063000047', '0', 'CCD', '12/31/2022', 'XYZ Corp.', '998765432')
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.