Integration flows are the foundation of any data integration project. Centerprise Data Integrator has built-in automation features that make this oftentimes complex process so easy that non-technical business users can create flows with minimal or no IT support.
In this example we will create a simple integration flow, called a dataflow, using an Excel source and putting the data in a database table. This is a common task used often for moving data from documents to databases so that it can be used downstream for operations and business intelligence.
To create a new dataflow, go to the file menu and select new/dataflow.
In the toolbox on the left side, you can see items such as sources, destinations, maps, transformations, and more.
To create a source, point to your source and drag and drop it onto the designer. In this example, since the source is an Excel workbook, we will drag and drop the Excel workbook source item onto the designer.
Next we need to specify the properties of the source. Right click on the source to open the properties window, which presents a wizard where we can specify all the properties for the data source.
In this window we specify where the file path for the source is located by clicking on the File Path button and pointing to the source file in the Explorer window.
Move to the next page by clicking on the right arrow button in the top left corner of the source window.
Centerprise opens a window that shows the layout of all the source fields from the Excel file. The application automatically identifies all the fields from the source and their corresponding data types.
Click OK and you can see your source in the designer. Click on the chevron in the upper right corner and the window expands to show all the fields from your source.
Now that the source is ready, you can preview your data by right clicking and selecting Preview Data. Centerprise has read the data from the Excel source and at the bottom of the window you can see how it looks inside the source.
Next we want to create a destination. We go to the destination table and from the toolbox drag and drop the table destination onto the designer.
Again, right click on Properties and in the Options dialog box you specify your credentials and the location of your database table.
Here you choose which type of database you are working with depending on your destination and input your credentials for that database type. In this example, we select the SQL server and input our credentials (or you can choose a recently used connection), then we click the test connection button to ensure that the connection works.
Now we move to the next page by clicking on the right arrow button in the top left corner. This opens a window that asks for information about the table into which we are going to write. We can choose an existing table or create a new table. In this case we will create a new table and leave the default options.
Again, click the right arrow to go to the next page, which shows us the layout of the destination.
Now our source as well as our destination is ready and we will map the two together. For the mapping, we will use the auto mapping feature of Centerprise. To do this, we drag and drop the entire source node at the top of the input to the output.
You can see that Centerprise has automatically created all the maps and that for each field in the source there is a line that goes to the matching field in the destination. This very simple map from source to destination will take the data as it is in the source and put it in the destination.
We have just created a simple dataflow by mapping our fields from our source Excel file to our destination database. Now we will give it a name and save it on our system so we can go ahead and run the dataflow.
For that, we use a very simple method. On the top left of the screen we click the drop down list next to Servers, which will show all the servers installed on the machine. In this case we choose the server Development.
We click on the green arrow to the right to start the dataflow.
At the bottom of the page the Job Progress window will show you the progress.
Click on the database button and you can see the results of your dataflow. This example shows that there were 83 records and they were all processed to the database destination with no errors.
That’s how easy it is to create a simple dataflow in Centerprise. The capabilities of the software extend far beyond simple processes to encompass the most complex of structured and unstructured data sources. Our next blog will show you how Centerprise can be used to create more complex dataflows.