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.
-
Specify this information to create a scheduled task in the Job
Scheduler:
- Task Type
- Aggregate Task
- After the task is created, click the Parameters button for the new task.
- Select Secure File Transfer Task and Export Query Enhanced Task in the Available Task Types column and click Add/Remove.
- Click Configure Selected Tasks.
- Click Parameters for the Export Query Enhanced Task.
-
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.
- 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.
- Click Submit then click OK.
- Click Parameters for the Secure File Transfer Task.
-
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.
- Click OK and then click OK.
-
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.
- Click Submit and then click OK.
- Click Back.
- Click Save.