CData Software, Inc. - Salesforce to SQL Server Integration

CData Software, Inc.

DBAmp

SQL Server Integration with

Version 5.1.6

Copyright ? 2021 CData Software, Inc. All rights reserved.

Table of Contents

Acknowledgments........................................................................... 7 Chapter 1: Installation/Upgrading ................................................. 8 Upgrading an existing installation.........................................................8 Prerequistes .......................................................................................9 Running the DBAmp installation file......................................................9 Configure the DBAmp provider options .................................................9 Connecting DBAmp to SQL Server ...................................................... 10 Verifying the linked server ................................................................. 11 Install the DBAmp Stored Procedures ................................................. 11 Running the DBAmp Configuration Program........................................11 Setting up the DBAmp Work Directory................................................12 Enabling xp_cmdshell for DBAmp....................................................... 13 Pointing DBAmp to your Salesforce Sandbox Instance ......................... 13 Chapter 2: Using DBAMP as a Linked Server ................................ 14 Four Part Object Names .................................................................... 14 SQL versus SOQL .............................................................................. 14 Using the four part object name and SQL ........................................... 14 Using OPENQUERY and SOQL ............................................................ 15 Inserting rows using SQL...................................................................17 Updating and Deleting rows using SQL ............................................... 18 Joining Tables............................................................ 19 Analyzing Performance when Joining Tables ....................................... 19 Using BIT datatype with DBAmp ........................................................ 21 Using Dates with DBAmp ................................................................... 21 Using DBAmp System Tables (sys_sf tables) ....................................... 22 Using DBAmp System Views .............................................................. 23 Using Count() with objects..........................................25 Using DBAmp to convert currency amounts to a default currency.........25 Using DBAmp to return translated values for picklists .......................... 26 Retrieving Archived and Deleted records ............................................ 26 Using Column Subset views ............................................................... 26 DBAmp and Salesforce API call Counts ............................................... 27 Big Objects Support .......................................................................... 28

2

Platform Events Support .................................................................... 30 Chapter 3: Making Local Copies of Salesforce Data ...................... 31 How SF_Mirror works ........................................................................ 31 How to run the SF_Mirror proc to make a local copy ........................... 32 Viewing the job history ...................................................................... 33 Mirroring all Salesforce Objects .......................................................... 33 How to run the SF_MirrorAll proc to replicate all objects ...................... 33 Copying only the rows that have changed .......................................... 34 Including Archived and Deleted rows in the local copy......................... 34 How to run the SF_Mirror proc without using xp_cmdshell ................... 34 Best Practices Incorporated into SF_Mirror ......................................... 35 Using the DBAmpTableOptions Table ................................................. 35 Making Local Copies with a Subset of Columns ................................... 37 Making Local Copies as Temporal Tables ............................................ 38 Chapter 4: Bulk Insert, Upsert, Delete and Update into Salesforce using SF_TableLoader............................................................................. 41 Differences between SF_BulkOps and SF_TableLoader ........................ 41 Why SF_TableLoader over SF_BulkOps? ............................................. 41 Checking the Column Names of the Input Table..................................42 Using External Ids as Foreign Keys.....................................................43 Understanding the Error Column ........................................................ 43 Bulk Inserting rows into Salesforce..................................................... 44 Bulk Upserting rows into Salesforce....................................................44 Bulk Updating rows into Salesforce .................................................... 44 Bulk Deleting rows from Salesforce .................................................... 45 Bulk HardDeleting rows from Salesforce ............................................. 45 Bulk UnDeleting rows from Salesforce ................................................ 45 Controlling the batch size with SF_TableLoader...................................45 Understanding a Sort Column when using SF_TableLoader .................. 46 How to run the SF_TableLoader proc ................................................. 46 How to run the SF_TableLoader proc without using xp_cmdshell ......... 47 SF_TableLoader Sample Recipe ......................................................... 49 Understanding SF_TableLoader failures .............................................. 50 Using Optional SOAP Headers ............................................................ 50 Converting Leads with SF_TableLoader .............................................. 51

3

Using IgnoreFailures Option with SF_TableLoader ............................... 53 Using AssignmentRuleId Option with SF_TableLoader ......................... 53 Chapter 5: Using SSIS with DBAmp .............................................. 55 Using the linked server as an SSIS Source .......................................... 55 Pushing Data to using SSIS ........................................ 55 Chapter 6: Uploading files into Content, Documents and Attachments 57 Chapter 7: DBAmp Stored Procedure Reference .......................... 62 SF_BulkOps ...................................................................................... 62 SF_TableLoader ................................................................................ 67 SF_BulkSOQL .................................................................................... 75 SF_BulkSOQL_Refresh ....................................................................... 80 SF_CreateKeys .................................................................................. 82 SF_DownloadBlobs............................................................................ 83 SF_DropKeys .................................................................................... 85 SF_Generate ..................................................................................... 86 SF_Mirror ......................................................................................... 87 SF_MirrorAll ...................................................................................... 92 SF_Refresh ....................................................................................... 94 SF_RefreshIAD ................................................................................. 96 SF_RefreshAll ................................................................................... 98 SF_Replicate ................................................................................... 100 SF_ReplicateAll ............................................................................... 102 SF_ReplicateIAD ............................................................................. 104 SF_MigrateBuilder ........................................................................... 106 SF_MigrateGraphML ........................................................................ 109 Chapter 8: Using the DBAmp Configuration Program................. 111 Options Page of the DBAmp Configuration Program .......................... 111 Registry Settings Page of the DBAmp Configuration Program ............. 113 Chapter 9: Retrieving Salesforce Metadata ................................ 118 How to run the SF_Metadata proc .................................................... 118 Using the LIST and RETRIEVE operations ......................................... 118 Requirements for the input table...................................................... 119 Example: Retrieve Dependent Picklist Information ............................ 121 Example: Retrieve Field Descriptions ................................................ 122 Chapter 10: Using DBAmp Performance Package....................... 124

4

Installing the DBAmp Performance Package...................................... 124 Using the DBAmp_Log Table............................................................ 126 Using the Performance Views........................................................... 127 DBAmp_Replicate_Perf view ............................................................ 127 DBAmp_Refresh_Perf view .............................................................. 128 DBAmp_TableLoader_Perf view ....................................................... 129 Enabling the Performance Trace ...................................................... 130 Chapter 11: MigrateAmp ............................................................. 131 What is MigrateAmp? ...................................................................... 131 Installing MigrateAmp ..................................................................... 131 MigrateAmp Approaches .................................................................. 132 Understanding MigrateAmp Concepts ............................................... 132 MigrateAmp Workflow ..................................................................... 136 MigrateAmp Architecture ................................................................. 137 Chapter 12: Using MigrateAmp ................................................... 140 Using the SF_MigrateBuilder Stored Procedures ................................ 140 Running SF_MigrateBuilder in User Interface .................................... 141 Running SF_MigrateBuilder in SQL Management Studio ..................... 144 Replicating the Source org data ....................................................... 146 Loading the Target org data ............................................................ 146 Resetting the Target org data if needed ........................................... 146 An in-depth look at the SF_MigrateBuilder Parameters ...................... 147 Passing Parameters to _Load Stored Procedure ................................ 150 Migrating Salesforce CRM Content.................................................... 150 Migrating Salesforce Knowledge....................................................... 151 Migrating Single Salesforce Knowledge Article Type .......................... 152 Migrating Multiple Salesforce Knowledge Article Types ...................... 153 Associating Knowledge Articles with Cases........................................ 154 Frequently Asked Questions............................................................. 154 Chapter 13: Viewing a Migration Database Diagram.................. 156 Chapter 14: DBAmp Client .......................................................... 162 Why DBAmp Client? ........................................................................ 162 Installing DBAmp Client ................................................................... 163 Running the DBAmp Client .............................................................. 164 Performing a Mirror Action............................................................... 165

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download