Picking up SFTP files from subfolders and reading IDs from PDF filenames in Orchestrate

Orchestrate does not read SFTP; the integration system does

An orchestration never opens an SFTP connection to fetch files. The pickup belongs to the integration system that launches your Workday Integration System orchestration: a Document Retrieval service step on its integration business process collects the files, and the orchestration then reads them as documents on the integration event.

The only SFTP action in Workday's list of Orchestrate file storage connectors is Upload File (Password Auth), which sends a file or a UTF-8 string to a server. Nothing in that list lists a directory or downloads from SFTP. So the design question is never "which Orchestrate step reads the folder" but "how does the integration event end up holding the right files".

Set up the pickup on the integration system

This assumes the app is deployed and the integration system already points at the orchestration (template Orchestrate Integration Template, with the Orchestration Name and Application Reference ID integration attributes filled in). The tenant-side steps for that are in getting started with Orchestrate for Integrations.

1. From the integration system's related actions, select Business Process > Create, Copy, or Link Definition to give it its own integration business process.
2. Edit the definition. Add a step of type Service, specify Document Retrieval, and give it an order that puts it before the Service / Fire Integration step. Workday's guidelines say the order is critical: for an inbound integration, Document Retrieval must come before Fire Integration.
3. Configure the retrieval on the new step: the external location it reads from and the file name it should match. Workday's troubleshooting for an inbound run that loads nothing starts there, checking that the file exists at that location and that its name matches the configuration. Configure it separately per tenant, because a Sandbox and Production pickup rarely share an endpoint.
4. The retrieval and fire steps run as the integration system user. Workday's guidelines list Modify / Put on Integration Build and Integration Configure for service steps; the orchestration's own API calls need whatever domains the work requires on top.

Workday publishes hard numbers for this route. The Document Delivery and Retrieval services handle up to 5,000 integration documents per integration run, 1 GB for a single file and 3 GB for all files in the run (compressed). A single FTP, SFTP or FTPS request can run for 6 hours. If a nightly drop can exceed 5,000 PDFs, split it by schedule or by folder before you build anything else.

What to do when the files sit in subfolders

Workday's published documentation for the retrieval service describes a source and a file pattern; it does not describe descending into subfolders. Do not design on the assumption that one retrieval collects a whole directory tree. Prove it in your tenant with a test folder first, and if it does not pick up nested files, use one of these two approaches.

Flatten at the source (recommended). Ask the sending system to write every file into a single drop folder and move the distinction (business unit, document type) into the filename, for example HR_21001_OfferLetter.pdf. The orchestration already parses the filename, so one more segment costs nothing, and the retrieval stays a single, simple configuration.

List and fetch folder by folder in Workday Studio. When the folders are created by the sender and cannot change, Studio's sftp-out transport can do what the retrieval step does not. Its list method returns the files at an endpoint (readable in MVEL through ftp.list()), get fetches the first file that matches the Input File Pattern, and the endpoint takes the form sftp://server/directory. Studio does not delete a file after get; a separate delete call does that. Keep the folder names in an integration attribute or map, loop over them in the Studio assembly, and attach what it fetches to its own integration event. Then launch it from the orchestration with a Trigger Integration step. The orchestration pauses while the Studio child runs, and when the child calls back the step outputs its status, integration messages and event documents. That pattern is covered in calling Studio integrations from an orchestration.

Read the retrieved files inside the orchestration

Retrieved files are identified by document tag. Workday's Trigger Integration reference shows the convention from the other side: its Add "Retrieved" Workday Tag option makes a child integration treat a passed document as if it had come from the retrieval service. Workday's own App Catalog sample for inbound worker photos (Worker Image Upload Inbound) filters on that tag through an integration attribute of type Document Tag, so the tag can be changed without editing the orchestration.

Read the tag list from the attribute, then loop over the matching document IDs. With an integration attribute named Input Document Tags:

Create Values  ReadSettings
  docTags = intsys.referenceAttributeValues("Input Document Tags", "Document_Tag_Name")

Loop  FileLoop
  Data Set: documents.labels(data.ReadSettings.docTags).getDocumentIds().iterator()

Inside the loop, data.FileLoop.item is one document ID. Two expressions give you what you need from it:

documents.documentId(data.FileLoop.item).getFileName(0)   // the filename
documents.documentId(data.FileLoop.item).get()            // the file content, as Data

The documents functions also filter and sort without a loop: names(...) keeps documents with the given filenames, minFileSize(...) drops empty files, and sortFileNameAsc() gives a stable processing order. getDocumentCount() tells you how many files the event holds before you start, which is the number to log first.

If the sender PGP-encrypts the PDFs, decrypt the content with the Data member function pgpDecrypt(privateKey, requireMdc) before anything uses it. Key and handshake failures on the server side are in SFTP, PGP and SSH errors.

Cut the ID out of the PDF filename

The Orchestrate String member functions are enough for every common naming scheme; none of them needs a regular expression. In the table, name stands for the filename expression from the loop:

Strip the extension with substringBeforeLast(".") rather than substringBefore(".") whenever a name can contain more than one dot. Use the Loop's Filter field to let only PDFs in, name.endsWithIgnoreCase(".pdf"), because endsWith is case sensitive and .PDF is common from scanners.

Then test the result before you call any API with it. isNumeric() returns true only for Unicode digits, so it rejects 21001a and -21001. Where your IDs are not purely numeric, compare against the pattern you expect with startsWith, length and contains. For any file that fails, add an Add Integration Message step with severity WARNING and the filename in the summary, and skip it. The integration then finishes with a list of exactly which files need renaming, instead of failing on the first bad name.

Match the IDs to workers in one call

Calling Workday once per file is the slow way. The App Catalog photo sample collects the IDs from all filenames, runs a single WQL query on workersForHCMReporting with employeeID IN (...), and then pairs each file with its worker in a Join Loop keyed on the ID. A file whose ID matches no worker falls out of the join, so log the unmatched ones separately; how the Join Loop treats unmatched rows explains where they go.

From there the PDF goes wherever the design needs it, for example to the worker's documents through the Staffing web service's Put_Worker_Document operation, with the file content from documents.documentId(...).get().

Prove the run did what you expected

Launch the integration with Launch / Schedule Integration, open the View Background Process page and read the Messages tab. Three messages make the run checkable: one INFO message with getDocumentCount() at the start, one WARNING per rejected filename, and one INFO message per processed file with the filename and the ID you extracted. Retrieved count minus rejected and unmatched files must equal processed files.

Then read the result back in Workday for two or three of the IDs: open the worker and confirm the document is there with the expected name. A completed integration event shows that the run finished. It does not show that each PDF reached the right worker.