Once the query is configured to export the data values according to the specifications needed, configure how Slate exports data.
- If data is consumed using a Web service, use Edit Web Service to configure the Web service specifications. These settings are described in the Exporting Data with Web Services article.
- If the query will generate a file, use Schedule Export to configure the file delivery options.
Who Can Schedule?
Users must have the Administrator (All Access) role or the SFTP Access permission in order to access the Schedule Export resource.
Schedule Export and Options
Slate supports the automated scheduling of exports to an SFTP endpoint. These exports are constructed within Slate as drag-and-drop or custom SQL queries and they deliver the results to the SFTP endpoint on a scheduled basis. From any query within Slate, select Schedule Export to set the export parameters.
|Setting||Options & Explanation|
Active - Automatically deliver a file during the requested delivery window.
Inactive - The file is not automatically created. Manual file creation is possible using Run to Browser and Run to SFTP if a file path has been configured.
Technolutions - Deliver files to the Technolutions SFTP server.
Custom - Slate supports sending a scheduled export to a remote SFTP server, but this practice is generally discouraged, since the export process fails if the remote SFTP server becomes unavailable.
The path specifies the filename (that may optionally include a subdirectory) for the results when sent to the SFTP endpoint. A file extension (such as .txt or .csv) should be included in this path if one is required (this is typical). The path supports path variables to include date and timestamps. Typically, files that are cumulative exports (sending out all data every time) should not include a timestamp, while incremental or differential exports should always include a timestamp to prevent overwriting files and "missing" information.
text box - Any file sent to the Technolutions SFTP server is automatically sent to the /outgoing/ directory. The path specifies the filename (optionally including a subdirectory) for the results. A file extension (such as .txt or .csv) should be included in this path if one is required (which is typical). For example:
The path supports variables to include date and timestamps. For example:
Typically, files that are cumulative exports (exporting all records every time) should not include a timestamp, while incremental or queue exports should always include a full timestamp to prevent overwriting a file that has not yet been picked up.
Secure Transfer - SFTP automatically provides a secure file transfer capability. If exporting data to a remote server that uses standard FTP (rather than SFTP), using the Encryption setting allows the file to be encrypted.
PGP Encryption - If PGP encryption is desired, enter (or paste) the Public Key that Slate should use to perform the encryption. For example:
No notifications - Do not send any email notifications.
Failures only - Send a notification to the configured email addresses when a file cannot be processed.
Failures and late deliveries - Send a notification to the configured email addresses when a file cannot be processed or when a file is delivered after the scheduled delivery window.Successes, failures, and late deliveries - Send a notification to the configured email addresses for all file statuses.
Delimited (tab-delimited, CSV, etc) - A delimited file exports the text with the columns separated by a particular character, such as a tab (a tab-delimited file), a comma (a CSV file), or other standard delimiters.
Further Delimited settings include:
“First”, “Last”, “A, B”
If sending that row without text qualifiers (without doing any value corrections), it would appear like this:
First, Last, A, B
With this data, we cannot differentiate between B being a part of the third column or being a separate fourth column. Including text qualifiers allows this differentiation.
When sending with text qualifiers, the quotation marks indicate that the text within may include the delimiter but that it should not be regarded as such.
If exporting data without text qualifiers, the tab-delimited format is the most versatile option. The tab character is rarely included in the data being exported, while the comma is much more routinely used.
Fixed Width - This format exports a file where the columns have a fixed width and all fields maintain a fixed alignment. This is a special format and should only be used if required by the destination system.
XML - This format exports the data as XML. Further XML settings include:
Document Export options - This is a special format used for exporting documents.
|Encoding||Unicode (utf-8) - The exported file uses a Unicode format and includes a byte order mark (BOM) at the start of the file. If the destination system does not support Unicode encoding, these characters may appear as gibberish, as may other characters throughout the file if they contain accented characters. With utf-8, all diacritic characters are preserved. Western European (iso-8859-1) - The exported file uses a format that will preserve many of the Western European diacritic characters.
ASCII (us-ascii) - This export option will decompose all diacritics (typically used when sending to an older system or a system where diacritics should be removed).
Allow empty files - An export file is generated even if the file is empty.
Suppress empty files - An export file is not generated if the file is empty.
ImportantThe SFTP server is set to Greenwich Mean Time. Your process should account for this, and it can be handled in two ways:
- Add a portion to the pickup script to remove processed files from the SFTP, so that only unprocessed files remain on the SFTP site. The pickup process can then pick up all files regardless of timestamp.
- Account for the time difference within the pickup script, to only pick up the desired file falling within the correct parameters.
Format and File Types
|Delimited||A delimited file exports the text with the columns separated by a particular character, such as a tab (a tab-delimited file), a comma (a CSV file), or other standard delimiters.|
|Excel 2007-2013||This format exports an .xlsx Excel spreadsheet file. Each "select" statement is exported into an individual worksheet within the same workbook.|
|Fixed Width||This format exports a file where the columns have a fixed width and all fields maintain a fixed alignment. This is a specialty format and should only be used if the destination system requires it. Within Query Betta, the width of a data point can be configured by editing the export and entering a value in the Width field. For a custom SQL query, the width can be configured by including a ":width" in the selected column names. For example, if exporting "p.[first] as [first name], p.[last] as [last name]", you can modify the select clause to include the field width "p.[first] as [first name:50], p.[last] as [last name:75]," which would truncate the first and last names at 50 and 75 characters, respectively, and pad them with whitespace at the end if their values are null or fewer than the specified width.|
|XML||This is a specialty format and requires that the SQL query be constructed using the "FOR XML PATH" syntax in SQL Server.|
|Document Export||This is a specialty format and requires a specific series of SQL statements that can be used to export materials and documents within Slate as PDF, TIFF, and JPEG images.|
Exports can be scheduled in multiple delivery windows, but we recommend only selecting the Overnight: 2:00am–4:00am window unless you have a strong use case to include other windows.
Although most exports only read data from the database rather than writing to it, they do add to the load on the database and they have the potential to impact performance.
The delivery window times are in Eastern Time and are presented as two-hour blocks. For example, if the 12:00pm-2:00pm window is selected, we will engineer our systems to deliver the file sometime between 12:00pm and 2:00pm. If the file has to be generated after 2:00pm, it will be treated as a late delivery for notification purposes.
If polling our SFTP servers to pick up the file, poll after the end of the delivery window. Many exports will complete long before the end of the delivery window, but exports may need to be shifted within the window for maintenance or load management reasons.
We have carefully selected these delivery times based upon an evaluation of database loads within Slate. For example, there are no delivery windows between 5:00pm and 8:00pm, as this is a window commonly used for decision releases.
In this example, Slate will generate a file on Monday, Wednesday, and Friday during the Overnight delivery window.
Slate supports calling a query as a web service, or pushing to a remote web service on a schedule. Refer to the Exporting Data with Web Services article for information on these configurations.
Monitor Export Status
There are two ways to monitor the status of scheduled exports:
- Setting a notification in the Schedule Export settings of your query, as described earlier in the article.
- Reviewing the Job Activity Monitor tool, located on the Database page. The Job Activity Monitor displays the history and results of all scheduled jobs.