Data Integration Module (DIM) Sample Project Walkthrough Guide (SQL Output)

Data Integration Module (DIM) Sample Project Walkthrough Guide (SQL Output)

Overview

This document walks through the setup of a SQL database output Sample Project in the Field2Base Data Integration Module (DIM) software. 

Reference Files & Folder Setup 

Note: This document assumes you have already installed the latest version of the DIM software and are ready to setup your first DIM Project. 

Files to Download 

Here are the reference files you need to download to walk through this Sample Project Setup. 

You will need to save the Sample Form to your local Drive for use in this walkthrough: 

  • Extract the Sample Form to the Forms folder (C:\Program Files (x86)\F2B Data Integration Module\Forms) 
Note: This document assumes you have Administrative privileges to create new folders on the PC running the DIM otherwise you will not be able to proceed on from this step. 

Put the Sample Databases in the Local Folder 

Here you will need to create a new folder on your hard drive for the Database files used in this walkthrough: 

  • Create a folder named “Sample DB” on the Local (C:) Drive 
  • Extract the files to the “Sample DB” folder 

Load the Sample Form 

The first step in the process is to load the Sample Form into the DIM’s Forms folder. 

  • Go to the F2B Data Integration Module Program Folder in the Start Menu  
  • Open the DIM Project Manager 
  • Go to the File menu 
  • Select Load Forms 



  • Select the Form using the Windows Explorer Browser 



Create the Sample Data Integration Module Project 

The next step is to actually create the Sample Project from the Sample Form provided. 

  • The Project Manager will open with No Project Loaded 



  • Open the File Menu 
  • Select “New” 

Load the eForm for the Sample Project 

Now, the eForm needs to be loaded for this Project. 

  • The Open Form screen will appear 
  • Select the “Customer_Introduction_Sample.eform” 



  • Click the Open Button 

 

Set the Initial Transform Type for the Project 

The next step is to actually create the Sample Project from the Sample Form provided. We will create a SQL Transformation with a connection to a local SQL Database (in this case, a SQL Compact Edition Database). The Sample SDF Database downloaded at the beginning of this walkthrough is the one that will be used. 

Note: The Sample SDF Database will only work with the Project Manager and does not actually work with the Service; this example was done to provide Users with a full review of the steps involved. 
1. The Initial Transform Type Selection screen will open 
2. Select the SQL option 
3. Click the OK button 


4. Select the OleDb as the Database Provider 
5. Enter the following into the Database Connection String entry box: 
      Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Sample DB\sql_sample_db.sdf;SSCE:Database Password='test'; 
6. Click the Connect button 



7. Leave the CustomerIntroduction Table selected 
8. Click the OK button 



9. Select the Insert 1: CustomerIntroduction in the SQL block shown in the Transformations section on the right side of the screen 



10. Select CompanyName in the Mapped Values section in the center of the screen 



11. Drag and Drop the CompanyName Region onto the Drag and Drop Region Here section 



12. Repeat this Column-Region assignment using the following pairs:
  1. CompanyContact column – CompanyContact Region 
  2. CompanyDescription column – CompanyDescription Region 
  3. NumberOfTablets column – NumberOfTablets Region
  4. TypeOfTabletUse column – F2BUse Region 
  5. Notes column – Notes Region 
13. You will now see the final SQL Insert Statement that will be used in the Details window at the bottom of the Project Manager:



Create another Transformation with an Access Database 

The final Data Transformation created within this Project will be SQL Transformation with a connection to an Access Database using an ODBC connection. 

The Sample Access Database downloaded at the beginning of this walkthrough is the one that will be used. 

1. Click on the SQL Transformation button 
 

2. Select the Odbc as the Database Provider 
3. Enter the following string into the Database Connection String entry box:  Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Sample DB\SampleDB.accdb;Uid=;Pwd=; 
4. Click the Connect button 


5. Leave the Customer_Introduction Table selected 
6. Click the OK button 


7. Select the Insert 1: Customer_Introduction in the second SQL block shown in the Transformations section on the right side of the screen 


8. Select CompanyName in the Mapped Values section in the center of the screen 


9. Drag and Drop the CompanyName Region onto the Drag and Drop Region Here section 


10. Repeat this Column-Region assignment using the following pairs:
  1. Contact column – CompanyContact Region 
  2. Description column – CompanyDescription Region 
  3. NumberOfTablets column – NumberOfTablets Region 
  4. Notes column – Notes Region 
  5. Industry column – CompanyIndustry Region 
  6. Usage column – F2BUse Region 
11. You will now see the final SQL Insert Statement that will be used in the Details window at the bottom of the Project Manager 


12. Save the Project to finalize all of the changes made. 

13. Now that you have applied all of the new Transformations to the Sample Project, the last step is to restart the DIM service. Return to the DIM Administrator Utility and click on the Restart button found in the Service section. 


    • Popular Articles

    • Forms Designer Quick Start Guide

      Overview Field2Base Forms Designer is the proprietary software application that allows your existing paper forms to be quickly converted to a smart E-form available to your end users via our mobile and web-based Mobile Forms applications. This ...

    • Portal 11.28.2023 Release Notes

      Overview Our release notes offer brief descriptions of product enhancements and bug fixes. We include links to the current articles for any affected features. Those articles will be updated shortly after the Portal release to include new ...

    • Integration Service Configuration Guide

      How To Configure Integration Services To Allow Read/Write Access on a Network Path All of our Integration Products, including the DIM, DUU, and EDM have respective Windows Services responsible for communicating with our server. Occassionally, ...

    • Data Integration Module (DIM) Migration Guide

      Overview This article provides the information necessary to migrate the Field2Base Data Integration Module (DIM) over from one server to another. Please refer to the DIM Install Guide for the initial installation of the Field2Base DIM. Once that's ...

    • How to Check the Version of Integration Products Running on a Windows 10 Machine

      Right-click on the Start menu button. Click on Apps & Features. In the Apps & Features search bar type in the Integration Product you are looking for, eg. F2B Data Integration Module, F2B Data Upload, or F2B Enterprise Dispatch Module. Click to ...