Calyx Point Central
- Heather Day
Note: Someone with IT capabilities will be required to set this up on the customer side.
The Calyx Point Data Server interface is comprised of three basic components:
SS-PDS-Setup.exe – the program used to produce the configuration file for the SSPDSProcess program and to check and/or create the necessary SQL database and tables.
SSPDSProcess.exe – the main program that extracts data from the PDS SQL database, exports to a .csv file and FTP’s the file to Experience.com, all on a scheduled process.
Experience.com Database – MSSQL database that is created on the main PDS SQL server to maintain those files that have previously been processes and to which Loan Officer to eliminate duplicate sends – See Appendix A for details.
Installation
Installation of the package can be performed by any personnel that have access to the PDS server.
To install:
Create a folder on the PDS server to hold the package, such as C:\Experience Download the .zip package from the provided URL
Unzip the contents of the package into the folder created above
Go to the folder and double click on the SS-PDS-Setup.exe program which will produce the following screen:
When it first appears, the Save and Exit button will not be visible. It will appear after the SQL connection has been checked and verified.
Fill in the required data on the form, select the proper Start Date (the system will send data daily starting from the selected date) and Trigger Date (this is the date within Calyx that the system checks for any new entries to be sent – any file with a date prior to current date and after the designated start date that hasn’t been sent prior to Experience.com will be picked up and sent).
Once everything is supplied, click on the Check SQL Connection button. If all is good, the Save and Exit button will appear. Once clicked, if this is a first time install, the proper database will be created alongside the PDS database and the 4 necessary tables will be created. If this is a rerun, the system will check for the tables and not attempt to recreate.
Running this program will create a Settings.cfg that contains all the information necessary to run the SSPDSProcess program. All passwords saved in the Settings.cfg file are encrypted.
Once this has ran successfully, all that is needed is to test the system with Social Survey to ensure that the file is properly moving to the FTP folder and then schedule the task on to the Windows task scheduler to run daily.
We recommend scheduling the task to run daily in the morning to pick up all prior days activity. You can do this using the standard Windows Task Scheduler under the Administrative Task and please schedule it using either administrative success or with a user that has full access to the folder created during the installation.
Appendix A – MSSQL Script for Database/Table Creation
(for display and notational purposes only -
embedded in the program and will execute automatically on system install)
CREATE DATABASE SocialSurvey
CREATE TABLE [dbo].[SS_Processed](
[SS_ProcessedID] [int] IDENTITY(1, 1) NOT NULL,
[FileID] [nvarchar](255) NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_SocialSurvey] PRIMARY KEY CLUSTERED
(
[SS_ProcessedID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[SS_LoanOfficer](
[LoanOfficerID] [int] IDENTITY(1,1) NOT NULL,
[iUserID] [int] NULL,
[FullName] [nvarchar](255) NULL,
[Email] [nvarchar](1024) NULL,
[Active] [bit] NULL,
[DateCreated] [datetime] NOT NULL,
CONSTRAINT [PK_LoanOfficer] PRIMARY KEY CLUSTERED
(
[LoanOfficerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE[dbo].[SS_LoanOfficer] ADD CONSTRAINT
[DF_SS_LoanOfficer_DateCreated] DEFAULT(getdate()) FOR [DateCreated]
CREATE TABLE [SS_LoanOfficerName](
[LoanOfficerNameID] [int] IDENTITY(1, 1) NOT NULL,
[LoanOfficerID] [int] NULL,
[FullName] [nvarchar](255) NULL,
CONSTRAINT [PK_LoanOfficerName] PRIMARY KEY CLUSTERED (
[LoanOfficerNameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[SS_DebugMessage](
[DebugMessageID] [int] IDENTITY(1, 1) NOT NULL,
[DebugMessage] [nvarchar](2048) NULL,
[DateCreated] [datetime] NULL,
CONSTRAINT [PK_DebugMessage] PRIMARY KEY CLUSTERED
(
[DebugMessageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Average setup and test time is estimated to take 1-2 hours.