DB Connector - SQL Server

[Community_User]

Originally posted on March 9 2020 at 23:35 UTC

Hello, we are working on an account import from a application database. The database server is SQL Server. We have designated the following settings:

URL: jdbc:sqlserver://<server IP>

UserName: Local SQL account

Password:

Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

AccountsImport:

<dataMapping>

<before-import>

</before-import>

<sql-query description="This is the Source DB Query" uniquecolumnsascommaseparated="name" >

<![CDATA[

select UserId, Email, BranchCode from UserTable where BranchCode='567' OR BranchCode="890";

]]>

</sql-query>

<mapper description="This is the mapping field for Saviynt Field name" ifusernotexists="noaction">

<mapfield saviyntproperty="accounts.name" sourceproperty="UserId" type="character"></mapfield>

<mapfield saviyntproperty="accounts.CustomProperty1" sourceproperty="Email" type="character"></mapfield>

<mapfield saviyntproperty="accounts.CustomProperty2" sourceproperty="BranchCode" type="character"></mapfield>

</mapper>

<after-import description="EMAIL,BATCH,SQL">

</after-import>

</dataMapping>

We have two questions related to this:

When running the AccountImportFullJob, we are required to select a File Name. our options are datafiles, fail, process, sav, success,xmlschemaforexternaldbimport. The DB Connect documentation states that if we populate the AccountsIMport section, we are not required to upload the schema. I can't find any documentation tied to the FIle Name items listed as options.

When running the AccountImportFullJob, with any File Name option, we get the following error. it is referring to MySQL, but our connection is configured for SQL Server. The query runs just fine outside of Saviynt.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

[Community_User · Accepted solution]

Originally posted on March 12 2020 at 11:57 UTC

Hi,

As per the connector functionality, if you are providing the xml in ACCOUNTSIMPORT section on DB connector, then you need not to select any xml file while running the Accounts Import Job. In this case as you have already provided xml content in ACCOUNTSIMPORT at connector, even though you select any file while running the job, the selected file will be ignored and job will pick up the content in ACCOUNTSIMPORT and perform the import.

Also, if you are running the job using the file method, then you should keep the ACCOUNTSIMPORT blank at connector. The file should have the xml content and file extension should be .xml . File should be placed at the location as configured in your environment, then only the file will be listed in the dropdown while running the Accounts Import Job.

Thanks,

Ranjan

[Community_User]

Originally posted on March 13 2020 at 00:00 UTC

Thanks, Ranjan, this did clarify things for me.

[Community_User]

Originally posted on September 23 2020 at 22:37 UTC

Hi,

I am looking for information on the schema for the data mapper XML file. For example all the samples I have seen do not have any text in the before-import and after-import tags. Per the tags name it suggests if I want to perform some initialization before beginning the import and any clean up after performing the import I should use these tags. However what all things I can do is not clear as there is no schema or documentation which points to the different tags which can be used.

Also in case I want to perform validation at specific attribute level, how can I have the validation performed. Same is true for the transformation for example I get the value as 'HIRED' from the source and I want to change it to 'Onboarded' in SSM how can I do these conversions