Mappings > Visio templates > Visio template example
  

Visio template example

This example describes how to create the Date to String Visio template and use it in a mapping task. The Date to String template converts date values in a database source to string values for a file or database target.
The example includes the following tasks:
  1. 1. Configure the Date to String template in the Cloud Integration Template Designer.
  2. 2. Upload the Date to String template to Data Integration.
  3. 3. Create a mapping task that is based on the Date to String template.

Step 1. Configure the Date to String template

Use the Cloud Integration Template Designer to configure and publish the Date to String Visio template.
Use objects in the Informatica stencil to create the data flow in a Visio 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 upload the Visio template to your Data Integration organization.
    1. To create a new template, click File > New > Custom Visio 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 Visio template, on the Informatica toolbar, click Validate Mapping Template.
    17. To save the Visio 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 Visio 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 Visio template.

Step 2. Upload the Visio template

Upload the Date to String Visio template to use it in your organization.
When you upload the Visio template, you can define template parameter descriptions, defaults, and display options. You can also upload an image file to visually represent the data flow.
    1. Click New > Components > Visio Templates and then click Create.
    2. On the Visio Template page, configure the following information.
    Template details property
    Value
    Template Name
    Date to String
    Template XML File
    Select the DateToString.xml Visio 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 upload the template using the configured template parameters, click OK.

Step 3. Create the mapping task

To process data using the Date to String Visio template, create a mapping task.
    1. Click New > Tasks > Mapping Task. and click OK.
    2. Configure the task details as follows and click Next.
    Task detail
    Value
    Task Name
    Date to String
    Location
    Browse to the folder where you want to store the Date to String mapping task or use the default location.
    Runtime Environment
    Select the runtime environment that contains the Secure Agent to use to run the task.
    Mapping
    Click Select and browse to the Visio template, and then click Select.
    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 Input 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.