Question

Importing Organization Roles: SSP SysAdminUnit

I am attempting to import data into the SSP SysAdminUnit (dbo.SysAdminUnit) object to populate Organization Roles, however, the process runs into the same invalid column issue no matter how I tweak the data file.

Import Info

  • Using the template downloaded from the Advanced Settings for the object:  SSP SysAdminUnit.  Template is bpm’online named as SSP SysAdminUnit.xlsx.
  • Populated one record with data in Name and Culture columns only as they are required (identified by orange font color).
  • Attempted imported via Data Import under System Designer (the “wizard”) targeting SSP SysAdminUnit.
  • All column to field mapping occurred automatically successfully.
  • Error returned from import attempt:  “Invalid column name 'LDAPElementId'. Invalid column name 'LDAPElementId'.”
  • The file’s column “LDAP element Id” is empty.
  • Tried removing the column and then all columns except Name and Culture with no success each time.

Questions

  • Error appears to be server side as the target not exposing the field to the import process even though mapped?
  • Am I missing additional requirements to do this?
  • Can I use the “wizard” to import data into this object/table?
File attachments

Like

8 comments

Dear Stephen,

File import can be used to create the system users indeed. Make sure you import the file into System Administration Object:

In case the issue persists, please provide the example of the record you are trying to import and we'll check what could be the reason for the issue.

Lisa

I was able to successfully import both organization roles (and divisions and users) using this object target.  Just a note to the community, besides Name and Culture, there are other required/needed/suggested fields:  Inherited from, Type, Contact, Password, and Active.  Inherited From is required else it will error out as a parent association is needed.  The Type field is important as it drives the logic of say an organization vs user etc...These Type values are found in the table dbo.SysAdminUnitType (see below). You cannot use the description of the Type, just the value otherwise the Type will default to 0 or Organization on record creation.  When importing a User, setting the Contact values is required* and will either match to an existing contact or create a new contact record.  Also, Password is required* and the import process will encrypt the value for you.  Setting Active to True is recommended all records.

*Note:  Omitting these values will not break the import but will create a poor data state for the record.

Questions

  1. I have not been able to load hierarchical data from a data file at one time, even when sequencing the data in the file.  It appears process is not able to create parent child relationships unless the parent already exist in the system before the import.  Is this correct?  If so, then the scope of the import is limited to one hierarchical level at a time.  For example, importing four levels of an organizational chart will require four separate imports.
  2. Are there any realistic limits to importing data into this table?  e.g. things that only the UI can do and not via this data load.

SysAdminUnitTypes (aka Type)

0    Organization

1    Division

2    Manager

3    Team

4    User

5    Self-service portal user

6    Functional role

 

 

Dear Stephen,

1. Yes,the import process is not able to create parent child relationships unless the parent already exist in the system before the import. You have to perform import in 2 steps: 

a) Import all roles without parent roles.

b) Import them again specifyng parent roles which already exist in the database (records will be updated).

2. You will also have to perform import  in ‘User in role’ object.

Please map ‘User Name’ as ‘User’ and ‘Role’ as ‘Role’.

After that, you will have to either 'Update roles' in the UI or run [tsp_ActualizeAdminUnitInRole] stored procedure in the database. 

 

Please also note that unfortunately, there is no possibility to grant licenses within import for now.

We have already this problem registered for our R&D team to consider simplifying this process in the further releases.

Best regards,

Diana

Diana,

Good info and nice reminder about the liscenses.  Too bad the User Roles are not populated automatically when the import occurs.  Kind of tough without some default role preset, though "All Employees" would be good target.  I am playing with this mainly to handle large setups e.g. 100 to 200 users.

Is there any documentation on what triggers automatically and not for certain imports like Account, Contacts and Leads, and more on System Administratino Object?  Your example of no User Roles is good information, which is helpful to setup an import strategy for large data sets.

Follow-up on the license issue to confirm the issue.  So if need to setup 100 users, I will have to manually grant them each a license via the UI?  There is no other method to do this for batch setups?

Dear Stephen,

 

You can use the SQL script to grant the licenses:

insert into 
SysLicUser (SysUserId, SysLicPackageId, Active)
       select Id,  
          (select Id from SysLicPackage
       where Name = 'Name of the license’) PackageId,
          1
          from SysAdminUnit
       where SysAdminUnit.Active = 1
  and SysAdminUnit.SysAdminUnitTypeValue = 4
       and not exists (
       select * from SysLicUser slu  
       where slu.SysLicPackageId = (select Id from SysLicPackage  where Name = 'Name of the license’) 
       and  slu.SysUserId = SysAdminUnit.Id)

Lisa

 

Thanks Lisa for the License script.  Very useful.

Anyone have information regarding my other post about documentation:  "Is there any documentation on what triggers automatically and not for certain imports...."?

Dear Stephen,

Unfortunately, there is no documentation like that in our Academy but I have passed the suggestion to add one to our Academy team. 

Lisa

Show all comments