Using DBBulkImport API with current JobServer user.

Hi hleigh,

the following should do the trick:

Using BI AS DbBulkImport(table, _

                         VID_GetValueOfDialogdatabases("ConnectionString"), _

                         Nothing, _

                         VID_BuildAuthString())

Please note that ConnectionString needs to be decrypted first if your database is encrypted.

please also note that this class was removed from the API in version 7 and that the BulkImportAsync method needs to be used instead.

 

HTH,

 Oliver

Thanks for the reply Oliver - more useful than the one referring me to Powershell REST Doco I got from Tech Support !

Anyway - tried using vid_BuildAuthString .. .

1 : Using SystemDebugger to run script - ended up with "null pointer" exception in VID_BuildAuthString - seemed to fail on final iteration when building string when I added breakpoints . . .

2 : Created a process to call script on Job Server - not much luck here - getting an "Authentication String is not valid" error . . . added some debug code to extract the result of the call to VID_BuildauthString() :

"Authstring = Identifier=sa;Name=sa;EnvironmentName=sa;Display=Service Account;UID="

Missing UID value on the end . . .

According to SDK - the UID value is that of a linked Person - not present for sa System User

So . . . What next ?? Any other suggestions ?

Hi hleigh,

you're right. VID_BuildAuthString is not able to build an AuthString in case of system user authentication. Mostly because Connection.User.Uid is Nothing in this case, but also because the string would require a Password.

If you really have to use system user authentication, you probably need to build your own AuthString using the following syntax:

Module=DialogUser;User=<user>;Password=<password>

I guess the password is required because DbBulkImport's initialiser does an extra Connection.Authenticate() call to re-authenticate the connection.

Hth,

 Oliver

May I ask, why you want to use the DBBulkImport API?

The BulkImport API works as you would import the data using plain SQL. No template execution, no event generation, no customizer checks.

Is this really what you want to achive?

In version 7, the object layer will be using bulk mode automatically for insert and update operations as long as no queries for the same table are blocking this. An example would be some auto-increment logic for the personal number that obviously tests against the person table while your are import data into the same table.

Hi Markus,

not having templates executed, event generation and customizer logic might sometimes be exactly what is required. Customers of mine process millions of data records every day. Using DbBulkImport helped to significantly reduce processing time and workload in their 6.1 systems. Even with version 7 I suspect BulkImportAsync to be a bit faster than the normal object layer methods, but that might just be a wrong impression which is not backed by qualified tests yet.

Yes, you're right. BulkImport can never be a replacement for the standard object layer methods. But there are use-cases where it fits perfectly. At least in pre-7 installations.

Just my $0.02.

cu,
Oliver

But you can use pure SQL for that having the same effect.

I just want clarify that this is not using the object layer at all. You may be aware of it, not everybody is. Especially because the original question was asked around using the object layer.

Markus

The use-case scenario I'm looking at is the import of 10 x thousands of objects into 1IM.

When testing standard Object Layer - create object - save - templates/event/customizer firing - model - the performance was rather slow. It appeared to be mainly due to the template/event/customizer firing. I'm looking at improving the performance of the import script by using DBBulkLoader to write the objects - then generating a job to execute templates - and any other event/customizer.

This model permits the fan-out of the template/event/customiser calculation - providing scalebility- and a major performance benefit. In addition it separates each aspect of the process - permitting failure of one step in the process - without effecting the others.

I'm aware that the same thing could be accomplished via direct SQL - but thought by using a built in API - I would be able to leverage the existing Job Server connection information. I suppose the benefit of direct SQL is that an authstring will not be required - as the Connection String will provide the only authentication required.

I was hoping to avoid having a config param stored with the user authstring - and leverage the existing sa connection when using the DBBulkImport - if this is not possible - I guess I'll just have to revert to using the stored authstring.

Thanks for your input.

I see that you thought about the pro and cons and therefore I am relived. That was the only point I wanted to make.

I just have one further suggestion. I am assuming that your are running on a version 6.x so, what you could do for the authentication string.

If you want to avoid the use of a password in the authentication string, why not use one of the AD based authenticators. Drawback is, that the account your are testing this and the account the job service is running with, have to be present in the One Identity Manager database and have to be linked to a person.

For example if you use the authenticator "Active Directory User account" your Authentication String would look like the following:

Module=ADSAccount

The permissions will be determined by the DialogUser set at the person.

HtH