Create an integration job to export data

  • Updated

You can configure an SFTP integration connection and 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 > Connections.
  2. Click Add Integration Connection.
  3. Complete the following fields:
    • Name the connection, such as SFTP.
    • Select SFTP for the Type Name.
    • Select your time zone for the Source Server Time Zone.
    • Select Internal or WIS for Runs On. If you use Internal, the Type Names should be limited to Internal, API endpoints, or a remote file system like S3 or Azure. Other types may not work as expected.
    • Toggle on debugging.
    • Enter your SFTP URL for the Host Name.
    • Enter your SFTP site user name for User Name.
    • Enter your SFTP site password for Password.
    • Enter a folder name for the Default Folder.

    SFTP.png

  4. Click Save.

Create the job definition

  1. Go to Administration > Job Definitions.
  2. Click Add Job Definition to create a new Job Definition.
  3. Fill-in the following fields:
    • Enter a Job Name, such as SFTP Flat File.
    • 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 the field if it does not display in the drop-down list.

    SFTP Job Definition.png

  4. Go to Recurrence and toggle Run as a Recurring Job to Yes. Fill in the fields if you want to automate this job.
  5. Click Save.

Add the job definition parameters

  1. Go the Parameters tab.
  2. Click Add Job Definition Parameter to add the first parameter.
  3. Fill-in the following fields:
    • Enter a Name, such as FlatFileType.
    • Select String for the Value Type.
    • Enter csv for the Default Value.
    • Enter FlatFileType for the Prompt.
  4. Click Save & Add New.
  5. Fill-in the following fields:
    • Enter a Name, such as Directory.
    • Select String for the Value Type.
    • Enter \userfiles for the Default Value.
    • Enter Directory for the Prompt.
    • Click Save.

    SFTP Parameters.png

Add the job definition step

  1. Go to the Steps tab.
  2. Click Add Job Definition Step.
  3. Fill-in the following fields:
    • Enter 1 for the Sequence.
    • Enter a Step Name, such as Cart History.
    • Select Cart for the Target Object.
    • Select SFTP Connection for the Connection Override. If you want to save the file locally, this step is not needed. If you do not see the SFTP Connection you created, go back to your connection and change the Type Name to Flat File. Try this step again and you should see SFTP in the drop-down list. Return to your SFTP Connection and change the Type Name back to SFTP.
    • Select Ignore for Action.
    • Enter OrderNumber and any other desired information for the Select Clause.
    • Enter CustomerOrder for the From Clause.
    • Enter Status='Submitted' for the Where Clause.

    SFTP Job Definition Step.png

  4. Click Save.

Schedule the job

Schedule the job, and 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 (...) and select Schedule Job.
  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 tab and go to Recurrence. Select YES to Run as a Recurring Job and fill-in the fields that display.

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"  }]