Author: Mayura Deshmukh
Links: Enterprise Solutions, Business Intelligence and Appliance Solutions
Welcome to Episode 7 of the Dell™ US Patent Office (USPTO) Project. In this episode we focus on loading a simple *.csv file into the database table using Dell Boomi.
Figure 1. Process overview
Before loading the *.csv file into the database, you will need to accomplish the following:
Create the USPTO database in SQL Server 2012 using a create database script, or through the Microsoft SQL Server Management Studio console. Click Connect to Instance> Expand instance, then right-click Databaseand select New Database from the drop-down list.
Figure 2. Creating a database using Microsoft SQL Server Management Studio
We will be storing just a few fields from the USPTO XML file within the database. To create reports like “Top 10 patent producing companies for a given year” and “Top 10 patent producing cities for a given year”, we are interested in fields such as patent publish date, inventor details, and company details.
Figure 3. Schema design
Create the tables shown in Figure 3 within the database.
To complete this process you will need to:
Create a process component. For detailed steps on creating a process component, check Episode 3 of this series.
Episode 3 also explains the process of creating a disk connection and operation.
Figure 4. Path for *.csv file
Figure 5. Disk Operation for the *.csv file
In this section you will create two profiles that Dell Boomi will use to interpret the data, one for a *.csv file and one for a database table. Once these are created, you can create a map component to link the fields in the *.csv file to the fields in the database table.
1. Create a *.csv profile. For detailed instruction that cover this process, see Episode 3 of this series.
2. Create a database profile.
a. Click Create Component Profile.
b. Enter a name and select database menu.
c. Create a profile with the values displayed in Figure 6.
Figure 6. Option for the Database Profile
d. Enter a Database Profile name.
e. Click the Options tab, select Write as the Execution Type.
Figure 7. Data Elements for the Database Profile
f. Click the Data Elements tab, click Statement.
g. In the right side window select the Standard Insert/Update/Delete within the Type field, and within the SQL Script field, enter the insert statement. Enter ‘?’ to dynamically insert the *.csv file values.
h. Right-click Fields, then select Add multiple fields.
Figure 8. Add input parameters
i. Enter a Field Name and Data Type for each field. These fields are the fields in your table in which you want to insert data.
Figure 9. Data Format for the input parameters
j. Select Save, and then Close.
1. Drag and drop the Map shape into the process. The Map Properties window opens automatically. Enter a label and select the (+) symbol to the right of the Map field.
2. Enter a Name for the Map, and then select Save.
3. On the left side of the map, select the symbol, select profile Flat File, and then select the *.csv profile created in the previous step.
4. Select OK.
5. On the right side of the map, select , select profile Database, and then select the database profile created in the previous step.
6. Select OK.
7. Click the Boomi Suggest button, and Boomi will automatically suggest mappings between the two profiles as in Figure 9, or simply drag elements on the left to the corresponding elements on the right to connect as in Figure 10.
Figure 10. Boomi Suggested Mapping
Figure 11. CSV to database mapping
8. Click Save, and then click Close.
1. Drag and drop the connector shape in to the process field; the Connector Action window opens.
2. Select the Connector as Database and the Action as Send options to insert data into the database.
Figure 12. Creating database connector
1. To the right of the Connection field shown in Figure 11 select the (+) symbol to create a new connection.
2. Enter the required information as in Figure 12 and Table 1.
Figure 13. Database connector details
Username to access the SQL Server database
Password for above user account
SQL Server instance to which you want to connect
1433 (the port number Dell Boomi uses to connect to the SQL Server
Name of the database to which you want to connect
;domain=your domain name
Note: Enter the ;domain=your domain name in the Additional Options field if you are using Windows Authentication to access SQL Server. If you are using SQL Authentication, the Additional Options field should be blank.
3. Select Save and Close
1. To the right of the Operation field in the Connector Action window select the (+) symbol to create a new operation.
Figure 14. Database operation
2. Enter following information:
Enter Name (optional)
Send (default when Send is selected as the Connector Action)
Select the database profile created in previous section
4. Select OK in the Connector Action window to create the connector.
1. In the Shapes menu click Logic and then drag and drop a Stop shape.
2. Enter a label and select Ok in the Stop Properties Window.
3. Connect all the components as shown in the figure below.
Figure 15. Complete process
4. Name and Save your process.
To test your process,
1. Click Run a Test button on the upper right side of the window.
2. Select the Atom you want to use and click the Run Test button.
Figure 16. Select atom to run test
If there are any errors you can check on the Logs tab for the detailed error message. If the test executes successfully data from the *.csv file will be inserted into the table.
Figure 17. Records imported into table
Additionally, you would get a notification and your process would appear as shown in Figure 16.
Figure 18. Testing process
Please view the how-to video in the blog Boomi Getting Started to deploy and execute the process.