To set up the ability to send data files to the SFTP, we will need the attached document completed (linked below text in this article). If you are in a current implementation, please return this to your Consultant or Project Manager. If you are requesting the setup post implementation, Submit a ticket to our Support Team requesting this setup and provide this document with that ticket.
The loading of Fund Manager and Project Effort data templates is handled via an SFTP File (Secure File Transfer Protocol). SFTP creates a secure connection between your server and the SFTP server at Cayuse. This connection typically goes through port 22. SFTP then uses the SSH protocol (for a more secure connection) to encrypt the requested file and transfer the file to Cayuse.
To properly set up this connection Cayuse requires the following information as early in your implementation as possible.
If you are already Live and want to begin importing data through an SFTP, review the information below, complete the Customer SFTP Request Form at the end of this document and send to our Support Team.
Required Information
Please have your IT Resource team Provide us with your Public SSH Key.
Helpful Links:
File Requirements
- Required: Data .csv templates must be sent to the Cayuse SFTP folder in one single zipped file.
- Optional: Encrypted password protection
Our current process requires single key encryption managed by tools such as WinZip. These tools typically use AES encryption, which is an industry standard encryption method. WinZip supports AES encryption in two different strengths: 128-bit AES and 256-bit AES.
The Zip File can be sent with or without a password. The password can be any amount of characters and is alphanumeric. There are no set parameters around the password.
Zip Files MUST be:
- Sent to the root directory
- .zip extension
- Named as follows:
- CUSTOMER CODE_”anything_you_want to name in this area”_yyyymmdd.zip
Other Considerations
- The Order in which the files are zipped is not relevant as our data load package will load them in a specific order.
- A single zip file can only contain only one of each .csv data file. Only One Zip file can be sent daily.
- Multiple files with the same name will cause the load to fail from that point on.
- Once the files are loaded they are removed from the SFTP folder.
Data Load Failed Notification:
What “group” email address(es) do you want to use for notification of “Load Failed”?
A notification will be sent to this email address stating “Your Data Load Failed. Please contact support/PSC for further assistance.”
A Data load will fail for the following circumstances:
- An invalid .csv File Format
- Multiple zip files are in SFTP on a given day
- Multiple .csv files of the same file name are in the zip folder more than once
Data Load Successful Notification:
What “group” email address do you want to use for notification of a “Load Successful”?
What do you want the subject line to read on Success Load emails?
For example: Cayuse Datafeed Succeeded - PROD
The body of the email will include a summary (see below for sample email image) for each .csv file in the zip file with the following total numbers of records:
- In your .csv file
- Inserted
- Updated
- Ignored
Please note: Ignored records can occur within a successful load on all or just some records (rows of data) within the .csv file due to several reasons. Some examples of those are:
-
Missing required field data
- Ex. No data in RefNum2 column
-
Incorrect Data Type value
- Ex. Text format in a date field
-
Invalid data within the file
- Ex. Fund code doesn’t exist on the master Fund Code Table
- Data within a single field with a comma (,) as that will separate this data into the next column and cause the data to be incorrect on that row
If an error occurs, a report will be generated and sent to your SFTP into the folder titled “Error”. The email will provide the name of the file that is generated for that day’s load.
Example of Data Load Successful Email Notification:
The “Please Note: Validation Report Generated with File Name: DataFeedValidation_ YYYYMMDD.XLSX” will only be in the summary mail if there are Ignored Records on that given day’s data load.
****THE ATTACHMENTS IN THE AUTOMATED EMAIL WILL BE SENT IN A ZIP FILE THAT CURRENTLY MAY REQUIRE THE FOLLOWING APPLICATION TO BE USED TO UNZIP THE FILE. IF YOU SEND YOUR FILES PASSWORD PROTECTED, YOU WILL HAVE TO ENTER THAT SAME PASSWORD TO OPEN THE ERROR FILE AS WELL.
IF YOU HAVE A MAC, GO TO YOUR APP STORE AND DOWNLOAD:
IF YOU HAVE WINDOWS, GO TO THIS WEBSITE TO DOWNLOAD:
https://www.win-rar.com/download.html?&L=0
An example of an Error Report is as follows:
REQUIRED INFORMATION
Scheduler Time:
Data files will be picked up 7 days a week.
Please note: An email will be generated every day - regardless if there are any files sent that day.
What time do you want our scheduler to pick up and load your data files? ( PT - Pacific Time
Required File Naming convention for data files accepted in the automated Package Load
The file names MUST be exactly as listed below to have your files picked up by the data load package.
Be sure to ask your Cayuse Consultant what your Customer Code is to add to the beginning of each file name.
File names ARE case sensitive - therefore Customer Code should be in ALL CAPS and the rest of the letters in the file must be upper/lower as designated below.
Required File Naming convention for data files accepted in the automated Package Load
- The file names MUST be exactly as listed below to have your files picked up by the data load package.
- Be sure to ask your Cayuse Consultant what your Customer Code is to add to the beginning of each file name.
- File names ARE case sensitive - therefore Customer Code should be in ALL CAPS and the rest of the letters in the file must be upper/lower as designated below.
Fund Manager Non-Transaction Data Files:
Fund Manager Foundational Data Load
- CUSTOMER CODE_Object_Set_Object_Codes_YYYYMMDD.csv
- CUSTOMER CODE_Cost_Centers_YYYYMMDD.csv
- CUSTOMER CODE_Flex1_YYYYMMDD.csv
- CUSTOMER CODE_Flex2_YYYYMMDD.csv
- CUSTOMER CODE_Program_YYYYMMDD.csv
- CUSTOMER CODE_Vendor_Code_YYYYMMDD.csv
- CUSTOMER CODE_Indirect_Cost_Type_Code_YYYYMMDD.csv
- CUSTOMER CODE_Indirect_Cost_Object_ Code_YYYYMMDD.csv
- CUSTOMER CODE_Funds_YYYYMMDD.csv
- CUSTOMER CODE_Cost_Centers_In_Funds_YYYYMMDD.csv
Fund Manager Personnel Data Load
- CUSTOMER CODE_Fringe_Benefit_YYYYMMDD.csv
- CUSTOMER CODE_Personnel_Title_YYYYMMDD.csv
- CUSTOMER CODE_Pay_Period_YYYYMMDD.csv
- CUSTOMER CODE_Salary_YYYYMMDD.csv
- CUSTOMER CODE_Salary_Distribution_YYYYMMDD.csv
Fund Manager Chartstring Security Data Load
- CUSTOMER CODE_Chartstring_Security_YYYYMMDD.csv
Project Effort Non-Transaction Data Files:
Effort Foundational & Personnel Data Load
- CUSTOMER CODE_Certification_Period_YYYYMMDD.csv
- CUSTOMER CODE_Pay_Periods_YYYYMMDD.csv
- CUSTOMER CODE_Role_Rule_Types_YYYYMMDD.csv
- CUSTOMER CODE_Personnel_YYYYMMDD.csv
- CUSTOMER CODE_Fund_Effort_YYYYMMDD.csv
- CUSTOMER CODE_Committed_Costshare_YYYYMMDD.csv
Additional Effort files ONLY IF you have not implemented the Fund Manager Application
- CUSTOMER CODE_Object_Set_Object_Codes_YYYYMMDD.csv
- CUSTOMER CODE_Funds_YYYYMMDD.csv
- CUSTOMER CODE_Transactions _YYYYMMDD.csv
Fund Manager Transaction Data Files:
Transaction Data Load
Option 1: One Data file for all Transaction Types
CUSTOMER CODE_Transactions _YYYYMMDD.csv
Option 2: Up to Four different files for the various Transaction Types
- CUSTOMER CODE_Expenses_YYYYMMDD.csv
- CUSTOMER CODE_Expenses_Payroll_YYYYMMDD.csv
- CUSTOMER CODE_Encumbrance_YYYYMMDD.csv
- CUSTOMER CODE_Budget_YYYYMMDD.csv
Will you send all transactions (payroll, expense, encumbrance and budgets) in ONE daily file or in multiple files:
- Option 1: ONE file (Required if you have only the Project Effort Application without Fund Manager)
- Option 2: MULTIPLE files
CAUTION: It is imperative that you follow all of the above requirements for sending your import files. A Data File will be ignored (not loaded) and not listed on the summary grid in the email for the following circumstances
- An invalid .csv or .zip file name
- Ex. misspelling, added or missing characters, for example the use of - instead of _
- Incorrect date in the .csv or .zip file name
- Ex. file name has “_20230523” and your scheduler does not pick up the next file until 5.24.23 pacific time. The file name date must match the date of the next scheduled pickup.