Creating A File Based Integration

Products	Versions
TIBCO Scribe Insight	7.9.1

Description

An Integration Process (IP) detects an event and runs a DTS to modify and integrate your data. File based Integration Processes (IP) run when a specified file is transferred to, created in, or modified in a specified directory. The file may or may not be the data source for the data translation specification (DTS) files (*.dts), so the available options vary accordingly.

TIBCO Scribe® Insight supports two file based connections. One uses an ODBC DSN connection and the other is the TIBCO Scribe® built-in Unicode Text adapter. This example uses an ODBC DSN connection. Download the FileIntegrationExample ZIP file, which contains sample files to help you with this example. Extract the files to a Collaboration folder on your hard drive. Collaboration folders are typically in the C:\Users\Public\Documents\Scribe\Collaborations directory.

General File Integration Flow

Configuration Process

In this File Based Integration example, we cover the following steps in the process:

Create an ODBC DSN
Create and test the DTS
Create a new File Integration
Test the process

Creating The ODBC DSN

Create the ODBC DSN using the TIBCO Scribe® Insight Text ODBC DSN driver installed with TIBCO Scribe® Insight. The DSN specifies the path and file name that the DTS is expecting for the source connection. The path and file name are fixed values. Wildcards cannot be used. The CSV/TXT file needs to exist in the Processing folder extracted from the FileIntegrationExample ZIP file while defining the ODBC DSN and when working on the DTS via the Workbench.

Note: The Unicode Text Adapter could be used in place of an ODBC DSN connection

In the TIBCO Scribe® Insight Workbench, click View > Connections.
In the Connection Manager click New.
Select ODBC Data Sources > Add/Modify ODBC Data Source. The ODBC Data Source Administrator dialog displays.
Click the System DSN tab.
Click Add. The Create New Data Source dialog displays.
Select the Scribe Text File option and click Finish. The ODBC Text Driver Setup dialog displays.
Enter a Data Source Name.
Enter the path to the data source in the Database Directory field. If you are using the files extracted from the FileIntegrationExample ZIP file enter the path to the 2_Processing directory.
Click Advanced.
Click the Define button.
Browse to the CUSTOMERS.CSV file in the 2_Processing directory and click Open.
On the Define Table dialog review the settings associated with the file to make sure it is parsed correctly.If you are using the sample CUSTOMERS.CSV file in the 2_Processing directory, no changes are required.
Click OK.
Click Cancel on the browser window.
On the ODBC Text Driver Setup dialog, click OK.
On the ODBC Data Source Administrator, click OK.
On the Add A Connection dialog, select the new ODBC Connection and click OK.
On the Connection Settings dialog, click OK.

When finished, a QTEXT.ini file is created with the file definition. This file always needs to reside in the Processing folder with the CSV file.

Create And Test The DTS

The next step is to create and test the DTS. In this example the DTS expects the source file to be named CUSTOMERS.CSV. Run the DTS and verify that the data is integrated correctly into the target.

NOTE: Save the DTS in a Collaboration to ensure that it is accessible by an Integration Process in the TIBCO Scribe® Insight Console. Collaboration folders are typically in the C:\Users\Public\Documents\Scribe\Collaborations directory.

Create A New File Integration Process

The File Integration process is a series of instructions that determines when to run the DTS to process a new file and how to do the necessary housekeeping to remove files that have been processed.

Open the TIBCO Scribe® Insight Console.
In the tree, navigate to Scribe Console > Integration Server > Integration Processes.
Click Add at the top of the window.
Step 1: Process Type – This option is selected for you. Configure this step as follows:
- In the Process Event section select File.
- Enter a Process Name.
- In the Data Translation Specification section click Browse.
- Navigate to the Collaboration folder containing the DTS you configured in the previous section and select the DTS file.

Step 2: Pre/Post Processing Commands (Optional). Click Step 2 and configure pre and post job processing on your data files.

This example assumes that multiple files may exist in the Staging folder extracted from the FileIntegrationExample ZIP file, each file should be uniquely named by appending a datetime stamp and sequence number, for example, CUSTOMERS-20160218.CSV. Since the DTS is expecting a different file name, Customers.csv, located in the Processing folder, a pre-job script must be created to move and rename the file.
A Sample VBScript is included in the FileIntegrationExample ZIP file and needs to be modified. See the comments in the script. The script expects a parameter which is the name of the file the DTS is expecting.
The Pre-Job sample VBScript is titled FileIP_PreJob.txt. You must edit the file to include the correct settings for your environment and rename it with a .vbs extension. This script does the following:

Checks whether or not a Customer.csv file already exists in the Processing folder. If it does, the script exits since the file may be processing or something else may have gone wrong.
Copies the original Customer.csv file from Staging to the Backup folder
Moves the original file from Staging to the Processing folder and renames it.

The Post-Job VBScript is titled FileIP_PostJob.txt. You must edit the file to include the correct settings for your environment and rename it with a .vbs extension. This script deletes the file in the Processing folder.
Configure Step 2 as follows:

In the Pre job command field, click Browse, navigate to the Pre-Job VB script, and select it.
In the Post job command field, click Browse, navigate to the Post-Job VB script, and select it.

Step 3: Event Settings – In this step you specify the Event File that triggers the File Integration to run.

Whenever a file is placed in the specified folder and the file matches the naming convention, the File Integration process runs. It is not the location where the file is processed, even though it could be the same location. The DTS determines the location and name of the file that is processed.

Click Step 3 and configure it as follows:

In the Event file(s) field, click Browse, navigate to the folder in the Collaboration where the trigger file will be placed and select the folder.
At the bottom of the Browse dialog, enter the name of the trigger file in the File or Wildcards field. In this example we used CUSTOMERS*.CSV. The * serves as a wildcard include any trigger file that starts with CUSTOMERS and ends with .CSV.
In the DTS Execution section, select Run DTS every time new file detected.

NOTE: Steps 4 and 5 do not need to be configured for this example.

Click Apply to save the Integration Process.

Test The Process

The only thing that remains is to test the process.

Activate the File Integration by opening it, selecting Step 4, and setting the Status to Active.
Copy a file into the Staging folder that follows the naming convention CUSTOMERS*.CSV.
Verify that the new information is moved into the target location.

Here is an example of the Pre-Job failure if the Customers.csv file already exists in the Processing folder.

For additional information, see the following in the TIBCO Scribe® Insight Help – Integration Processes Overview, File Integration Properties

Issue/Introduction

A TIBCO Scribe® Insight Integration Process (IP) detects an event and runs a DTS to modify and integrate your data. File based Integration Processes (IP) run when a specified file is transferred to, created in, or modified in a specified directory.

Attachments

Creating A File Based Integration get_app

Welcome to "KB Articles"