# Workflows with a Dynamic Destination Path

## Workflows with a Dynamic Destination Path

Workflows are designed to orchestrate an automated and iterative execution of ordered tasks. When designing a workflow to orchestrate a process, a source object is required, that can provide access to a directory containing bulk load of source files of the same layout and format. A workflow is designed, such that each source file in that particular directory goes through the same data processing.

This requires a source file to be replaced in the run-time until all source files (of the same layout and format) within a directory are processed and loaded to respective destination files. Now, this further involves the associated destination files to be replaced in the run-time as well, hence making it essential to provide a dynamic destination path. For example, a source directory containing 100 excel source files in this workflow will create 100 destination files after going through the process.

In this document, we will learn to create workflows with a dynamic destination path that directs Astera to create a unique destination file for each source file.

In Astera, a dynamic destination path is achieved in two steps:

1. Parameterizing the dataflow through a *Variables* object.
2. Providing a run-time destination path through an [*Expression Transformation*](https://documentation.astera.com/astera-data-stack-v10/dataflows/transformations/expression-transformation) object in the workflow.

### Parameterizing the Dataflow

In this case, we are orchestrating a simple dataflow that contains customer account details coming in through an [*Excel Workbook Source*](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/excel-workbook-source). The source data is passed through an [*Aggregate Transformation*](https://documentation.astera.com/astera-data-stack-v10/dataflows/transformations/aggregate-transformation) and loaded to an [*Excel Workbook Destination*](https://documentation.astera.com/astera-data-stack-v10/dataflows/destinations/excel-workbook-destination).

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FUx3UWqo8onzeprXgvBXh%2Fdataflow.PNG?alt=media\&token=a0a9f7f4-0b7a-40d7-a685-68a59a6c5cd3)

Now, there are 100 Excel source files in a directory containing customer account details of 100 customers. Each of these *Customer\_Accounts* files must go through the same aggregate transformation and loaded to a distinctive *Excel Workbook Destination* file.

For this purpose, we will add a *Variables* object and parameterize the source file path and destination file path in the dataflow.

1. Go to *Toolbox > Resources > Variables*, and drag-and drop the *Variables* object onto the dataflow designer.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fa9XlMqzZodCo8upxkQSu%2Fvariable.gif?alt=media\&token=59d94008-7120-494a-9c62-2e2d2baed369)

The *Variables* object is currently empty and needs to be configured.

2. Right-click on the object’s header and select *Properties* from the context menu. A properties window will open.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FPXpTyXseIDLdXD1oNFgl%2F7.png?alt=media\&token=e2174393-8a10-4edf-9aee-86140d6e900f)

3. On the *Variable Properties* window, create two new fields:

* *Excel\_Source*
* *Excel\_Destination*

Set both fields as *Input*. For their *Default Value*, paste the path for customer account file source and customer account file destination in each field, respectively.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FcKnedvlszvZhbt7SG2i1%2F6.png?alt=media\&token=294a3af8-8092-48a6-8554-2aa677f09231)

Click *OK.*

4. Double-click on the *Customer\_Account* source object header. An *Excel Source Properties* window will open. Click *Next,* and the *Layout Builder* will open. Click *Next* again, and the *Config Parameters* screen will open.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FxFFvT4hJp4nR6hCxgm3N%2FConfig_source.gif?alt=media\&token=7b0dc061-e173-43cb-ad82-5f92912a179b)

5. On the *Config Parameters* window, under the *Value* column for *File Path*, provide the name of the field enclosed in {….} that points to the source file path in the *Variables* object in step 3.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F0W6JPi1m4up2pb64QBzH%2F5-1589980484676.png?alt=media\&token=aa9ece54-beac-4f29-adc3-4418a84a8d67)

6. Now, Repeat steps 4 and 5 for the *ExcelDestination* object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F1ECyx4Ok8FH52skphgxG%2F6-1589980754450.png?alt=media\&token=e7e7082f-e1f8-4bd4-a6a6-7a8873a8bc94)

Our dataflow has been parameterized. The next step is to create a workflow with a dynamic destination path.

### Creating a Workflow

1. Open a new workflow by going to *File > New > Workflow*.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fb8zk18OjbAQeuaHIIS1O%2F1_workflow.png?alt=media\&token=f8d6e8e7-ed74-41a0-a865-5749fe7e3b52)

2. To provide access to the local source directory containing 100 customer account files, go to *Toolbox > Sources > File System Item Source* and drag-and-drop the [*File System Item Source*](https://documentation.astera.com/astera-data-stack-v10/dataflows/sources/file-system-items-source) object onto the workflow designer.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fm96UdTM742SRWNG0QIm0%2FFile_system_item_source.gif?alt=media\&token=4aa71b47-5361-4c49-9707-f7e16b3734a4)

3. Right-click on the object’s header and select *Properties* from the context menu. A configuration window will open for the *FileSystem* object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F742U0aa6FsWXDk4xywua%2F2_properties.PNG?alt=media\&token=dfe93ef0-8072-479a-b43e-3dca74ff3625)

4. Provide the local directory path in *File Location*. Apply a *Filter*, \**.xls* and click *OK.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FCbhQiE253mnqDYRlvrI5%2F3.png?alt=media\&token=7583b209-2af7-4259-9dff-d7df89720b90)

5. Right-click on the *FileSystem* source object’s header and select *Loop* from the context menu. This will direct Astera to run the task in a loop, replacing the source file paths in real-time, until all source files have been processed.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FlAwcxkVRGCKXmBrVFblo%2Floop.gif?alt=media\&token=03b0f6fc-81b2-4a80-8391-fb921cf0b466)

6. Go to *Toolbox > Workflow Tasks > Run Dataflow* and drag-and-drop the *Run Dataflow* object onto the workflow designer. Map the header output port of the *FileSystem* source object onto the header input port of the *Run Dataflow* object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fd7zWEuB8Np6CUU9JeWgN%2Frun_dataflow.gif?alt=media\&token=f7cb4476-79ce-4353-801c-eba72b0866b3)

7. Right-click on the *Run Dataflow* object’s header and select *Properties* from the context menu. Here, provide the *Job Info* *Path* for the dataflow that is being orchestrated and click *OK.*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FNlpcQIAadJS6Z7vyyD1R%2F5.PNG?alt=media\&token=2b41a03c-62c9-4392-8434-f644ddae05bf)

8. Go to *Toolbox > Transformation > Constant Value*, drag-and-drop the [*Constant Value*](https://documentation.astera.com/astera-data-stack-v10/dataflows/transformations/constant-value-transformation) transformation object onto the workflow designer. Double-click on its header, a *Constant Value Map Properties* window will open.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FXN5QcNn0VGgC6gxnhev1%2Fconstant_value_7.gif?alt=media\&token=e8bb6748-72d2-4277-9af0-6b4a5ba68d03)

9. Paste the path of the folder where Astera will load the destination files, in the *Constant Value* box as shown below. Click *OK.*

<figure><img src="https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F0u8sGEHJwOx2NGbDN4ux%2F7%20(2).png?alt=media&#x26;token=3cfbd16f-49a2-4c02-800d-ec61f2b3b581" alt=""><figcaption></figcaption></figure>

10. Go to *Toolbox > Transformation > Expression*, drag-and-drop the *Expression* transformation object onto the workflow designer. Double-click on its header, and a *Layout Builder* screen will open.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2F2ObUh1YDYitZWF8pyu9z%2FExpression_8.gif?alt=media\&token=50ca1707-b742-49f8-bb80-22625cc24e75)

11. On this *Layout Builder* screen, create four new fields and set the data type accordingly.

* *FullPath*
* *DestinationDirectory*
* *FileName*
* *DestinationFilePath*

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FQUb3nN6hv1mppsHukuEk%2F8.png?alt=media\&token=ca46d938-8b07-4d68-810a-936f55b4f15b)

Click *OK.*

12. Define field mappings as follows:

* *FullPath* field under *FileSystem* source object onto the *FullPath* field under *Expression* transformation object.
* *FullPath* field under *FileSystem* source object onto the *Excel\_Source* field under *RunDataflow* task object.
* *Value* field under *Constant Value* transformation object onto the *DestinationDirectory* field under *Expression* transformation object.
* *DestinationFilePath* field under *Expression* transformation object onto the *Excel\_Destination* field under *RunDataflow* task object.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FthIsCA3ZDvbtCKc1ixgN%2F9.PNG?alt=media\&token=25ee721d-80a7-4558-a964-d07656e88d23)

13. Double-click on the *Expression* transformation object’s header, to open its *Layout Builder* screen. Write the following expressions for these fields:

* *FileName* : LSplit(RSplit(FullPath,2,”\”,0),2,”.”,0)
* *DestinationFilePath* : DestinationDirectory+FileName+”.xls”

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2FJsbuwQUeSjTZV9BBerqu%2F10.png?alt=media\&token=3c07c3bf-5c42-46ce-a325-a58b97bff6cf)

Click *OK.* A dynamic destination file path has been created.

14. Click on the *Start Workflow* icon located in the toolbar at the top, and execute this workflow.

![](https://3083465318-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FsR50Wa7EwZGlmPSAMkkf%2Fuploads%2Fi8OyVZnqsoJq5p2WAEIs%2F11.png?alt=media\&token=887b9ac0-654e-4cd1-95c8-90bfa403b303)

This will create a unique destination file for each source file.