This scenario describes a four-component job which aims at fueling a database with data contained in a file, including transformed data. Two steps are required in this job, first step is to create the file, that will then be used in the second step. The first step includes a tranformation phase of the data included in the file.
Drag and drop a tRowGenerator, a tMap, a tMysqlOutputBulk as well as a tMysqlBulkExec component.
Connect the main flow using row Main links.
And connect the start component (tRowgenerator in this example) to the tMysqlBulkExec using a trigger connection, of type OnComponentOk.
A tRowGenerator is used to generate random data. Double-click on the tRowGenerator component to launch the editor.
Define the schema of the rows to be generated and the nature of data to generate. In this example, the clients file to be produced will contain the following columns: ID, First Name, Last Name, Address, City which all are defined as string data but the ID that is of integer type.
Some schema information don't necessarily need to be displayed. To hide them away, click on Columns list button next to the toolbar, and uncheck the relevant entries, such as Precision or Parameters.
Use the plus button to add as many columns to your schema definition.
Click the Refresh button to preview the first generated row of your output.
Then select the tMap component to set the transformation.
Drag and drop all columns from the input table to the output table.
Apply the transformation on the LastName column by adding
.toUpperCase()in its expression field.
Then, click OK to validate the transformation.
Double-click on the tMysqlOutputBulk component.
Define the name of the file to be produced in File Name field. If the delimited file information is stored in the Repository, select it in Property Type field, to retrieve relevant data. In this use case the file name is clients.txt.
The schema is propagated from the tMap component, if you accepted it when prompted.
In this example, don't include the header information as the table should already contain it.
Click OK to validate the output.
Then double-click on the tMysqlBulkExec component to set the INSERT query to be executed.
Define the database connection details. We recommend you to store this type of information in the Repository, so that you can retrieve them at any time for any Job.
Set the table to be filled in with the collected data, in the Table field.
Fill in the column delimiters in the Field terminated by area.
Make sure the encoding corresponds to the data encoding.
Press Ctrl+S to save your Job.
Press F6 or click Run on the Run tab to execute the Job.
The clients database table is filled with data from the file including upper-case last name as transformed in the job.
For simple Insert operations that don't include any transformations, the use of tMysqlOutputBulkExec allows you to skip a step in the process and thus improves performance.
Related topic: tMysqlOutputBulkExec properties