Back to Index |

| The Join Object
Mapping data and transformation

Joining input objects | Mapping columns | The Code Window | Filtering data | Exceptions

The Join object is the central point of data mapping and the transformation of your data. Once you have all of your data objects defined and connectors in place, double click on the Join object. There is a lot going on in this window, the primary functions of the Join object are to:

- Map individual columns from input objects to the output objects

- Join input objects (references) where necessary

- Filter incoming data

- Write VB.NET code to transform the incoming data before it goes to the output object.

Illustration 1.0

Joining input objects

All Join objects have exactly one (1) primary input object, all other input objects are considered references that join to the primary input object based on a key column. All reference objects must contain at least one key column so that BlueSky Integration Studio knows how the reference input object joins to the primary input object.

If your job as more than one input object, then you must join them. To join an input object:

1. Ensure the reference input object contains at least one key column.

2. In the Join object window, click the join icon on the reference input object

3. Drag and Drop the key column to the reference input objects key column. This is how the two objects will communicate, so to speak. Whenever a record flows in through the primary input, it will attempt to find the same information in the reference input object based on the key column.

4. Click OK.

* Note: The primary input column can also be transformed using VB.NET! For instance, say initially the columns do not match perfectly, or maybe the input column's data needs some logic wrapped around it, like a Select Case statement to determine how to join the input objects. The opportunities are limitless!

Mapping columns

Mapping columns is simply a matter of dragging an input column to an output column. Notice it automatically inserts the VB.NET keyword Return for you. That's because every output column is a VB.NET function, and you must return data from the function, otherwise it will default to Nothing.

The fact that each output column is a VB.NET function gives you almost limitless power to transform your data using any VB.NET code and/or .NET classes that you see fit. This is all done in the code window beneath each column.

The Code Window

The code window is where you can write VB.NET code around specific to that output column. The good news is you have complete access to the entire VB.NET language and any .NET framework class you see fit to accomplish your goal. Each and every output column is a function wrapper, you just need to fill in the blanks.

To get to the code window click on the button in the input columns cell that you want to modify.

The code window has features such as syntax highlighting, and drop down column lists. It also has all the input objects listed in a tree to the left that you can use to insert columns directly into your code to alleviate typing mistakes. You can use any number of columns from your input objects in the code which is extremely useful for performing calculations. You can also use pre-developed CodeStore functions, or reference external components.

Fill in the necessary transformation code and click OK.

Filtering data

Every output object contains a filter function in which you can write VB.NET code to filter the data before it gets written to the output object. The filter object is a boolean function that requires you to return either True or False. If the return value is True, the record is allowed to pass through to the output object. If false, it is ignored.

To get to the filter code window, just click the filter arrow icon. Again, you have complete access to the VB.NET language and any of the .NET framework classes. Just remember your output must be either True or False.

In the following example, the code is checking the Customer ID column to make sure it is less than 1000. If it is, the code returns True indicating that the record can pass through to the output object. If the Customer ID is greater than 1000 then it must be invalid and the function returns False, in which it will be ignored.

Handling Exceptions

Notice on Illustration 1.0, we have 2 output objects. The first being a SQL Server table, and the second being the exceptions table. Handling exceptions is as simple as applying code to the Filter for the exception output object. You simply write code and return True or False whether you want each record from the input object to flow into the exception object.

Given the example above, if the Customer ID is greater than 1000 it is simply ignored. But what if you want to capture that invalid record and put it somewhere? Simple, just write the filter the exact opposite way:

If CInt(i2.CUSTOMER_ID) > 1000 Then
Return True
Else
Return False
End If

So, for the exceptions output, if the Customer ID > 1000 then the filter returns True indicating to allow this record to pass through to the exceptions output object.

<<Prev Next>>

Designing the process flow
Defining Properties
Mapping and Transformation
Compiling Jobs

© 2003 - 2007 Relational Solutions, Inc. - All rights reserved