Create an integration job to export data

  • Updated

This article outlines how to set up an SFTP integration connection, create a recurring integration job to automate data exports from Optimizely Configured Commerce and deliver files to an SFTP site.

Create the SFTP connection (save to SFTP site only)

Perform the steps in this section only if you want to deliver files to an SFTP site. If you want to save the exported data files locally, you do not need to complete this section.

  1. Go to Administration > Jobs > Connections.
  2. Click Add Integration Connection.
  3. Fill-in the following fields:
    • Enter SFTP Connection for the Name
    • Select SFTP for the Type Name
    • Select your time zone for the Source Server Time Zone
    • Enter your SFTP URL for the Url
    • Enter your SFTP site user name for the User Name
    • Enter your SFTP site password for the Password
    • Enter a folder name for the Default Folder

  4. Click Save.

Create the job definition

  1. Go to Administration > Jobs > Job Definitions.
  2. Click Add Job Definition to create a new Job Definition.
  3. Fill-in the following fields:
    • Enter a name for the Job Name that is Export Cart History
    • Select Internal for the Connection
    • Select SqlQuery for the Preprocessor
    • Leave None for the Integration Processor
    • Select SftpFlatFile for the Post Processor (Enter SftpFlatFile in this field if it does not show up in the drop-down menu.)

  4. Scroll to the Recurrence section. Select YES to Run as a Recurring Job and fill-in the fields that appear if you want to automate this job.
  5. Click Save.

Add the job definition parameters

  1. Select the Parameters finger tab.
  2. Click Add Job Definition Parameter to add the first Job Definition Parameter.
  3. Fill-in the following fields:
    • Enter a name for the Name, that is FlatFileType
    • Select String for the Value Type
    • Enter csv for the Default Value
    • Enter FlatFileType for the Prompt
  4. Click Save & Add New.

    Fill-in the following fields:

    • Enter a name for the Name, that is Directory
    • Select String for the Value Type
    • Enter \userfiles for the Default Value
    • Enter Directory for the Prompt

  5. Click Save then Back.

Add the job definition step

  1. Select the Steps finger tab.
  2. Click Add Job Definition Step.
  3. Fill-in the following fields:
    • Enter 1 for the Sequence
    • Enter a name for the Step Name, that is Cart History
    • Select Cart for the Target Object
    • Select SFTP Connection for the Connection Override (if you want to save the file locally, this is not needed)

      If you do not see the SFTP Connection you created in the first section, go back to your SFTP Connection and change the Type Name to something else (that is Flat File). Try this step again and you should see your SFTP Connection in the drop-down menu. Then, return to your SFTP Connection and change the Type Name back to SFTP.

    • Select Ignore for Action under Delete Behavior
    • Enter OrderNumber (and any other desired information) for the Select Clause
    • Enter CustomerOrder for the From Clause
    • Enter Status='Submitted' for the Where Clause

  4. Click Save then Back.

Schedule the job

Schedule the job, then it will save the exported data file either under the specified storage provider directory or under the Default Folder you specified in your SFTP Connection.

  1. Click More Options and select Schedule Job. The Schedule Job window appears.
  2. Select the date and time and whether this job should run in real time (more frequently than the default interval the WIS uses to ping your website), then click Schedule Job.

    If you want to set this job up as a recurring job, select the Details finger tab and scroll to the Recurrence section. Select YES to Run as a Recurring Job and fill-in the fields that appear.

Sample of the completed job definition

Code Sample: SFTPFlatFile DataExport (JSON)

[  {	"Id": "e0d597da-9327-44c2-a67d-d6a81ddaf89a",	"IntegrationConnectionId": "794318b7-0b88-44de-8403-a7d7013e3bb3",	"StandardJobName": "",	"Name": "SFTP Flat File",	"Description": "",	"JobType": "",	"DebuggingEnabled": true,	"PassThroughJob": false,	"NotifyEmail": "",	"NotifyCondition": "Completion",	"LinkedJobId": null,	"PassDataSetToLinkedJob": false,	"UseDeltaDataSet": false,	"PreProcessor": "SqlQuery",	"IntegrationProcessor": "None",	"PostProcessor": "SftpFlatFile",	"LastRunDateTime": null,	"LastRunJobNumber": "",	"LastRunStatus": "",	"RecurringJob": false,	"RecurringStartDateTime": null,	"RecurringEndDateTime": null,	"RecurringInterval": 1,	"RecurringType": "Days",	"RecurringStartTime": null,	"RecurringStartDay": 0,	"EmailTemplateId": null,	"RunStepsInParallel": false,	"LinkedJobCondition": "SuccessOnly",	"MaxErrorsBeforeFail": 0,	"MaxWarningsBeforeFail": 0,	"MaxRetries": 0,	"MaxDeactivationPercent": 0,	"MaxTimeoutMinutes": 0,	"IntegrationConnection": null,	"JobDefinitionParameters": [	  {		"Id": "7db7b654-d242-43f0-9487-268cea683446",		"JobDefinitionId": "e0d597da-9327-44c2-a67d-d6a81ddaf89a",		"Sequence": 0,		"ValueType": "String",		"DefaultValue": "csv",		"Prompt": "FlatFileType",		"Name": "FlatFileType",		"CustomProperties": [],		"CreatedOn": "2018-08-27T15:38:14.8044378+00:00",		"CreatedBy": "admin_sa",		"ModifiedOn": "2018-08-27T15:38:14.8044378+00:00",		"ModifiedBy": "admin_sa"	  },	  {		"Id": "2c3a6aca-7df3-45fa-aa12-e4b189f59391",		"JobDefinitionId": "e0d597da-9327-44c2-a67d-d6a81ddaf89a",		"Sequence": 0,		"ValueType": "String",		"DefaultValue": "\\userfiles",		"Prompt": "Directory",		"Name": "Directory",		"CustomProperties": [],		"CreatedOn": "2018-08-27T15:38:14.8094218+00:00",		"CreatedBy": "admin_sa",		"ModifiedOn": "2018-08-27T15:52:45.5604653+00:00",		"ModifiedBy": "admin_is\\ikhoh"	  }	],	"JobDefinitionSteps": [	  {		"Id": "b69d68a3-bf64-4184-81ef-01d1cf0bd5e5",		"JobDefinitionId": "e0d597da-9327-44c2-a67d-d6a81ddaf89a",		"Sequence": 1,		"Name": "Cart History",		"ObjectName": "customerOrder",		"IntegrationConnectionOverrideId": null,		"IntegrationProcessorOverride": "",		"IntegrationConnectionOverride": null,		"SelectClause": "OrderNumber",		"FromClause": "CustomerOrder",		"WhereClause": "Status='Submitted'",		"ParameterizedWhereClause": "",		"DeleteAction": "Ignore",		"DeleteActionFieldToSet": "",		"DeleteActionValueToSet": "",		"DeleteChildren": false,		"SkipHeaderRow": true,		"FlatFileErrorHandling": "",		"JobDefinitionStepFieldMaps": [],		"JobDefinitionStepParameters": [],		"IntegrationQuery": null,		"CustomProperties": [],		"CreatedOn": "2018-08-27T15:38:14.8114201+00:00",		"CreatedBy": "admin_sa",		"ModifiedOn": "2018-08-27T16:35:31.0534093+00:00",		"ModifiedBy": "admin_is\\ikhoh"	  }	],	"IntegrationJobs": [],	"CustomProperties": [],	"CreatedOn": "2018-08-27T15:38:14.7934212+00:00",	"CreatedBy": "admin_sa",	"ModifiedOn": "2018-08-27T15:38:28.8153805+00:00",	"ModifiedBy": "admin_is\\ikhoh"  }]