Export data integration job

7 min

This article outlines how to set up an SFTP integration connection, create a recurring integration job to automate data exports from Optimizely {user["product-name"]}, and deliver files to an SFTP site.

SFTP support The OOTB for SFTP only supports exports and does not support imports or reading from a specific folder.

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.

Go to Administration> Jobs > Connections.

Click Add Integration Connection.

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

Click Save.

Change the storage provider setting (save locally only)

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

Go to Administration> System > Settings and search for Storage Provider.

Select FileSystem from the Storage Provider drop-down menu. When the job runs, it will save the exported data file under the specified storage provider directory.

Click Save.

Create the job definition

Go to Administration> Jobs > Job Definitions.

Click Add Job Definition to create a new Job Definition.

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 doesn't show up in the drop-down menu.)

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.

Click Save.

Add the job definition parameters

Select the Parameters finger tab.

Click Add Job Definition Parameter to add the first Job Definition Parameter.

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

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

Click Save then Back.

Add the job definition step

Select the Steps finger tab.

Click Add Job Definition Step.

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 don't 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

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.

Click More Options and select Schedule Job. The Schedule Job window appears.

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.

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