Orchestrating File Processing and Archiving Using appRules File Iteration Activities

A common requirement in IT shops is the ability to quickly process lots of files in a manageable and predictable manner.  CSV, Excel, Access, XML, JSON are some of the file types that you may be asked to process and archive.

We recently provided a project snippet to a customer that was migrating hundreds of Excel and Access files from multiple locations into a centralized MySQL database.   In this post, we will use this opportunity to discuss the project snippet and the file iteration activities in appRules.

A majority of the activities that were used in the snippet come from the appRules File System module which is shown below:

In a future post, we will discuss the activities in the appRules File System module as a group.  Note that in addition to the included activities, file and directory functions can also be performed using custom functions.  For this walkthrough, we will concentrate on the activities that were used in the project snippet.

 

Below is the project snippet:

Below is the description of the main activities used in the project to perform the iteration of the files:

  1. DisplayName: InitializeFileInfoSource

Workflow Activity: DirectoryInitializeFileInfoSource

Description: The DirectoryInitializeFileInfoSource activity creates records for each file in a folder.  The records contain information for each file and the file attributes are treated as data field values as shown below.  This feature allows you to treat the file just like other records in appRules.  In this example, the DirectoryInitializeFileInfoSource is the Source and upon initialization, it loads the first record.

Below are the properties of the DirectoryInitializeFileInfoSource activity.

Once configured, the record data fields can be accessed as shown below:

  1. DisplayName: Archive File

Workflow Activity: FileMove

Description: The FileMove activity is used for moving a file from one location to another.

MoveFrom: Instead of specifying a specific file path in this property, the value used in this case is a DataFieldValue from the current file record.

MoveTo: This is the destination for the file.  The file can be move to a specific file path or to a folder as specified.

  1. DisplayName: GetNextFileInfoRecord

Workflow Activity: DirectoryGetNextFileInfoRecord

Description: This activity gets the next file info record which contains the attributes of the specific file.  When the end of the records is reached, App.Result is set to false and iteration will end.

 

Conclusion:   

The appRules File System module includes workflow activities that can be used to iterate files for large file processing projects.  In this example, by using just three activities, we are able to iterate and archive the files without writing code.

 

To download appRules: www.appstrategy.com/appRulesTrial

Replicate Cloud Platform Data Using the appRules® Data Replication Activity

As companies move applications to the cloud, there is an increasing need to make data available locally for many reasons.  IT organizations want to store the data locally for backup purposes, reporting and data analysis using familiar and latest tools.

appRules includes a very powerful workflow activity that is specifically designed to replicate data in a SQL database.  The ReplicateData activity is available in most modules of appRules.  It is designed to allow you to replicate data by simply configuring properties.   You can elect to to replicate all entities or to replicate only a selected list of entities.

Figure 1 shows a simple but very powerful workflow that can be used to replicate Dynamics CRM data in Microsoft SQL Server.  The process is the same for replicating Salesforce, SugarCRM, NetSuite, Eloqua, HubSpot, Marketo, SAP and other data sources.

Figure 1: ReplicateDynamicsCRMData

Accept the default entity configurations or make changes:

Select Entities:

Configure the properties below and you are ready to go.

Conclusion

The ReplicateData activity is a powerful tool for replicating SaaS data in SQL Databases.   It includes configurable options for managing every aspect of the process.

You can download appRules with sample projects at: http://www.appstrategy.com/appRulesTrial

Using appRules to Read and Process Stored Procedure Records

appRules Studio includes workflow activites for reading records from a stored procedure.  By utilizing the stored procedure related activities, the stored procedure records and the fields they contain can be read from the database and utilized in appRules just like any other records and fields.

Once a stored procedure record is read into an appRules project, the record and its fields can be used in Mappings, Custom Functions, Conditions, and other modules and facilities.

In this post, we will walk through a simple workflow example for reading records from a stored proceedure and viewing the field values using the ViewRuntimeRecords  activity.

Below is the sample workflow for reading records from a SQL Server Northwinds sample database stored procedure:

 

The table below shows the key workflow activities used in composing the workflow:

 

# Activity Type Notes
1 initializeProject Use the default values
2 ExecuteMssqlStoredProcedure

(ExecuteSqlServerStoredProcedure)

This activity is used to define a Source to select the records to be processed.

 

  • Configure the DataSource property to select the data source to be used for connecting to the database.

 

  • Set the StoredProcedure property by selecting it from the list of stored procedures in the database.

 

  • Define the fields in the stored procedure record by adding an entry for each field in the StoredProcedureFields collection. Only the field name and field type is required when defining the field.  The field name must match the column name used in the stored procedure.

 

  • If your stored procedure uses parameters, you can define them by adding an entry for each parameter in the StoredProcedureParams collection.

 

  • Set the AutoLoad property to FirstRecord to read the first record from the stored procedure once the activity is initialized.

 

3 While Use a While activity to control the flow of the workflow.

 

  • Configure the Condition by using the App.ActionResultIsValid flag to check the last action performed by the activity before the While activity.

 

  • Add a Sequence activity to the Body of the While activity. This Sequence will contain activities that will read and display the next record from the stored procedure.

 

4 ViewRuntimeRecords Use the default values to display the fields read from the stored procedure.

 

5 GetNextMssqlRecord

(GetNextSqlServerRecord)

Reads the next record from the stored procedure.
6 CloseMssqlSource

(CloseSqlServerSource)

Closes the connection to the database
7 CloseProject Closes the project, logs statistics and frees resources
 

 

 

Below are the properties of the ExecuteSqlServerStoredProcedure activity:

 

This is an example of how you can use appRules workflow activities to read records and associated columns from a stored procedure.

You can download appRules Studio including this sample project at: http://www.appstrategy.com/appRulesTrial

Data Integration 101: Anatomy of a Simple ETL Project Using appRules

In this Data Integration 101 post, we will do a quick tour of a simple ETL project using appRules.

The project which is shown below is used to perform bulk export of data from a SQL database to a CSV file.  No code was used to accomplish the task and it utilizes only three main activities.

The main activities are described below:

# Activity Description
1 InitializeSqlServerCeSource This activity is the initialization for the SQL Source.  You can select an activity from any SQL module – SQL Server, Oracle etc.  Configure the properties of this activity from any SQL module to select the records to export.
2 InitializeDelimitedTargetFile Configure this activity from the Delimited File module to define the Target CSV file and its attributes.
3 MapAndSaveRecords Use this activity to map data fields from the Source to the Target.  The activity also saves the records.

 

Conclusion

This example showcases the no-code ETL capabilities of appRules.  The same process can be used to export data from Accounting, CRM, ERP and other modules – without writing code.

 

You can download appRules with sample data integration projects at: http://www.appstrategy.com/appRulesTrial