The most common task of any Salesforce implementation is the creation of user accounts. All projects need users! It can be as simple as inputting a few users from the UI or loading one or several CSV files dealing with parent-child relationships. The user object has a particular place in Salesforce between metadata and real data. In this post, I’m going to talk about users import and configuration in an org where you’ve already deployed the security components profiles, permission sets, roles, public groups and queues.
Preparing Your Toolbox
Before starting your journey into users import, you need to make sure you’ve got the right tools and approach to doing the job.
📃 The usual format used during data migrations is CSV. It’s a simple text structure, easy to manipulate but not very robust. My recommendation is to learn a method which (1) works for you and (2) deal with all use cases. This way, you won’t have to reinvent the wheel project after project.
In particular, I suggest to always stick to UTF-8 for character encoding. Even if you’re not working on an international double-bytes characters project you may get a couple of foreign last names lost in the bulk of your data that caries accented characters from Western or Eastern Europe.
✏️ When editing CSV files I use Easy CSV Editor on my Mac and Ron’s Editor on my PC. Sometimes a text editor, like VS Code, for instance, can be handy to access raw data, validate delimiters and the general shape of a file. If you’re not sure, you can use the online service csvlint. Ban the use of traditional spreadsheets (“Excel” or “Numbers”) as it’s difficult to control how the data is actually saved to disk. Instead, prefer database apps like “Access” or my Salesforce Users Import Template.
🤖 There are two APIs available to load data in Salesforce (SOAP and Bulk). I normally use the Bulk API. It might be a bit touchy at times, especially when dealing with files, but there are some settings to tune it.
🛠️ At some point in the process, you’ll have to identify fields by their API names. I recommend using the Chrome Extension Salesforce Inspector for this purpose. And for when you need to look around in the database, nothing beats the Salesforce professional’s Swiss army knife: The Workbench.
🚛 To load your data in a Salesforce server, you need a client app. Here is a short list of the free options on the table. I’m not evaluating the paid options as they’re not a fit for consultants. Expect the below results to change drastically when paying for the service.
This is the reference to which all tools are measured. It’s provided and supported by Salesforce which is a key advantage. It’s got a comprehensive list of tuning options and is very robust. When an insert is timing-out with another tool, I always end up trying with the Salesforce Dataloader. The main issue I have with it, is that it’s not convenient to use when running many transactions, redoing stuff, changing org… You’re always starting from scratch.
|Ease of use||✔️|
Dataloader.io is well known for dealing with parent/child lookups from within the field mapping section. Which means that there’s no need to retrieve Salesforce IDs before loading records containing parent lookups! It’s also very easy to use, thanks to its slick UI. And that’s also its default, there’s not much to configure should you want to tune a data load. Also, there’s no support which can become a blocker when you need it.
|Ease of use||✔️|
Jitterbit feels like a data loader created by people who are used to load data! With one login to your Jitterbit account to get access to all the salesforce orgs, you’re working on. You can create a library of queries, run multiple queries in parallel, run the same query on multiple orgs and do some clever transformations. The support is done via a community forum where questions usually get a quick answer. The setup process can be tricky though and the last update was released in Decembre 2016.
|Ease of use||✔️|
So, in the end, Jitterbit is my weapon of choice and I keep Salesforce Dataloader as a backup tool.
From time to time, I log on Dataloader.io and check if it’s any better. It has the potential to become the best option but it’s not there yet and I hope that Salesforce’s acquisition of Mulesoft will help the product in this respect.
There’s a logical order to go through the migration.
- Deploy the metadata to the target org (including security components).
- Retrieve the security components IDs.
- Import the users using the “Profiles” and “Roles” IDs.
- Retrieve the user IDs from the success log.
- Load the assignment junction objects.
At this point, you’re ready to complete the data migration using the users and queues IDs to set records ownership.
Retrieving The Security Components IDs
Although profiles, permission sets, roles, public groups and queues can be migrated as data, you’re better-off deploying them as metadata. So, assuming they’ve already been deployed when the users’ migration starts, the first thing to do is to retrieve their IDs. The following SOQL queries will do just that.
SELECT Name, Id FROM Profile
SELECT Name, Id FROM UserRole
SELECT Name, Id FROM PermissionSet WHERE IsOwnedByProfile = FALSE
SELECT Name, Id FROM Group WHERE Type = ‘Regular’
SELECT Name, Id FROM Group WHERE Type = ‘Queue’
Store the results in the tabs starting with IDs of my Salesforce Users Import Template.
Importing The Users
There is quite a lot of fields to gather in this part of the process:
- The users’ data that the business owner wants to import.
- The users’ data that Salesforce considers mandatory.
- The many-to-many relationship between users and their permission sets, public groups and queues membership.
😱 This may look like a simple task but it tends to be a pain. It usually requires multiple back and forth between the business owner and yourself, juggling with several versions of an Excel file.
As I needed a smoother workflow, I came up with the process I’m sharing in this post which is based on a Google Sheet. In a nutshell, both tabs and columns headers follow the same colour code:
- Blue: for the business owner and yourself if you can help.
- Yellow: comments that are not imported.
- Red: for yourself only.
- Grey: Not supposed to be edited.
The tab “Values” contains the sandbox prefix in cell “A2”. It should be emptied when dealing with a production environment.
The fields listed below are required and identified, in the “Users” tab, by a dark blue column header.
👍 If you add new columns to the template use the field API Name as a column header so that you will be able to do an “auto-map” when configuring the data loader insert job.
Tab “Input – Users”
To create the CSV file for users records, select the “Input – Users” tab, then menu “File”, “Download as” and “Comma-separated values (.csv, current sheet)”. Then load the data in the User object.
Retrieving The User IDs
To retrieve the user IDs, simply copy the ID column from the “Users” insert success file into column “E” of the “Users” tab. The rows are kept in the same order to allow a simple “Copy & Paste”.
Loading The Assignments
Assignments (permission sets) and memberships (public groups and queues) are junction objects supporting no extra attribute. So, a pair of IDs is always the only thing to load.
Tab “Input – PS”
To create the CSV file for permission sets assignments, select the “Input – PS” tab, then menu “File”, “Download as” and “Comma-separated values (.csv, current sheet)”. Then load the data in the PermissionSetAssignment object.
Tab “Input – Groups”
To create the CSV file for “Public Groups” memberships, select the “Input – Groups” tab, then menu “File”, “Download as” and “Comma-separated values (.csv, current sheet)”. Then load the data into the GroupMember object.
Tab “Input – Queues”
To create the CSV file for “Queues” memberships, select the “Input – Queues” tab, then menu “File”, “Download as” and “Comma-separated values (.csv, current sheet)”. Then load the data in the GroupMember object.
This is it! At this stage, you should have a security model fully replicated in your target org with the users correctly mapped into it and ready to be used. Hooray!!! 🎉
Related Posts & Resources
- Salesforce post (2013): Fifteen Things to Consider Before Your Next Data Migration
- Trailhead project: Import and Export with Data Management Tools
- SOAP API reference: User Object
- Salesforce data model: User, Sharing and Permissions