Overview - New (Integration)

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

If necessary, you can also check the History Log.

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.
3. Enter the following details:

   Setting	Description
   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 occasions 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:
   

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:

   Setting	Description
   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:

   Setting	Description
   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.
   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.
   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.
   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, char, datetime, decimal, int, money, ntext, nvarchar, smallint, varbinary or varchar. New Default: Use the 'Allow Nulls' setting to define whether or not this field should allow nulls and, if not, define the value to be used for any null fields using the 'New Default' setting. In the example below, the Data Type of the Price Field will be changed to Money and null values will be allowed.
   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.
   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 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 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 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.
   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.
   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.
   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.
   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.

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

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.
   The screen appears as follows:
   
2. Enter the following details:

   Setting	Description
   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

3. Click the Validate SQL button.
   If successful, you will see the following message at the top of the screen:
   
4. Click the Update Integration button. If successful, you will see the following message at the top of the screen:
   

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.aspx page. There are three stages to the integration process:

1. Upload the import file

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



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

2. Stage the data

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



2. 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.

3. Integrate the staged data

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

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

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.