For more information on SQL Data Types please see Microsoft Developer Network Transact-SQL Data Types https://msdn.microsoft.com/en-us/library/ms187752.aspx.
Create a delimited text file containing the fields in the exact order displayed in the table below with nulls only in fields allowing nulls and following all other rules for ILLiad field contents such as specific values allowed and password requirements as noted:
Once you've created the text file, upload the document to your ILLiad SQL Server for importing. If your server is hosted, contact your hosted server provider (OCLC or Atlas) regarding sending the file for import. The rest of these instructions pertain to only self-hosted servers with access to their own SQL Server.
Once the file is in place, you would then browse the SQL Server Management Studio under Databases | ILLData and right click on the database to choose Tasks .. Import Data.
Then, follow these instructions:
- A Data Transformation Wizard will open.
- Choose your Flat File Source as the source. Make sure that the file is delimited (separated by a character, usually a comma).
- Verify the file format and column delimiters. There should be a preview pane below.
- Set your source to your local SQL Server by filling out the Server name, authentication, and database.
- The next screen should show your source as the text file and the destination. You will need to change the Destination from the default table name to UserValidation by clicking in that field and choosing it from the drop-down.
- Click Next. You will want to run this immediately, but will also want to check the box to Save an SSIS package for later execution. It defaults to saving the package to the SQL Server. The schedule should be changed, or else it will run every day at midnight. It assumes that the text file will be in that same location each time, so be sure that you put the text file in a location where you'll be able to FTP it in the future.
- Click Next. Give the package a name if you chose to create one (i.e. UserValidation import for ILLiad).
- Click Next. Review the import in the final form. It should be going from a flat file to a SQL Server table. Click Finish to import the file and schedule the job.
For example, if you wish to limit access to ILLiad to only those users who have a library card, you can import a list of those library cards into the UserValidation table where the library card is the username. If you would like to import other information about those users (name, address, contact information, etc.) you can do that as well.
| Field | DataType | Nulls Allowed? | Note/Special Instructions |
|---|---|---|---|
| UserName | nvarchar(100) | No Nulls Allowed |
REQUIRED IN THIS FILE - Primary Key–This field ** must be unique ** across the entire database. If your list of users to import as duplicate entries, you will get errors trying to import those users into the UserValidation table. |
| UserValidationType | nvarchar(4) | No Nulls Allowed |
REQUIRED IN THIS FILE - The UserValidationType column has been added to the UserValidation table. Two values can be used in UserValidationType: Auth (default) or Load. Auth indicate that the User record will be used to authenticate a user. If a user successfully authenticates and their User record does not already exist, a User record will be created with information from the UserValidation data. The UserValidationType of "Load" is applicable when using a WebAuthType of LDAP or RemoteAuth is enabled. If so, the default WebAuthType will be used and the UserValidation data is only used to populate a newly created User record after a user successfully authenticates. |
| LastName | nvarchar(40) | No Nulls Allowed | REQUIRED IN THIS FILE |
| FirstName | nvarchar(40) | No Nulls Allowed | REQUIRED IN THIS FILE |
| SSN | nvarchar(20) | Yes Nulls Allowed | |
| Status | nvarchar(15) | Yes Nulls Allowed |
If you plan to use either the ILLiad web reports based on status or use status specific web pages, you will need to populate this field either in user validation or on NewAuthRegistration by hidden value or user input. |
| EMailAddress | nvarchar(100) | Yes Nulls Allowed | |
| Phone | nvarchar(15) | Yes Nulls Allowed | |
| MobilePhone | nvarchar(15) | Yes Nulls Allowed | |
| Department | nvarchar(255) | Yes Nulls Allowed |
If you plan to use the ILLiad web reports based on department, you will need to populate this field either in user validation or on NewAuthRegistration by hidden value or user input. |
| NVTGC | nvarchar(20) | Yes Nulls Allowed |
*REQUIRED in User record for ILLiad to function correctly. Must be populated either by UserValidation or by NewAuthRegistration hidden field or user input. |
| Password | nvarchar(64) | Yes Nulls Allowed |
Please see Usernames and Passwords section for further information here.
|
| NotificationMethod | nvarchar(10) | Yes Nulls Allowed |
* REQUIRED in User record for ILLiad to function correctly. Must be populated either by UserValidation or by NewAuthRegistration hidden field or user input. Valid values are Electronic, Phone, or Mail. |
| DeliveryMethod | nvarchar(25) | Yes Nulls Allowed |
* REQUIRED in User record for ILLiad to function correctly. Must be populated either by UserValidation or by NewAuthRegistration hidden field or user input. Valid Values are Hold for Pickup, Mail to Address. |
| LoanDeliveryMethod | nvarchar(25) | Yes Nulls Allowed |
* REQUIRED in User record for ILLiad to function correctly. Must be populated either by UserValidation or by NewAuthRegistration hidden field or user input. Valid Values are Hold for Pickup, Mail to Address. |
| AuthorizedUsers | nvarchar(255) | Yes Nulls Allowed | |
| Web | nvarchar(3) | Yes Nulls Allowed |
* REQUIRED in User record for ILLiad to function correctly. Must be populated either by UserValidation or by NewAuthRegistration hidden field or user input. Valid Values are Yes, No. |
| Address | nvarchar(40) | Yes Nulls Allowed | |
| Address2 | nvarchar(40) | Yes Nulls Allowed | |
| City | nvarchar(30) | Yes Nulls Allowed | |
| State | nvarchar(2) | Yes Nulls Allowed | |
| Zip | nvarchar(10) | Yes Nulls Allowed | |
| Site | nvarchar(40) | Yes Nulls Allowed | |
| Number | nvarchar(20) | Yes Nulls Allowed | |
| Organization | nvarchar(50) | Yes Nulls Allowed | |
| Fax | nvarchar(15) | Yes Nulls Allowed | |
| ArticleBillingCategory | nvarchar(50) | Yes Nulls Allowed | |
| LoanBillingCategory | nvarchar(50) | Yes Nulls Allowed | |
| Country | nvarchar(50) | Yes Nulls Allowed | |
| SAddress | nvarchar(40) | Yes Nulls Allowed | |
| SAddress2 | nvarchar(40) | Yes Nulls Allowed | |
| SCity | nvarchar(30) | Yes Nulls Allowed | |
| SState | nvarchar(2) | Yes Nulls Allowed | |
| SZip | nvarchar(10) | Yes Nulls Allowed | |
| PasswordHint | nvarchar(50) | Yes Nulls Allowed | |
| SCountry | nvarchar(50) | Yes Nulls Allowed | |
| Blocked | nvarchar(3) | Yes Nulls Allowed | |
| PlainTextPassword | nvarchar(250) | Yes Nulls Allowed |
Please see Usernames and Passwords section for further information here.
|
| UserRequestLimit | nvarchar(5) | Yes Nulls Allowed | |
| UserInfo1 | nvarchar(255) | Yes Nulls Allowed | |
| UserInfo2 | nvarchar(255) | Yes Nulls Allowed | |
| UserInfo3 | nvarchar(255) | Yes Nulls Allowed | |
| UserInfo4 | nvarchar(255) | Yes Nulls Allowed | |
| UserInfo5 | nvarchar(255) | Yes Nulls Allowed |
Tips & Best Practices:
- Verify that your WebValidation table supports the fields and values that you are specifying in UserValidation and NewAuthRegistration/ChangeUserInformation web pages. Learn more about WebValidation at https://support.atlas-sys.com/hc/en-us/articles/360011907573
- Any fields that you want to be populated with defaults without user intervention that you do not supply via UserValidation you must specify as hidden fields on the NewAuthRegistration web page. You will also need to comment out the matching non-hidden fields in the web page to refer to hidden values specified and prevent registrants from selecting values that overwrite your uservalidation or hidden entries. Learn more about hidden fields at https://support.atlas-sys.com/hc/en-us/articles/360011807614
-
If you would like to display to the registrant the value supplied by UserValidation for registration but not allow them to modify it, you will need to make it read-only in the web page display. Ex.
<label for="EMailAddress">
<span class="field">
<span class="req">*</span>
<span class="<#ERROR name="ERROREMailAddress">"><strong>Email Address </strong></span>
</span>
<input readonly id="EMailAddress" name="EMailAddress" type="text" size="40" class="f-name" value="<#PARAM name="EMailAddress">"><br>
</label> - If you are an EZProxy user supplying ALL values that you want to collect in the user record with UserValidation entries and hidden fields, you will want to remove all webvalidation entries except for FirstName validated with .* and add error message of “Issue with User Record. Please contact ILL Staff at 555-555-5555.” This prevents EZProxy users who are authenticated but not in UserValidation table from registering without contacting ILL staff to inform of missing record.
Usernames and Passwords for UserValidation
If your patrons are going to authenticate through the use of the UserValidation table, you will need to configure the username requirements and temporary initial password for your patrons. It is technically possible to not use the password field and leave that blank but is not recommended for security and privacy issues.
Usernames- The username patrons would enter when logging into the patron web pages would go in the Username field of the UserValidation table. This field must be unique across the entire database. If your list of users to import has duplicate entries, you will get errors trying to import those users into the UserValidation table.
Passwords- The password value can be implemented by entering the password for all users into the PlainTextPassword field of the UserValidation table. This field is used only for the initial registration of new users to compare to the value the new customer types in and is no longer used for future logins.
Example
For example, if you would like all ILLiad customers to use their library card as their username and their last name in all lowercase as their password, you can enter those values into the UserValidation table. The Username field would be 123456789 if that is the customer's library card number and the PlainTextPassword field would be "smith". Note that while you are using the customers' last name as his password, you would want to enter that value into the PlainTextPassword field and not the LastName field. Any other information you wish to pre-populate can be entered in UserValidation as well (FirstName, LastName, Phone, EMailAddress). Because you are using the PlainTextPassword field, the Password field in UserValidation would be blank.