Setting up secured SQL Enhanced Query exports

Use the steps to set up a secured SQL Enhanced Query Export.

To display the hidden fields, see the steps in Displaying the hidden SQL Enhanced Query Export parameters.

  1. Specify this information to create a scheduled task in the Job Scheduler:
    Task Type
    Aggregate Task
  2. After the task is created, click the Parameters button for the new task.
  3. Select Secure File Transfer Task and Export Query Enhanced Task in the Available Task Types column and click Add/Remove.
  4. Click Configure Selected Tasks.
  5. Click Parameters for the Export Query Enhanced Task.
  6. Configure these parameters for the Export Query Enhanced Task Parameters:
    Folder
    Specify the path for the file to be exported, using the absolute path with respect to the Job Scheduler or file server.
    File Name
    Provide a file name.
    Note: If required, include a date and time reference as part of the file name. For example, Filename_yyyyMMdd_HHmmss.
    Export Type
    Select an export type from the drop-down field. Options include CSV, XLS, and HTML.
    Write Headers
    Select this option to include headers in output file.
    Note: This option works with all formats.
    Selection Query
    Provide your own custom select query. The results of the selection query are used as a filter in the export query.
    Note: This field is not required. However, we recommend you provide a selection query for the export.

    See Selection and Export query examples.

    Batch Size
    Specify a batch size. The default is 20. The maximum allowed is 200.
    Export Query
    Provide your own custom export query. This is the primary query. This field is required.

    See Selection and Export query examples.

  7. Click Submit then click OK.
  8. Click Parameters for the Secure File Transfer Task.
  9. Click Add File Transfer Parameters and specify this information:
    SFTP Endpoint
    Select the SFTP Endpoint. To configure an endpoint, see Configuring a file transfer endpoint.
    Type
    Select Export. Export sends the file to an external system that is accessible by WFM.
    Source Folder
    Specify the name of the source folder/directory that contains the file(s) you want to transfer.
    Source File
    Specify the name of the source file(s) you want to transfer. You can use file masking to specify multiple files. Use the characters '?' and '*' to represent single or multiple wildcard characters. For example, *.csv will select all csv files and *-???.csv will select all csv files that begin with any amount of characters followed by a "-" character and then three other characters.

    You should note the following about file masking:

    • File masking is not permitted on file extensions.
    • If the file mask produces no matches, no file will be transferred when the job scheduler task runs and a log message will state that no matching file exists.
    • If the file mask produces multiple matches and the job scheduler fails to transfer any of the files, Job Scheduler will retry the file transfer the next time the task runs.
    • By default, files with the extension of .bak will not be transferred. This extension typically signifies a backup copy of a file.
    Destination Folder
    Specify the name of the destination folder/directory.
    Source Archive Option
    You can specify one of these options:
    • None: Once source files are copied to the destination file location, do nothing to the source files.
    • Archive: Once source files are copied successfully to the destination file location, move the source files to the Archive Folder.
    • Purge: Once source files are copied successfully to the destination file location, delete the source files.
    Archive Folder
    Specify a folder/directory for archiving files. This option is only used if Archive is selected from the Source Archive Option above.
    Note: The Source Folder and Archive Folder you specify should be on the same server and it is the user's responsibility to maintain the folder as per their business requirements.
  10. Click OK and then click OK.
  11. Specify this information:
    Log Folder
    Must be set to the directory where the logs are written. The directory path must not include the client and server names. The client and server names are inserted automatically at runtime.
    Log Level
    Set to None, error, warn, info, or debug as required.
  12. Click Submit and then click OK.
  13. Click Back.
  14. Click Save.