Installation Progress

1. Create the source connections
2. Create the destination connection
3. Import the extractions
4. Schedule the extractions
5. Set up the data sources in SEI
6. Import the template
7. Build and load the OLAP cubes

Source Connection

A source connection in DataSync defines how the system connects to your Sage 100 NA database to extract and refresh data. Creating source connections is required before you can load or synchronize tables in your data warehouse.

Create two types of source connections:

Source Connection	Description
Refresh	Use for tables that can be loaded incrementally and kept up-to-date with regular refreshes.
No Refresh	Use for tables that cannot be loaded incrementally and are not refreshed automatically.

important

Sage 100 NA Standard uses the ODBC ProvideX driver, which requires additional configuration for DataSync to function properly. Complete the steps below before creating your source connection.

Configure ODBC ProvideX

Limit parallel jobs in DataSync

By default, DataSync allows up to 400 parallel jobs. For ODBC ProvideX, you must reduce this to improve reliability:

1. Go to C:\inetpub\wwwroot\NectariDataSync\SyncAPI and open the appsettings.json file.
2. Find the following line and change the value to 1:

   "MaxNumberOfJobsRunningInParallel": "400"

3. Save the file.
4. Restart the DataSyncAPI application pool in IIS for changes to take effect.

Update DataSync application pool permissions

By default, the DataSync application pools run as NetworkService, which usually does not have permission for these shares. You must update the Identity for each DataSync application pool to a user account with folder access.

1. Press Windows key + R to open the Run dialog.
2. Type inetmgr and press Enter to open IIS Manager.
3. In the left sidebar, expand your server to view Application Pools.
4. For the DataSyncAPI pool, right-click the pool and select Advanced Settings.
5. In the Process Model section, click the Identity field, then click the ellipsis (...).
6. In Application Pool Identity, choose Custom account and click Set.
7. Enter the username and password of an account with access to the Sage 100 NA shared folders.
8. Click OK to save.
9. Repeat thiss for DataSyncIdentity, DataSyncJob, and DataSyncSync pools.
10. Restart each pool for changes to apply.

Locate required ODBC connection paths

To configure your DataSync ODBC source connection, you must supply the Directory, Prefix, and ViewDLL paths. These are the network locations where your Sage 100 program and data files reside.

1. Locate your Sage 100 installation directory.
2. Identify the required paths for your configuration:
   • Directory: The main installation path for Sage MAS90 (e.g., \\YourServer\Sage\Sage 100 Advanced\MAS90).
   • Prefix: The SY subfolder within the directory (e.g., \\YourServer\Sage\Sage 100 Advanced\MAS90\SY).
   • ViewDLL: The Home subfolder within the directory (e.g., \\YourServer\Sage\Sage 100 Advanced\MAS90\Home).
3. Ensure that the user account set for the DataSync application pool has read access to these network locations.

Create a refresh source connection in DataSync

1. Log in to DataSync.
2. From the welcome screen, select Connections.
3. Next to Source Connections, click New.
4. Select ODBC.
5. In the Connection Properties panel, enter Refresh as the description.
   • For scenario 2 – cloud, use a description such as Refresh – [Company Name].
6. Leave Null Expression and Not Null Expression with their default values.
7. In the String Constant Left Delimiter and String Constant Right Delimiter fields, enter a single quote (').
8. Select the following options: Supports Joins, Supports Group By, Supports Parameters, Supports Order By, Supports Quotes, Alias, and Columns.
9. In the Left Delimiter and Right Delimiter fields, enter a single quote (').
10. Select the following options: Supports Limits, Supports Table Alias, and Supports Column Alias.
11. In the Metadata Loading dropdown list, select From SELECT Statement.
12. In the Additional Connection Properties panel, click Add property and enter the properties listed below.
13. In the Advanced Settings panel, set Tracking Type to Date.
    The time zone must match the Sage 100 NA application server.
14. Click Save.
15. For scenario 2 – cloud, repeat steps 2–9 for each company.
    Use Duplicate Connection (upper right corner) to speed up the process and adjust only the company-specific details.

Create a no refresh source connection in DataSync

1. Log in to DataSync.
2. From the welcome screen, select Connections.
3. Next to Source Connections, click New.
4. Select ODBC.
5. In the Connection Properties panel, enter No Refresh as the description.
   • For scenario 2 – cloud, use a description such as No Refresh – [Company Name].
6. Leave Null Expression and Not Null Expression with their default values.
7. In the String Constant Left Delimiter and String Constant Right Delimiter fields, enter a single quote (').
8. Select the following options: Supports Joins, Supports Group By, Supports Parameters, Supports Order By, Supports Quotes, Alias, and Columns.
9. In the Left Delimiter and Right Delimiter fields, enter a single quote (').
10. Select the following options: Supports Limits, Supports Table Alias, and Supports Column Alias.
11. In the Metadata Loading dropdown list, select From SELECT Statement.
12. In the Additional Connection Properties panel, click Add property and enter the properties listed below.
13. In the Advanced Settings panel, set Tracking Type to None.
14. Click Save.
15. For scenario 2 – cloud, repeat steps 2–9 for each company.
    Use Duplicate Connection (upper right corner) to speed up the process and adjust only the company-specific details.

Property settings

Property	Value
Driver	{MAS 90 4.0 ODBC Driver}
UID	[Sage 100 NA Username]
PWD	[Sage 100 NA Password]
Directory	[Sage MAS90 path] (e.g. \\[Server]\Sage\Sage 100 Advanced\MAS90)
Prefix	[Sage MAS90 path]\SY (e.g. \\[Server]\Sage\Sage 100 Advanced\MAS90\SY)
ViewDLL	[Sage MAS90 path]\Home (e.g. \\[Server]\Sage\Sage 100 Advanced\MAS90\Home)
Company	[Company Code]
LogFile	\PVXODBC.LOG
CacheSize	4
DirtyReads	1
BurstMode	1
StripTrailingSpaces	1
SERVER	NotTheServer
EnforceNullDate	1

note

For more information about these settings, see ODBC Driver Configuration (Windows)

Next: Create the Destination Connection →