SIK - SQL Insertion - ACH

Deprecated feature.  

 
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.

Overview

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.

Settings

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.  
Notes:  

--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.  

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.

Testing

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')

Production

Trigger the Software Integration Kit using the command line noted in the Automation tab.

General notes:

--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.