Notifications
You currently have no notifications!
INTEGRATION
IRP
Common Tasks (1)
Interventions (1)
Languages (1)
Questions & Answers (1)
Reviews (1)
Site Scripts (1)
Web Services (1)
Training Videos
Peer Support

Overview - New (Integration)

How Tos (1)

You can find this section under Integration > Overview - New in the IRP Admin left navigation menu.

The screen presents you with an overview of ‘dynamic’ integrations on the IRP. The screen also provides you with summary log files that show what integration actions have been carried out and when they occurred, whether or not those actions were successful and details such as the name of the IRP Admin users who performed the integrations.

This is useful information because, if you notice that some data has been incorrectly formatted or that it is wrong in some way or other on your website, you need to know when the integration was carried out and by whom. You can then proceed to contact the user for more details, if necessary. You can then go on to correct the errors.

Dynamic Integration Overview Screen

The main dynamic integration screen (IntegrationOverview.aspx) appears as follows (note that what you see here depends on what integrations have taken place and so your screen may not look exactly like the screen capture that follows):

Dynamic Integration Overview screen

The screen includes the following summary information:

  • Last Integrated: This is the date and time when this integration was last run.
  • Integration Type: This is the type of this integration or the main database tables it performs operations on (products, stock levels, orders, pricing or other).
  • Integration File Last Uploaded: This is the date and time when data was last uploaded for this integration.
  • Integration Data Last Staged: This is the date and time that data was last staged to the database as part of this integration.
  • Integration SQL Set Up: This indicates whether the SQL query has been set up for this integration.
  • Automation Enabled: This indicates whether automation is allowed for this integration.
  • Data Manipulation Steps: This is the number of data manipulation steps that have been set up as part of the data staging step in this integration.
  • Rows in Staging Table: This is the number of rows in this integration’s staging database table.
  • Has Automation URL: This specifies whether this integration has a URL enabling it to be automated via a common task.
  • Historical Files: This is the number of history log entries made against this integration.

Note: If previous integrations have been carried out, you can click the Download Latest File button on the IntegrationEdit.aspx page to download the latest uploaded integration file

You can use the following buttons to edit the integration details or carry out the integration:

  • Edit: Click this button to go to the IntegrationEdit.aspx screen for the integration. From here you can modify many of the integration details that were defined when the integration was created.
  • Perform: Click this button to manually execute the integration.

You can add dynamic integrations by clicking the Add New Integration button at the top of the screen. Also you can click the Legacy Integrations button to go to the Legacy Integrations Overview screen, as described in the Legacy Integrations help topic.

You can add Integrations by clicking the Add New Integration button at the top of the screen.

Also you can click the Legacy Integrations button to go to the main UniversalIntegrationOverview.aspx screen where you can see an overview of legacy Integrationintegrations, as described in the Legacy Integrations help topic.

History Log

Click the History Log button at the top of the page to see the following summary information. You can use the search bar to find particular files and if you expand Other Options under the Search bar, you can search by Integration or Admin User:

  • ID: This shows the ID of the entry in the log file.
  • User: This shows the username of the IRP Admin user who carried out the integration.
  • Integration: This shows the name of the integration.
  • Notes: This shows a description of the integration operation, for example 'Integration Updated', 'Custom Manipulation Data Manipulation Step Added', 'Automated Integration Attempted but No Data Sent in Communication', etc .
  • Success: This shows whether the action was successful (True) or unsuccessful (False).
  • TimeStamp: This shows the date and time when the user carried out the action.

Application Settings for Integrations

You can use the following Application Settings for Integrations (these are located in the 'Integration' grouping):

  • Universal Integration Custom File Has Column Names: Set to true if the Stock file uploaded using the Universal Stock Integration has column headers.
  • Universal Integration Custom Integration Query: The Stock Integration SQL Query for Integrating Stock using the Universal Stock Integration functionality.
  • Universal Integration Stock File Has Column Names: Set to true if the Stock file uploaded using the Universal Stock Integration has column headers.
  • Universal Integration Stock Integration Query: The Stock Integration SQL Query for Integrating Stock using the Universal Stock Integration functionality.
  • Universal Integration Stock Levels File Has Column Names: Set to true if the Stock Levels file uploaded using the Universal Stock Levels Integration has column headers.
  • Universal Integration Stock Levels Integration Query: The Stock Levels Integration SQL Query for Integrating Stock Levels using the Universal Stock Level Integration functionality.


How To Guides (1)

To add an integration, follow these steps:

  1. Define Integration Information
  2. Select any Data Manipulation Steps
  3. Define the SQL Script
  4. Perform the Integration
  5. If necessary, you can also:

  6. Check the History Log

1. Define Integration Information

  1. Go to Integration > Overview in the IRP Admin left navigation menu.
  2. Click the Add New Integration button at the top of the screen.
    The Add New Integration screen appears. Enter the following details:
  3. SettingDescription
    Integration Details tab
    Integration ID This unique identifier is added automatically by the IRP.
    Integration Name Enter a name to identify this integration.
    File Has Headers Check this box if the file that is uploaded as part of this integration has column headers in its first row.
    Allow Files with No Data Are there occassions when Integration files will be uploaded without any data? If left unticked, an error will be thrown when manually or automatically performing Integrations on files with no data. If ticked, the Integration process stops without an error being thrown. For example, you may use this setting if the Integration is an Orders Integration that sends only new data that may or may not be present.
    Save Historical Files Choose to save copies of the files being Integrated via this Integration.
    Log Retention Period Enter the number of days to keep audit entries and files for this integration. By default this is set to seven days. Any that are further away than this value will be removed daily by the 'Clean Up Integration Logs' Maintenance Task. This Maintenance Task will:
    • Run daily in the morning and will delete any audit entries older than the Log Retention Period on a per-integration basis.
    • Only delete standard audit entries and will never delete any entries caused by the SQL of the integration being edited.
    • Also delete historical integration files and integration report files older than the Log Retention Period.
    Integration Type Select the type of integration (or the main database tables that it performs its operations on) from the drop-down list. Choose from Products, Stock Levels, Orders or Other.
    Description Optionally, enter a description of this integration, explaining what it is used for. You can use this setting to detail release notes or technical information about the integration, its complexities and any changes that have been made to it since its inception.
    Image Processing tab
    Has Image Processing Element: If checked, at the end of the Integrate step of this Integration, a 'Process Product Images' task will be run. The Integration Images Staging table will be checked for values in its two columns: ModelID and ImageURLs. This table should be populated via the SQL Script if Images are to be processed as part of this Integration.
    Images DB Table Name The name of the Images Staging database table that should be referenced from the SQL Script tab if Image processing is enabled on this Integration. This table contains two columns: ModelID int, and ImageURLs nvarchar(max) and should be populated with this in mind.
    Download Username Username (if required) for downloading Images located at the URLs in the ImageURLs column of the Images staging table.
    Download Password Password (if required) for downloading Images located at the URLs in the ImageURLs column of the Images staging table.
    Automation tab
    Enable Automation Check this box if you want to allow automation for this integration. If you do check this box the remaining Automation settings described below appear.
    Disable Integration Step for Automations Check this box to stop automated executions of this integration from final processing.
    Automation Type Select from the drop-down list the type of automation used to perform this integration:
    HTTP Post: files or form data sent over HTTP or HTTPS to the Posting Page URL as detailed below. This can be performed as often as is configured by the third-party software performing the Automation. Note that files can also be downloaded from FTP and FTPS URLs.
    URL Fetch: hosted files downloaded from the Automation URL entered below and subsequently staged and then integrated. This Automation can be scheduled via a Common Task which can be set up via the Common Tasks IRP Admin section.
    Automation URL The URL where an Integration file is hosted before it is downloaded and subsequently Staged and Integrated. This can be either an https or ftps URL. If using an FTP URL, please append the port number (even if standard) to the end of the URL, e.g. ftp:\\1.2.3.4:21\myfile.txt
    Please Note, this External URL may have to go through standard security policy and validation checks before it is accepted. Please Contact your SI for verification.
    Automation File Format The Format of the file being hosted at the Automation URL. This will determine what format your IRP will save the hosted file as.
    Download Username The username required to download the file. This should only be entered if required by the URL you are downloading.
    Download Password The password required to download the file. This should only be entered if required by the URL you are downloading.
    Post Data Format If integration data is being sent to the posting page via HTTP as form data, specify here its format so that it can be correctly processed prior to the staging and final integration stages. Leave this set to ‘Full File Posted‘ if the full integration file is being sent to the posting page via HTTP. Otherwise you can set this to ‘Comma Delimited in Form Data’, ‘Tab Delimited in Form Data’ or ‘Pipe Delimited in Form Data’.
    Posting Page URL (Windows Login) The URL to be used for posting integration data over HTTPS. This URL is behind a pre-agreed Windows Login so any communication will need to take that Username and Password otherwise a 401 unauthorised response will result. This URL can be used to post form data or an integration file directly.
    Click the View URL button to visit the page at the URL defined.
    Posting Page URL (IP Restricted) The URL to be used for posting integration data over HTTPS. This URL is NOT behind a Windows Login but instead will require the requesting static IP address to be added to the list of Permitted IP Addresses. This URL can be used to post form data or an integration file directly.
    Click the View URL button to visit the page at the URL defined.
    Uncache Site Check this box if you want to update the cached version of the website after the integration has completed.

  4. Click the Insert Integration button.
    If successful, you will see the following message at the top of the screen:
  5. Integration Successful message

2. Select any Data Manipulation Steps

You use data manipulation steps to manipulate/edit the data you are uploading into a compatible format so that it can be imported into your IRP database successfully. This section allows you to set the step/s which will be performed on the import data. The steps will be repeated automatically, each time an integration is performed. There are a number of different manipulations that can be performed on an import file, as described in the table below.

  1. Click the Data Manipulation Steps tab to add data manipulation steps to run as part of this integration’s staging database table.
  2. Enter the following details:
  3. SettingDescription
    Step Type Select a built-in function from the drop-down list with which to manipulate the staged data prior to final integration. See the table below for details of each function.

    The possible functions are as follows:

    FunctionDescription
    Add New Field This allows you to add a new field to the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • New Field Name: The name of the new field to be added to the import table.
    • New Field Type: The database type of the new field to be added to the import table. Select from: bit, decimal, int, money, datetime, ntext, varbinary, smallint or nvarchar.
    • New Field Size: The size of the new field to be added to the import table.
    • New Field Default: The default value of the new field to be added to the import table.
    • Allow Nulls: Check this box to permit null values.
    • In the example below, the EntryDate value is being added to record the date that the data was entered. A default value of the current date is being set using the GETDATE() command.

      Add New Field
    Append Text This allows you to append text to an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Text to Append: The text to append to your specified field.
    • Field Name: The field to append the specified text to.
    • Position: The position in the field that you wish to append the specified text to (Start or End).
    • In the example below, the text ‘Brand Y’ will be added to the beginning of the Model field.

      Append Text
    Capitalize Field This allows you to capitalize the first character of an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field Name: The name of the import field to capitalize.
    • In the example below, the first letter of the value stored in the Brand field will be capitalized in each record of the import file.

      Capitalize Field
    Change Field Type This allows you to change the Data Type and/or Size of an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field to Change: The name of the field to change the field type of.
    • New Type: The field type to change the field to. Select from: bit, decimal, int, money, datetime, ntext, varbinary, smallint or nvarchar.
    • Size: The size of the new field type.
    • In the example below, the Data Type of the Price Field will be changed to Money.

      Change Field Type
    Custom Manipulation This allows you to execute a SQL script against the staged data. (Note that this is different from the SQL script used on the ‘SQL Script’ tab which is used to actually perform the final integration step.) When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • SQL to Run: The custom SQL to run on data once it has been imported and just before final staging.
    • Max Execution Period (secs): This setting defines the maximum time that should be allowed for an integration data manipulation step to run and can take the following values:
      • Blank: Steps will be limited to no more than 30 seconds.
      • 0: Steps will have an unlimited time for execution.
      • >0: Steps will be limited to that number of seconds for execution.
      This feature means that steps that take slightly longer to run can bypass the system default time of 30 seconds, without throwing an exception due to timing out.
    • Import Table: The import table name to run custom manipulation SQL on.
    • In the example below, the Price value is being changed to the Price plus 10% where the original Price value is less than 5.

      Custom Manipulation
    Remove Field This allows you to remove an existing field from the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field to Remove: The name of the imported field to be removed from the dataset.
    • In the example below, the ManufacturerCode field will be removed from the import file.

      Remove Field
    Remove Quotation Marks This allows you to remove quotation marks from an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field Name: The imported field to remove quotation marks from.
    • In the example below, quotation marks will be removed from the Model field.

      Remove Quotation Marks
    Remove Text This allows you to remove text from an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Text to Remove: The text you wish to remove from the specified field.
    • Field Name: The field you wish to remove the specified text from.
    • Position: The position in the field you wish to remove the specified text from (Start, Anywhere or End).
    • In the example below, the text ‘Brand X’ will be removed from anywhere in the Model field.

      Remove Text
    Remove Whitespace This allows you to remove all whitespace from an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field Name: The name of the import field to remove whitespace from.
    • In the example below, all whitespace in the PartCode field will be removed in each record of the import file.

      Remove Whitespace
    Rename Field This allows you to rename an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field to Rename: The name of the field you wish to rename.
    • New Name: The name you wish to rename the field to.
    • In the example below, the ManufacturerCode field will be renamed PartCode in the import file.

      Rename Field
    Replace Text This allows you to replace text in an existing field with a different value in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Text to Replace: The text to replace in your selected field.
    • Replace with: The text you wish to replace the specified text with in your selected field.
    • Field Name: The field you wish to perform the replacement in.
    • In the example below, the text ‘Size 8’ is being replaced with ‘Small’ in the Option field.

      Replace Text
    Set Field Value This allows you to set a default value for all records in an existing field in the import file. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field Name: The imported field to set the value of.
    • Value to Set: The value to set the specified field to.
    • In the example below, a default value of ‘1’ will be set against all records of the StockHeldExternally field.

      Set Field Value
    Split Field This allows you to split the value held in one field into two separate fields. When you select this option you can then fill in the following details:
    • Execution Order: Enter the order in which this data manipulation step should be performed.
    • Field to Split: The field to be split.
    • String to Split on: The string to split the field on.
    • Resultant Fields: The two resultant field names to split the initially specified field into.
    • In the example below, the ManufacturerCode field contains values such as 'CAT01-15895'. This value contains the Category ID of ‘CAT01’ and the product PartCode of ‘15895’. The values are separated by the ‘-’ character. This will split these two values into separate fields called Category and PartCode.

      Split Field

  4. To add any of the manipulation steps above, click the Add Data Manipulation Step button.

3. Define the SQL Script

This tab is where you define the integration SQL query that will run to import the data from the import file/staging table to the various tables in your IRP database. Without the query, integration is not possible.

You must have IsDBA permission to use this section as an understanding of SQL, IRP database structure and your data is required to create the import script. We advise that you contact the Support Team regarding the creation of this query.

  1. Click the SQL Script tab to configure a SQL script to run as part of this integration.
  2. The screen appears as follows:

    SQL Script screen

  3. Enter the following details:
  4. SettingDescription
    DB Table Name This is the integration staging database table name that should be used in the SQL code.
    Integration SQL This is the SQL that performs the final integration stage. This SQL should be set up to run primarily using data from the integration staging database table. Note that the SQL will be padded by the following code:

    TOP:
    BEGIN TRANSACTION IntegrationTransaction

    DECLARE @error INT
    @error = 0

    BOTTOM:
    IF @error > 0
    ROLLBACK TRANSACTION IntegrationTransaction
    ELSE
    BEGIN
    UPDATE [IntegrationStagingTableName] SET [timestamp_integrated] = GETDATE()
    COMMIT TRANSACTION IntegrationTransaction
    END


    The following SQL should be inserted after each statement block to specify whether an error has occurred:

    SET @error = @error @@ERROR

  5. Click the Validate SQL button.
    If successful, you will see the following message at the top of the screen:
  6. SQL Successful message
  7. Click the Update Integration button. If successful, you will see the following message at the top of the screen:
  8. Update Successful message

4. Perform the Integration

Note that the integration setup steps must be completed every time the import file format changes.

To perform the integration and update the data held in your IRP database, click the Perform button on the main UniversalIntegrationOverview.aspxpage.

There are three stages to the integration process:

  1. Upload the import file.
  2. Stage the data.
  3. Integrate the staged data.

To perform these steps, proceed as follows:

Upload the import file

  1. Click the Browse button to locate the integration data file for import:
  2. Upload File

  3. Click the Upload button to upload the integration file.

Stage the data

  1. Click the Stage button to stage the data from the uploaded integration data file:
  2. Stage the data

  3. A sample of the data that will be imported will be displayed in the Sample of Imported Stock Data section. If you are happy with the sample data, proceed to the next step. Otherwise, make any necessary changes to the Data Manipulation Steps and repeat the last two steps, until correct.

Integrate the staged data

  1. Click the Integrate button to integrate the staged data using the SQL query:
  2. Integrate the staged data

Response Codes for Third-Party Automated Integrations Using HTTP Post

If third parties are using HTTP Post to send data to the Posting Page URL, you may see the following response codes:

  • case 0:
    Automated Integration Successful
  • case 1:
    Error Uploading Integration data
  • case 2:
    Error Staging Uploaded file
  • case 3:
    Error Integrating Staged data
  • case 10:
    Integration does not Exist
  • case 20:
    Automation Disabled for this Integration
  • case 30:
    HTTP Post not enabled for this Integration
  • case 40:
    No Post Data Received - Please Check Automation Configuration to ensure Post Data Format is correct

5. Check the History Log

  1. Click the History Log tab to view the history log for this integration.
  2. Optionally, expand Other Options under the search bar to search by any user. After you click the Search button you can see the following details in the grid:
    • ID: This shows the ID of the entry in the log file.
    • User: This shows the username of the IRP Admin user who carried out the integration.
    • Integration: This shows the name of the integration.
    • Notes: This shows the action that the user carried out, for example, “Integration Updated”.
    • Success: This shows whether the action was successful (True) or unsuccessful (False).
    • TimeStamp: This shows the date and time when the user carried out the action.

Copyright © 2019 IRP Commerce. Use of this website constitutes acceptance of the IRP World Terms of Use, IRP Privacy Policy and IRP Cookie Policy
IRP Commerce is a Trading Name of Export Technologies Limited, Concourse 2, Catalyst, Belfast, BT3 9DT, UK. Registered in Northern Ireland, Number: NI 041856. VAT Number: GB 888249658
A Deloitte Fast 50 Company six times: 2010, 2011, 2012, 2013, 2014 & 2018