Satuit Data Exchange (SDX) is a utility that lets non-programmers leverage the Satuit Bulk API to create and schedule automatic imports and exports of data. This allows for the ability to integrate data from third-party applications such as portfolio accounting systems into Satuit and also enables users to export data on a regularly scheduled basis to a file location where it can be consumed by another application such as a data warehouse.
When to Use SDX
SDX sits on top of the Bulk API which is based on REST principles and is optimized for importing or exporting large sets of data. You can use it to insert and update many records asynchronously by scheduling jobs. Export jobs can also be scheduled to take data from Satuit and output it to a network file location. These jobs are processed in the background and normally scheduled during off-peak hours. SDX is designed to make it simple to process data from a few thousand to millions of records.
How SDX Works
Data Exchange Credential Tool
A client tool that is installed on a network resource. This tool is used to specify the URL location, API Key, Username, and Password information for the service as well as the SMTP Server and email notification list for error messages. A second tab contains the Error log information.
Data Exchange Service
A service that is installed on the same network resource that the Credential tool is. This Service allows you to control Run Time-Frequency.
Imports
You import or update a set of records by creating an import job. The fields and what type of operation is being performed are determined by Templates created in the Import Tools. The Import tools are part of the Satuit System Administrator toolkit.
Exports
You export data by creating an export job. The fields included and column order for the file are determined by a Saved View. Saved views can be created through the advanced or custom search tools in Satuit.
Data Exchange Credential Tool
Requires Bulk API License. The tool needs to be installed with local administrator rights on an appropriate network resource with access and permissions to read and write to the import and export file locations.
-
URL: The Satuit site URL
- Example: https://www2.satuitcrm.com/companycode/
- API Key: bulk API key, this can be found in System Admin > System Configuration > Global Settings
- Username: The Satuit UserID for the API license
- Password: The password associated with the Satuit API license user
- Test API Connection: When you click on it, lets you know if the connection is successful
- SMTP Server: The email server used to send notification messages
- Notification List: Enter emails that you want the notifications sent to. Email addresses should be separated by semicolons
- Send Test Email: Will send one test email to all the addresses in the Notification list
Note
When it comes to notifications, the email addresses in the Import/Export schedules take precedence. If there is no email address configured in the schedule, then the email addresses in the notification list of the credential tool will be used. This is not a required field.
Error Logs
- Error Log Tab of Credential Tool: If there is a failure then an email will be sent to the addresses in the notification list, and in the email, there will be a log reference string for that particular job. Copy that log reference and paste it in the ‘Log Reference’ field and Click 'Get Details' and this will give you the stack trace of the error
- In Satuit: Under System Admin → Satuit Data Exchange → SDX Error Log, you have an error log grid that logs the data of the job error. Here also you have the log reference number
- On the server where the Credential Tool and the Service are installed, there is a text file that logs all the jobs that are being processed
- This can be located at C:\ProgramData\SatuitDataExchange\log.txt
- Once the text file reaches the size limit of 1MB it will create a new log file and archive the old ones
Data Exchange Service
- Has to be installed on the same server the credential tool is
- Default PassiveRunTImeFrequency is set to 1800 sec and ImmediateRuntimeFrequency is set to 5 sec
- This setting can be changed and the XML is in the ConfigFile (\DataExchangeService.exe.config)
- Below is an example of how this service works: For a scheduled import job, when at the scheduled time the service picks up the job and pushes it to the task queue, if there is another job waiting then it will pick that job up after 5 seconds if there is no other job to be picked up and if the import task is still in the task queue and not done then the service goes to sleep for the set passive time of 30 minutes after 30 minutes if the import task is complete in the task queue then if there is an exception file it will download it
- If there is an exception file then that file will be downloaded to the same folder the import CSV was in with a filename ( originalfilename_RunDate_YYYYMMDDTHHmm)
Note
In the event, the service isn’t running as a local admin account, make sure the following registry key has read permissions for the service account:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Satuit Technologies\SatuitDataExchange
Creating Import Jobs
To create an Import job, go to System Administrator, then Satuit Data Exchange, then SDX Configuration. From here choose 'Add' and select Import from the drop-down menu. Create a schedule name and description and specify the import file location. Next, specify the Import template to be used as well as the recurrence pattern.
- Imports: Multiple Notification Email Addresses can be entered separated by semicolons. The SMTP server must be configured in the Satuit Data Credential Tool to receive these notifications
- The full path for the import file location must be specified including the file extension,
Ex: c:\det\qalive.csv (here this c drive is the c drive in the server that the utility is in) UNC path can also be given (\\asc-qaiis01\BulkapiTestFiles\ qalive.csv) - File location must be accessible to the server that the utility is in, otherwise, the job will fail
- File location (either local path or UNC) can NOT include the following special characters:
-
- !
- @
- #
- $
- %
- ^
- &
- *
- (
- )
- +
- =
- [
- ]
- ;
- '
- "
- |
- ,
- <
- >
- /
- ?
However, we do allow:
- -
- _
- {
- }
- :
- \
- ~
-
- If an import file has a dynamic date in its file name then that can be specified in the import schedule with a date format enclosed in curly braces {}, example c:\det\qalive{mm-dd-yyyy}.csv
- Note that this date will be the current date of the server that the utility is in
Creating Export Jobs
To create an Export job, go to System Administrator, then Satuit Data Exchange, then SDX Configuration. From here choose 'Add' and select Export from the drop-down menu. Create a schedule name and description and specify the export file location. Next, specify the Saved View template to be used as well as the SSRS report to be used. Note you can choose to run the Saved View without an SSRS report. Finally set the recurrence pattern and Range of Occurrence.
- Exports: can be run just with only Saved View or with Saved View and a report
- If it is being run with only a Saved View then the default file format is csv
- If it is run as a report then there are options in the File format that can be selected
- Export file location can be dynamic just like the import schedules ex: E:\DETExports\testfilel{dd-mmm-yyyy}.csv
- File extensions and file formats:
- If export file location is specified as C:\testfile.csv and File format is csv then the file will be downloaded with filename testfile.csv
- If the file location is specified as C:\testfile and the file format is csv file will be downloaded with file name testfile.csv
- If the file location is specified as c:\testfile.csv and the file format is pdf then the file will be downloaded with file name testfile.csv.pdf
Move Source Files Post Processing
- SDX Configuration – Import window allows you to select the Move file after processing option an enter the Post-Processing file path
- When selected, exception reports will be delivered to the post-processing directory
Troubleshooting
-
Error log
- C:\ProgramData\SatuitData Exchange\log (may need to show hidden files in file explorer)
-
Restart service:
- Open Services
- Satuit Data Exchange (need admin rights to stop/start)
-
Change polling time:
- C;\DataExchangeService\DataExchangeService.exe.config
- Edit in Notepad++
- Change "PassiveRuntimeFrequency" value (in seconds)
- In the schedule jobs main grid ( System Admin > Satuit Data Exchange > SDX Configuration) Updated On column shows the UTC date so sometimes based on the scheduled time Updated On can be a date ahead