Scheduling Exports

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

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.

Server 

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.

Path

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 boxAny 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:

  • /banner/: subdirectory (optional)
  • SlateToSIS.txt: filename

The path supports variables to include date and timestamps. For example:

  • If the file is generated on 3/21/2018 at 2:57am the file will be named: SlateToSIS_20180321T025700.txt
  • Select Vars to see other variables supported in the path filename.

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.

Encryption 

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:

Notification   

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

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:

  • Delimiter: Set the delimiter used in the file. Options include: Tab (<tab>), Comma (,), Semicolon (;), Pipe (|), Caret (^), Bang (!).
  • Headers: Include or do not include the column names (such as the export names) in the first row of a delimited file.
  • Text Qualifiers: Text qualifiers (double quotation marks) should typically be included on delimited files, unless the destination system does not support these qualifiers. For example, consider the scenario of exporting a CSV file with the following columns:

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

  • Line Endings: Text files created on DOS/Windows machines have different line endings than files created on Unix/Linux. DOS/Windows systems use both a carriage return and line feed (<Cr><Lf>) as a line ending, while Unix systems use just a line feed character (<Lf>). Select the option that the destination system requires.
Excel Spreadsheet - This format exports a .xlsx Excel spreadsheet file.

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.

  • Important: If using the fixed-width format, a width for every export included in the data export query must be configured.

XML - This format exports the data as XML. Further XML settings include:

  • Doctype: Enter a doctype only if the file needs to be validated against a Document Type Definition (DTD) file.
  • Null Handling: Omit null elements or send xsi:nil=”true” for null elements.
  • Root Element: Provide the root tag for the file. If blank, the default setting is <data>.
  • Row Element: Provide a row tag for the file. If blank, the default setting is <row>.

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

  • For example, an eñe in Spanish (the “n” character with a tilde to become “ñ”) would be sent simply as “n”. Acute and grave characters will be sent without the accents, and all cedillas and circumflexes will be removed leaving their basic characters remaining.
Suppress Empty 

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.


Important

The SFTP server is set to Greenwich Mean Time. Your process should account for this, and it can be handled in two ways:
  1. 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.
  2. 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
Format File Type
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.
Delivery Windows

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.

Requested Weekdays

Exports can be scheduled for multiple days of the week. Files will be delivered during the configured delivery window, on the weekdays selected.

In this example, Slate will generate a file on Monday, Wednesday, and Friday during the Overnight delivery window.

Web Service

Slate supports Web service calls to queries with optional parameters. Select Edit Web Service to configure a Web service.

  • Service Type - Select the type of Web service that is needed.
  • Parameters - Configure the parameters (if necessary).
  • Password Protection (recommended) - Configure a username and password to password protect the Web service.

If parameters are added, use Custom SQL filters to specify how the parameter should be used:

Access the unique Web services URL in the Query Details section. Select the link next to Web Service.

All declared parameters are appended to the URL. In this example, the "created" parameter is appended in the generated URL. When calling the Web service, the value for parameters should be populated.

Was this article helpful?
7 out of 11 found this helpful