# 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 a 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/v/astera-data-stack-v8/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/v/astera-data-stack-v8/dataflows/sources/excel-workbook-source). The source data is passed through an [*Aggregate Transformation*](https://documentation.astera.com/v/astera-data-stack-v8/dataflows/transformations/aggregate-transformation) and loaded to an[ *Excel Workbook Destination*](https://documentation.astera.com/v/astera-data-stack-v8/dataflows/destinations/excel-workbook-destination).

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FLJXwUl2nbbPw50TXHvW7%2F0.png?alt=media)

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 be 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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FAYzefq2tKkw6XjpUJ4N8%2F1.gif?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FmB7L5S3JF2MKZNKnAaYI%2F2.png?alt=media)

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 the Customer Account File Source and Customer Account File Destination in each field, respectively.

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Fl1Qw0AnmlhQNz3VGWMMy%2F3.png?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FGX9ET71BkayZY7gZJssj%2F4.gif?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Ff7vW1ajg9pp0zKO3kocQ%2F5.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FnYD9Wv5j9uzKQu7lh8Oh%2F6.png?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FUrKEccNp1CdOaRl15wgH%2F7.png?alt=media)

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/v/astera-data-stack-v8/dataflows/sources/file-system-items-source)object onto the workflow designer.

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FyBsnPRzfNt75fPH25NFq%2F8.gif?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FWTfBvV7IhdvL23liyMCg%2F9.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FfJN5c5p8pgBioqzWoqDy%2F10.png?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FsEhviQeIzPXgxoVC7LEQ%2F11.gif?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FqrEAEIBIiyhFgdZ16GQW%2F12.jpeg?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2F1fZb5D2UJvlXoYUblEcH%2F13.png?alt=media)

8. Go to *Toolbox > Transformation > Constant Value*, and drag and drop the *Constant Value* transformation object onto the workflow designer. Double-click on its header and a *Constant Value Map Properties* window will open.

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FqHa7vCvYVQRzWjHmKxn7%2F14.gif?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FQ7XyaoTATVSOCbzyGwYx%2F15.png?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FNsNbyJlCwiACvxyOfubm%2F16.gif?alt=media)

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

![](https://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FB9HjvSoXR6KZnJLbmPxx%2F17.png?alt=media)

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 objects 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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2Fd3J86Y4lpnEOCM7PUgje%2F18.png?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FojTKfP3Mn52drCXD0m9F%2F19.png?alt=media)

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://750977703-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FqHxyVNGb7tSdIWecl6Ru%2Fuploads%2FXuGoOsv82FE97rG9gg8p%2F20.png?alt=media)

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