DIM Sample Project Walkthrough Guide (SQL Output)

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. 


    • Related Articles

    • DIM Sample Project Walkthrough Guide (CSV Output)

      Overview This document walks through the setup of a CSV 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 ...
    • EDM Sample Project Walkthrough Guide

      Overview This document walks through the setup of a Sample Project in the Field2Base Enterprise Dispatch Module (EDM) software. Reference Files & Folder Setup NOTE: This document assumes you have already installed the latest version of the EDM ...
    • Creating SQL Update Data Transformations

      Overview The standard SQL Data Transformation used in a DIM Project is an Insert SQL transaction. SQL Update transactions are also supported for cases when a company needs to update existing information in their SQL Database. This feature is only ...
    • Additional Project Options Not Available in the DIM Project Manager

      Overview The Field2Base Data Integration Module does have some Project features that can be added by directly editing the Project XML file with a Text Editor. Most of these features are advanced or not often used options and were not included in the ...
    • Data Integration Module (DIM) User Guide

      Overview The Field2Base Data Integration Module allows you to automatically download data from your Sent Forms to your backend systems. The DIM provides transformation of your Form Data into a variety of formats including CSV and SQL. The DIM Sample ...