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.
- Connect to your Deltek SQL Server using Windows or SQL credentials with access to EM table
- Choose your Vision database
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
- Review results, and edit script as necessary.
The example script assumes:
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
- 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"
- Replace <USERNAME>, <PASSWORD>, <SQLSERVER> and <DATABASE> with the correct information for your Vision installation.
- If your Argos7Installers folder is somewhere other than c:\, adjust the script with the correct location.
- Create a folder called "AutoScripts"
- Save the file as C:\Argos7Installers\AutoScripts\DeltekUserExport.bat
- Double-click DeltekUserExport.bat and verify that it creates a file called UserList.csv
- 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.
- Go to Control Panel>Administrative Tools>Task Scheduler
- Click "Create Basic Task" from right-hand "Actions" panel
- Enter a name (Deltek User Export) and Description
- Choose "Daily" for schedule
- Enter a time (remember this time for later)
- Choose "Start a program"
- Select your BAT file
- Check "Open the Properties dialog for this task when I click Finish"
- Click Finish
- Identify a user account to run this task
- Set to "Run whether user is logged on or not"
- Click OK
- Run the task to verify it works
Configure Argos Manager to import users automatically
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.
- User Group
- Domain (required in multi-domain environments)
- First Name
- Last Name
- Employee Number (not used)
- Email (required when using Canon OnBoard and scanning)
- 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.