Scenario: Retrieving data in error with a Reject link
This scenario describes a four-component Job that carries out migration from a
customer file to a MySQL database table and redirects data in error towards a CSV file
using a Reject link.
In the Repository, select the customer file
metadata that you want to migrate and drop it onto the workspace. In the
[Components] dialog box, select tFileInputDelimited and click OK. The component properties will be filled in
If you have not stored the information about your customer file under
the Metadata node in the Repository. Drop a tFileInputDelimited component from the family File > Input, in
the Palette, and fill in its properties
manually in the Component tab.
From the Palette, drop a tMap from the Processing family onto the workspace.
In the Repository, expand the Metadata node, followed by the Db Connections node and select the connection required to
migrate your data to the appropriate database. Drop it onto the workspace. In
the [Components] dialog box, select tMysqlOutput and click OK. The database connection properties will be automatically
If you have not stored the database connection details under the Db Connections node in the Repository, drop a tMysqlOutput
from the Databases family in the Palette and fill in its properties manually in the
For more information, see
From the Palette, select a tFileOutputDelimited from the File > Output family, and
drop it onto the workspace.
Link the customers component to the
tMap component, and the tMap and Localhost
with a Row Main link. Name this second link
Link the Localhost to the tFileOutputDelimited using a Row > Reject link.
Double-click the customers component to
display the Component view.
In the Property Type list, select Repository and click the […]
button in order to select the metadata containing the connection
to your file. You can also select the Built-in
mode and fill in the fields manually.
Click the […] button next to the File Name field, and fill in the path and the name of
the file you want to use.
In the Row and Field
Separator fields, type in between inverted commas the row and
field separator used in the file.
In the Header, Footer and Limit fields, type
in the number of headers and footers to ignore, and the number of rows to which
processing should be limited.
In the Schema list, select Repository and click the […] button in order to select the schema of your file, if it
is stored under the Metadata node in the
Repository. You can also click the
[…] button next to the Edit schema field, and set the schema manually.
The schema is as follows:
Double-click the tMap component to open its
Select the id, CustomerName,
id2, RegTime and
RegisterTime columns on the table on the left and drop
them on the out table, on the right.
In the Schema editor area, at the bottom of
the tMap editor, in the right table, change the
length of the CustomerName column to
28 to create an error. Thus, any data for which the
length is greater than 28 will create errors, retrieved with the Reject link.
In the workspace, double-click the output Localhost component to display its Component view.
In the Property Type list, select Repository and click the […] button to select the connection to the database metadata.
The connection details will be automatically filled in. You can also select the
Built-in mode and set the fields
In the Table field, type in the name of the
table to be created. In this scenario, we call it
In the Action on data list, select the
Create table option.
Click the Sync columns button to retrieve
the schema from the previous component.
Make sure the Die on error check box isn’t
selected, so that the Job can be executed despite the error you just
Click the Advanced settings tab of the
Component view to set the advanced
parameters of the component.
Deselect the Extend Insert check box which
enables you to insert rows in batch, because this option is not compatible with
the Reject link.
Double-click the tFileOutputDelimited
component to set its properties in the Component view.
Click the […] button next to the File Name field to fill in the path and name of the
Click the Sync columns button to retrieve
the schema of the previous component.
Save your Job and press F6 to execute it.
The data in error are sent to the delimited file, as well as the error type met. Here,
we have: Data truncation.