SQL Sync Job

The SQL sync job allows you to get data from a SQL database.

In this case you will need to specify a few “special” cases.

1. You have to define the Connection string for the target database.

2. You have to define the queries to get the data. You can use views , define parameters, and much more. But you have to make sure the SQL returns exactly one table.

3. You (may) have to define a connection string where “job progress” can be stored. This is to make sure that several calls to the service only return the “delta’s” iso a full table export. In that case you also have to set the “schema name” that should be used.

All those settings are set in the “Delta Fetch” configuration section.

An example config

The below configuration (in json format) defines a SQL job.

{
    "source": "SQL",
    "raptorTenantId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
    "TaxonomyConfig": {
          "UseEntitySource": "EXAMPLE"
    },
    "packageInfo": {
        "package": "TEST-PACKAGE",
        "source": {
            "connectionString": "Server=SRV\\DB;Database=XX;User Id=USR;Password=PASS;"
        },
        "storage": {
            "connectionString": "inherit",
            "schema": "rptr-integration-demo"
        },
        "entities": [
            {
                "name": "INVOICES",
                "select": "
DECLARE @date AS NVARCHAR(50);
IF (@timestamp IS NULL) 
	SET @date = '' 
ELSE 
    SET @date = CONVERT(varchar, @timestamp, 23); 

SELECT 
    [inv].[id] AS [ID], 
    [inv].[net amount] AS [AMOUNT],
    [inv].[ref] AS [CUSTOMER]
FROM [dbo].[invoice-table] AS [inv]
WHERE [inv].[change] >= @date OR [inv].createdAt >= @date",
                "timestampParameter": "timestamp"
            },
            {
                "name": "ORDERS",
                "select": "SELECT * FROM [dbo].[ORDERS]"
            },
            {
                "name": "USERS",
                "select": "SELECT * FROM [dbo].[user table]",
                "timestampParameter": "none"
            }
        ]
    }
}

If we take a closer look at this config, we can see that there are 3 tables exported, so the output of this “packager” will be a zip file with 3 xml files in it. One “INVOICES.xml”, a “ORDERS.xml” and a “USERS.xml”. We can also see that we will always export all the orders, and all the users because the query does not use the timestamp parameter.

The best example of the power that this SQL packager offers is the “Invoice” example.

Here we can see that:

• We can choose which columns we export

• We can rename columns (by adding “AS [NEWNAME]”)

• We can use any kind of logic (as long as we eventually do only one SELECT… statement)

• We used “inherit” as value for the storage connection string. This works when the SQL packager runs in our server park. But when you would run the SQL packager on prem you may have to set a connection string here.

• The package name is “TEST-Package”, this is used to get the correct timestamp parameter. So if you want to do a “full” sync iso a “delta” sync. You could just change the package name.

Important to know:

• The column names have to be valid XML tag names.

• The column names have to match the entity configuration

• The entity configuration “source” can be overridden (By setting TaxonomyConfig.UseEntitySource) In the case of our example, the taxonomy service would look for entity config “EXAMPLE”

About the “storage” section:

The storage connection string and namespace define where the “progress” of the delta packager will be stored. The schema is important and has to be created by 9Altitudes in case the “cloud” packager is used. Otherwise it has to be an existing schema on your own database, to which the login (defined in the connection string) has full access to (read / write / create table / … )