Visual Integration Studio

This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

Every company and developer in the world has to work with data, usually on a daily basis. The problem is that many times the data is not always in the right place, or correct format to make it useable. Developers must spend countless hours building data processing and integration programs to move data to different places, scrub and filter the data, wrap the data in business rules and generally massage it into useable information that adds value to the company. And what then if you need to automate that process on a regular basis?

Of course, there are a plethora of ways to accomplish these tasks, many of them painfully slow and tedious or way to expensive to even consider. Many developers hand-code much this work through the use of SQL scripts, or they use an MS Access database to link tables from other data sources only to eventually hit the limits of the tool. Another option is buy an expensive integration tool such as Webmethods™ or Informatica™ which can cost well over $100,000 only to find that it is difficult or impossible to integrate them into your own custom applications.

Quite simply, developers want ease-of-use and the complete flexibility to seamlessly integrate any solution into their own custom developed applications. Data integration should be a non-issue, build it and forget about it.

Visual Integration Studio Overview

Visual Integration Studio is a powerful data integration, ETL (extraction, transformation and load) and process automation tool. It is one of those rare software tools that offers unlimited uses, and saves .NET developers countless hours of hand-coding all of those data integration tasks. With Visual Integration Studio you can integrate data from any data source to any data source and use standard VB.NET code to transform your data. Visual Integration Studio is also the only data integration tool available to offer 3 different deployment options: .NET executables, .NET components (dlls) and the actual VB.NET source code for your data integration job. This is where the true power and value of Visual Integration Studio comes in, offering complete flexibility to the .NET developer.

How does it work?

Visual Integration Studio offers a powerful visual design studio that allows you to simply drag and drop your data sources and elements from any data source to any data source, and write code to transform the data only when it is necessary. You can also mix data from different databases or files and join data sources together through the use of key columns.

The data source objects are defined through the use of the familiar properties window and many of the properties offer a helper dialog window to assist you in defining the properties. The design below is loading product master information into a hash table (a fast, in memory lookup table), and then loading Sales data that references the product master data to pull additional information such as the products description and cost. The job then loads that data into a SQL Server database for use on a corporate portal system. Notice as well that we are also filtering exception data into a text file for later viewing.

Once the job has been designed and all of the data objects are defined, you simply drag and drop the column level data elements to their output destinations. Notice we are mapping columns from both the Sales Information table as well as the product_hash lookup table. (ref.COST). This is accomplished to the use of the PRODUCT_ID key that we defined.

That would be pretty powerful in itself if this were simply a data mapping tool, but what if you need to transform or change the data in some way as it was passing through this process? And not only that, but have the complete power of the .NET framework to do so! Each output column in each data source contains a code-behind window that allows you to transform the data in any way possible using standard VB.NET source code. You can also filter the data using VB.NET.

Deployment options

Flexibility the key to Visual Integration Studio. You can compile any job design into a true .NET executable, which you can fully automate, or maybe you'd like to package this job up into a reusable .NET component (.dll) and reference it directly from your own custom application. One of the great things about the .NET framework is its language independence, you can reference the .NET component from any VB.NET, C# or ASP.NET application, taking Visual Integration Studio completely out of the picture. Of course any component built with Visual Integration Studio is runtime royalty-free.

Process Automation

When you compile your job design to a .NET executable, it is automatically stored in the Visual Integration Studio repository where you can run it at will, stop it, view the log files, and even monitor the job in real-time. You can actually watch the data flowing through the process and monitor statistical information such as records-per-second. In the professional and enterprise editions the Visual Integration Studio server and repository can be located on a powerful server-based system where they can be batched together into a complex process and automated through the use of the Visual Integration Studio Scheduler service.

How to reference a job from your .NET application

When you compile a job to a .NET component, you have all of the same functionality that you get in the Job Control window above. You can run, stop or reset jobs, view the log files, and even monitor them all through code. For instance, let's say we've designed a job called ExportBudget and we compile it to a .NET component. To run the job, you would simply need to reference the component in your application, create an instance of the job's class object and call the RunJob method. You can also run the job in either the current thread or in a background thread, allowing your application to continue on.

[Visual Basic .NET]

Public Sub RunMyJob() 
        Dim js As ExportBudget.CRJobStatus
        Dim myExportBudgetJob As ExportBudget

        myExportBudgetJob = New ExportBudget()
        myExportBudgetJob.JobPaths.JobStatusFileName = "ExportJobStatus.xml"
        myExportBudgetJob.RunJob()

        js = myExportBudgetJob.GetJobStatus()

        If Not (js Is Nothing) Then
             MsgBox("Job is done, Status: " + js.JobStatus.ToString)
        Else
             MsgBox("Job is done")
        End If
End Sub

[C#]

private void RunMyJob()
{
      ExportBudget.CRJobStatus js;
      ExportBudget myExportBudgetJob;

      myExportBudgetJob = new ExportBudget();
      myExportBudgetJob.JobPaths.JobStatusFileName = "ExportJobStatus.xml";
      myExportBudgetJob.RunJob();

      js = myExportBudgetJob.GetJobStatus();

      if (js != null) 
      {
           MessageBox.Show("Job is done, Status: " + js.JobStatus.ToString());
      }
      else
      {
           MessageBox.Show("Job is done");
      }

}

Interacting with the Visual Integration Studio Server

Many times you will want to interact directly with the Visual Integration Studio server itself. The benefits of a server-based system are that your Visual Integration Studio jobs will typically run much faster on a server, which should be located near your database servers right in the data center. With the server component, your applications can connect to the Visual Integration Studio server with just a few lines of code where you can run jobs and monitor them. With the server component, you could literally build a custom Job control window specific to your application and give the users the ability to run and monitor the jobs themselves!

[Visual Basic .NET]

Imports CRFramework.VISControls 
Dim VIS As VISServerControl

VIS = New VISServerControl()

VIS.UserName = "administrator"
VIS.Password = "manager"
VIS.ServerName = "myserver.mycompany.com" 'DNS or TCP/IP address to the 
                                          'VIS Server

VIS.Connect()

VIS.RunJob("myJob", "SharedDomain")

[C#]

using CRFramework.VISControls;

VISServerControl VIS;

VIS = new VISServerControl();

VIS.UserName = "administrator";
VIS.Password = "manager";
VIS.ServerName = "myserver.mycompany.com"; // DNS or TCP/IP address to the
                                           // VIS Server

VIS.Connect();
 
VIS.RunJob("myJob", "SharedDomain");

Summary

You've seen just a few examples of the power available in Visual Integration Studio. Some of the other features include:

• Referencing external components and libraries to extend the capabilities of Visual Integration Studio
• Build parameters into your jobs to make them dynamic
• Code Store module to build global, re-useable functions available to any Visual Integration Studio job
• Role-based security