Learn more
 

Ready to get started?

Learn more or sign up for a free trial:

CData Sync

In this article

Related articles

Replicate Multiple SAS Data Sets Accounts


Replicate multiple SAS Data Sets accounts to one or many databases.

CData Sync for SAS Data Sets is a stand-alone application that provides solutions for a variety of replication scenarios such as replicating sandbox and production instances into your database. Both Sync for Windows and Sync for Java include a command-line interface (CLI) that makes it easy to manage multiple SAS Data Sets connections. In this article we show how to use the CLI to replicate multiple SAS Data Sets accounts.

Configure SAS Data Sets Connections

You can save connection and email notification settings in an XML configuration file. To replicate multiple SAS Data Sets accounts, use multiple configuration files. Below is an example configuration to replicate SAS Data Sets to SQLite:

Windows

<?xml version="1.0" encoding="UTF-8" ?> <CDataSync> <DatabaseType>SQLite</DatabaseType> <DatabaseProvider>System.Data.SQLite</DatabaseProvider> <ConnectionString>URI=C:/myfolder;</ConnectionString> <ReplicateAll>False</ReplicateAll> <NotificationUserName></NotificationUserName> <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString> <TaskSchedulerStartTime>09:51</TaskSchedulerStartTime> <TaskSchedulerInterval>Never</TaskSchedulerInterval> </CDataSync>

Java

<?xml version="1.0" encoding="UTF-8" ?> <CDataSync> <DatabaseType>SQLite</DatabaseType> <DatabaseProvider>org.sqlite.JDBC</DatabaseProvider> <ConnectionString>URI=C:/myfolder;</ConnectionString> <ReplicateAll>False</ReplicateAll> <NotificationUserName></NotificationUserName> <DatabaseConnectionString>Data Source=C:\my.db</DatabaseConnectionString> </CDataSync>

Set the following connection properties to connect to your SAS DataSet files:

Connecting to Local Files

  • Set the Connection Type to "Local." Local files support SELECT, INSERT, and DELETE commands.
  • Set the URI to a folder containing SAS files, e.g. C:\PATH\TO\FOLDER\.

Connecting to Cloud-Hosted SAS DataSet Files

While the driver is capable of pulling data from SAS DataSet files hosted on a variety of cloud data stores, INSERT, UPDATE, and DELETE are not supported outside of local files in this driver.

Set the Connection Type to the service hosting your SAS DataSet files. A unique prefix at the beginning of the URI connection property is used to identify the cloud data store and the remainder of the path is a relative path to the desired folder (one table per file) or single file (a single table). For more information, refer to the Getting Started section of the Help documentation.

Configure Queries for Each SAS Data Sets Instance

Sync enables you to control replication with standard SQL. The REPLICATE statement is a high-level command that caches and maintains a table in your database. You can define any SELECT query supported by the SAS Data Sets API. The statement below caches and incrementally updates a table of SAS Data Sets data:

REPLICATE restaurants;

You can specify a file containing the replication queries you want to use to update a particular database. Separate replication statements with semicolons. The following options are useful if you are replicating multiple SAS Data Sets accounts into the same database:

You can use a different table prefix in the REPLICATE SELECT statement:

REPLICATE PROD_restaurants SELECT * FROM restaurants

Alternatively, you can use a different schema:

REPLICATE PROD.restaurants SELECT * FROM restaurants

Run Sync

After you have configured the connection strings and replication queries, you can run Sync with the following command-line options:

Windows

SASDataSetsSync.exe -g MyProductionSASDataSetsConfig.xml -f MyProductionSASDataSetsSync.sql

Java

java -Xbootclasspath/p:c:\sqlitejdbc.jar -jar SASDataSetsSync.jar -g MyProductionSASDataSetsConfig.xml -f MyProductionSASDataSetsSync.sql