Example - Date Conversion
This example describes how to create a simple date conversion integration template in the Cloud Integration Template Designer, import the template to Informatica Cloud, and create a Mapping Configuration task.
The Date To String template converts date values in a database source to string values for a file or database target.
Example Step 1. Configure and Publish the Date To String Integration Template
Use the Cloud Integration Template Designer to configure and publish the Date To String integration template.
Use objects in the Informatica stencil to create the data flow in an integration template. Add and configure objects and links. When the template is complete, validate and publish the template. Then create an image file. You will use the image file when you import the integration template to your Informatica Cloud organization.
1. To create a new template, click File > New > Custom Integration Template > Create.
2. To save and name the file, click File > Save. Select a local directory and name the file: DateToString.vsd.
VSD is the default file type for the Cloud Integration Template Designer.
3. From the Informatica stencil, add a Source Definition object to the template. Double-click the Source Definition icon.
4. In the Source Definition Details dialog box, configure the following source definition properties.
To configure a property, select the property you want to configure. In the Property area, enter the value you want to use, and click Apply.
Source Definition Property | Value |
|---|
Transformation Name | $DBsrc$ |
Source Table | $DBsrc$ |
For sources, use the same template parameter name for the transformation name and source table. Sources are always template parameters, regardless of whether you enclose the name in dollar signs, but use dollar signs to clearly indicate it is a template parameter to other users.
5. Add a Source Qualifier object to the data flow, and configure the following property:
Source Qualifier Property | Value |
|---|
Transformation Name | SQ |
6. Add a Link object between the Source Definition to the Source Qualifier. Double-click the link to configure link rules.
7. To name the link, in the Link Rules dialog box, in the Rule Set Name field, enter All.
8. To configure a link rule that moves all data from the source to the source qualifier, click New Rule. In the Define Link Rule dialog box, click Include, click All Ports, and click OK.
9. To save your changes and close the Link Rules dialog box, click OK.
10. To configure the expressions to convert dates to strings, add an Expression object to the data flow. On the Property tab of the Expression Details dialog box, configure the following property:
Expression Details | Value |
|---|
Transformation Name | EXP_DateConversion |
11. On the Configuration tab, click New Expression, then configure the following details and click Apply.
This expression declares the macro variable name. It also sets up the "port" variable to represent all ports, and defines the macro variable fields to include all ports.
New Expression Details | Value |
|---|
Port Name | Declare_%enums% |
Expression | {"port":"All Ports"} |
12. Configure another expression as follows and click Apply.
The macro statement defines the output ports. With the use of the "port" variable, it names output ports "<output port>_o".
The macro expression determines if a port contains a date of the $fromdateformat$ template parameter format. If it does, it converts the date to the $todateformat$ template parameter format. Then, it converts the date to a string.
New Expression Details | Value |
|---|
Port Name | %port%_o |
Expression | iif(IS_DATE(%port%,'$fromdateformat$'),TO_CHAR(TO_DATE(%port%,'$fromdateformat$'),'$todateformat$'),%port%) |
13. To configure a link rule that moves all data from the source qualifier to the Expression object, click New Rule.
In the Define Link Rule dialog box, click Include, click All Ports, and click OK.
To save the link rule, click OK.
14. Add a Target Definition object to the data flow. Configure the target definition as follows.
Target Definition Property | Value |
|---|
Transformation Name | $tgt$ |
Target Table | $tgt$ |
15. To configure a link rule that moves data from the Expression object to the target definition, click New Rule.
In the Define Link Rule dialog box, click Include.
To include all ports that end in "_o", with click Pattern and for Starting Port Pattern, enter "_o$". Click OK.
To save the link rule, click OK.
16. To validate the integration template, on the Informatica toolbar, click Validate Mapping Template.
17. To save the integration template, click File > Save.
18. To create an image file, click File > Save As. Save the file as JPEG or PNG.
You can use the Arrange All icon on the Informatica toolbar to arrange the data flow before taking the screenshot.
19. To publish the integration template, on the Informatica toolbar, click Publish Template. Navigate to the directory you want to use, and click Save.
The Cloud Integration Template Designer creates the template XML file.
20. Save and close the integration template.
Example Step 2. Import the Date To String Integration Template
Import the Date To String integration template to use it in your organization.
When you import the integration template, you can define template parameter descriptions, defaults, and display options. You can also import an image file to visually represent the data flow.
1. Click Design > Integration Template > New.
2. On the New Integration Template page, configure the following information.
Template Details Property | Value |
|---|
Template Name | Date To String . |
Template XML File | Select the DateToString.xml integration template file. After you select the file, the template parameters in the file display in the Parameters table. |
Template Image File | Select the JPG file that you created. After you select the file, the template image file displays. |
3. To configure the display properties for the $DBsrc$ template parameter, click Edit.
4. In the Edit Parameters Properties dialog box, configure the following options and click OK.
Edit Display Properties | Value |
|---|
Default Value | Do not configure a default value. |
Visible | Select Yes to display the template parameter in the Contact Validation task. |
Editable | Select Yes to allow the task developer to configure the source connection in the Contact Validation task. |
Required | Select Yes to require this template parameter to be set. |
Valid Connection Type | Determines the connection type allowed for the source. You can select a connection type that exists in your organization or select All Connection Types. Select Relational Database. |
Logical Connection | Do not configure. |
5. To configure the display properties for the $tgt$ template parameter, click Edit, configure the following options, and click OK.
Edit Display Properties | Value |
|---|
Default Value | Do not configure a default value. |
Visible | Select Yes to display the template parameter in the Contact Validation task. |
Editable | Select Yes to allow the task developer to configure the target connection in the Contact Validation task. |
Required | Select Yes to require this template parameter to be set. |
Valid Connection Type | Determines the connection type allowed for the source. Select Flat File or Relational Database. |
Logical Connection | Do not configure. |
6. Enter the following description for the $fromdateformat$ template parameter to help the task developer understand the information to provide: Source data format.
7. To configure the display properties for the $fromdateformat$, click Edit, configure the following options, and click OK.
Edit Display Properties | Value |
|---|
Default Value | Do not configure a default value. |
Editable | Select Yes to allow the task developer to configure the date format. |
Required | Select Yes to require this template parameter to be set. |
Input Control | Select Text Box. Displays a text box so the user can enter the date format. |
8. Enter the following description for the $todateformat$ template parameter to help the user understand the information to provide: Conversion data format.
9. To configure the display properties for the $todateformat$, click Edit, configure the following options, and click OK.
Edit Display Properties | Value |
|---|
Default Value | Do not configure a default value. |
Editable | Select Yes to allow the task developer to configure the date format. |
Required | Select Yes to require this template parameter to be set. |
Input Control | Select Text Box. Displays a text box so the user can enter the date format. |
10. To import the template using the configured template parameters, click OK.
Example Step 3. Create the Mapping Configuration Task
To process data using the Date to String integration template, create a Mapping Configuration task.
1. Click Task Wizards > Mapping Configuration > New.
2. Configure the task details as follows and click Next.
Task Detail | Value |
|---|
Task Name | Date to String |
Runtime Environment | Select the runtime environment that contains the Secure Agent to use to run the task. |
Integration Template | Select the Date To String integration template. After you select the template, the template image displays. |
3. On the Sources page, select a source connection and source object, and click Next.
Based on the template parameter properties, the wizard displays only database connections.
4. On the Targets page, select a target connection and target object, and click Next.
Based on the template parameter properties, the wizard displays file and database connections.
5. On the Other Parameters page, enter the date format that you want to use for $fromdateformat$.
For example: yyyy-mm-dd.
6. Enter the date format that you want to use for $todateformat$ and click Next.
For example: mm-dd-yyyy.
7. On the Schedule page, select schedule details, email notification options, advanced options, and click Save.
When you run the task, the Secure Agent reads data from the source, converts dates to the new format, and writes data to the selected target.