Exporting users from Deltek Vision

The following document explains how to export a list of users from a Deltek Vision database and import them into Argos on a set schedule.

The following assumptions are made:

• Basic familiarity with SQL Server
• Basic familiarity with Windows BAT files
• Basic familiarity with Deltek Vision

Deltek Vision is a popular accounting package. In addition to storing project numbers, Vision typically stores information about all users in the company. For this reason, it is an excellent place to retrieve user information.

Create a SQL query with user information

The most important, and first step, is to get a list of users. Deltek Vision stores user information in a table called EM. Additional user information may be stored in a table called EmployeeCustomTabFields.

The basic set of information that is required for Argos is PIN code and Username.

1. Connect to your Deltek SQL Server using Windows or SQL credentials with access to EM table
2. Choose your Vision database

3. Enter this script and hit execute:

   SELECT Employee AS PIN, LastName, FirstName, Email,
   LEFT(Email, CHARINDEX('@',email)-1) AS Username
   FROM em
   WHERE Status = 'A'
   AND Email IS NOT NULL
   

4. Review results, and edit script as necessary.

The example script assumes: Employee ID will be used as PIN code Windows username = first part of email address The Sepialine Support contract does not cover custom scripting, but consulting is available. Contact us for more information.

We recommend creating a view in your Vision database to store the user list. This makes editing the script in the future easier.

CREATE VIEW ArgosUsers AS
SELECT Employee AS PIN, LastName, FirstName, Email,
LEFT(Email, CHARINDEX('@',email)-1) AS Username
FROM em
WHERE Status = 'A'
AND Email IS NOT NULL

Create a BAT script to export data automatically from Vision

This next step can be accomplished in a variety of ways (SQL Integration service, DTS package, etc) but a Windows BAT file is the simplest method.

You will need:

• Deltek SQL Server Name
• SQL username and password with access to ArgosUsers view
• Location of Argos7Installers folder

1. Open Notepad and Enter the following script:

SQLCMD -U <USERNAME> -P <PASSWORD> -S <SQLSERVER> -d <DATABASE> -h-1 -s "," -W -o "c:\Argos7Installers\AutoScripts\UserList.csv" -Q "SET NOCOUNT ON SELECT * FROM ArgosUsers"

1. Replace <USERNAME>, <PASSWORD>, <SQLSERVER> and <DATABASE> with the correct information for your Vision installation.
2. If your Argos7Installers folder is somewhere other than c:\, adjust the script with the correct location.
3. Create a folder called "AutoScripts"
4. Save the file as C:\Argos7Installers\AutoScripts\DeltekUserExport.bat
5. Double-click DeltekUserExport.bat and verify that it creates a file called UserList.csv
6. Review the CSV to verify it has the correct data

Set up a Windows Scheduled Task to run the BAT script

These instructions assume Windows 7 or Server 2008.

1. Go to Control Panel>Administrative Tools>Task Scheduler
2. Click "Create Basic Task" from right-hand "Actions" panel
3. Enter a name (Deltek User Export) and Description
4. Choose "Daily" for schedule
5. Enter a time (remember this time for later)
6. Choose "Start a program"
7. Select your BAT file
8. Check "Open the Properties dialog for this task when I click Finish"
9. Click Finish
10. Identify a user account to run this task
11. Set to "Run whether user is logged on or not"
12. Click OK
13. Run the task to verify it works

Configure Argos Manager to import users automatically

Follow the steps outlined in our Importing Billing Codes from a CSV file using a System DSN instructions to set up a task to automatically import users from a file. Set the scheduled time to import after the Windows task runs to export users from Vision. For example, If the Scheduled Task runs at 12:15am, run the Argos import at 12:20am.

Additional field configuration options

Argos allows you to import any of the following fields. Only Username is required. Depending on your Vision setup, some or all of this information may be available in your Vision database.

• Username
• User Group
• Domain (required in multi-domain environments)
• First Name
• Last Name
• Employee Number (not used)
• Email (required when using Canon OnBoard and scanning)
• Location
• Phone Extension (only used in phone tracking installations)
• PIN (used for accessing walkup copy/scan devices, such as MFPs and wide format plotters)

Contact Sepialine Support if you need assistance importing additional fields from Deltek Vision.