Configure Microsoft SQL as your Celigo sync destination with automated project detection and straightforward dataset naming—eliminating the need for a separate database selection step. Complete with comprehensive data-type mappings and reserved-word references, this article helps you ensure seamless data integration and accurate field alignment.
Create a data ingestion sync
Follow the instructions in Create a data ingestion sync to create a sync and configure your source application.
Set up Microsoft SQL as your destination
- Select Microsoft SQL as the destination that will ingest and store all source data from the sync.
-
Choose an established connection (Cloud mode is recommended), or select Create connection to create a new one (see Create a connection to an application).
Caution
Avoid sharing connections with flows scheduled at the same time, as running them simultaneously can reduce performance.
-
Name your destination schema - Celigo creates a schema based on the objects, exports, and fields you selected when configuring the source application. Give the schema or dataset a meaningful name to help you identify the destination tables that correspond to it later.
Note
This schema is for use only with this sync. Be careful when entering its name — you can't change it after you save and run the sync.
This sync creates destination tables using the following naming structure: <database>.<schema>.<table>
-
<database>- The database configured on your selected connection. -
<schema>- The schema name you specified. -
<table>- The specific object, export name, or custom table name selected for sync.
Define sync settings
Name and describe your sync and choose whether to receive email notifications (see Edit or view a sync's settings). Next, decide how often the sync should run (see Schedule and run a sync for the full set of scheduling options).
What happens next
After you save your sync's settings, the wizard is complete and you're returned to the sync's dashboard. The dashboard shows a "Ready to run" state — your sync is active, but hasn't loaded any data yet. Select Run to trigger the first run yourself, or wait for its next scheduled run.
Data type mapping
The data types sent to or inferred by Celigo syncs are transferred to Microsoft SQL as follows (see Understand how data types are inferred for what each Celigo data type represents):
| Celigo data type | Microsoft SQL data type |
|---|---|
| SHORT | BIGINT |
| INTEGER | BIGINT |
| LONG | BIGINT |
| NUMERIC | BIGINT |
| NUMERICDECIMAL | DECIMAL |
| NUMERICFLOAT | FLOAT |
| FLOAT | FLOAT |
| DOUBLE | FLOAT |
| NUMBER | DECIMAL(p,s) |
| DECIMAL | DECIMAL(p,s) |
| STRING | NVARCHAR(n) or NVARCHAR(MAX) |
| BINARY | VARBINARY |
| BOOLEAN | BIT |
| DATE | DATE |
| TIME | TIME |
| TIMETZ | TIME |
| DATETIMENTZ | DATETIME2 |
| TIMESTAMP_NTZ | DATETIME2 |
| DATETIMETZ | DATETIMEOFFSET |
| DATETIMELTZ | DATETIMEOFFSET |
| TIMESTAMP_LTZ | DATETIMEOFFSET |
| TIMESTAMP_TZ | DATETIMEOFFSET |
| ARRAY | NVARCHAR(MAX) |
| OBJECT | NVARCHAR(MAX) |
| VARIANT | NVARCHAR(MAX) |
| DEFAULT (any unmapped type) | NVARCHAR(MAX) |
Every table a Micorsoft SQL sync creates also includes three Celigo-managed system columns:
| Column | Microsoft SQL data type |
|---|---|
_celigo_extracted_at |
DATETIMEOFFSET |
_celigo_loaded_at |
DATETIMEOFFSET |
_celigo_deleted |
BIT |
Microsoft SQL reserved words
To avoid any conflicts at the destination, Celigo sync automatically handles the use of any Microsoft SQL Server (T-SQL) reserved words when creating or updating tables. You may freely use these keywords when defining your source data, although you will see variants in the data warehouse after the sync runs, such as a new table named name_ instead of the expected (reserved word) NAME.
The following keywords are reserved for both table and column identifiers in Microsoft SQL Server (T-SQL):
| Reserved keywords | |
|---|---|
| A | ALL, ALTER, AND, ANY, ARRAY, AS, ASSERT_ROWS_MODIFIED, AT |
| B | BETWEEN, BY |
| C | CASE, CAST, CHECK, COLLATE, COLUMN, CONNECT, CONSTRAINT, CONTAINS, CREATE, CROSS, CUBE, CURRENT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER |
| D | DEFINE, DELETE, DISTINCT, DROP |
| E | ELSE, ENUM, ESCAPE, EXCLUDE, EXISTS, EXTRACT |
| F | FALSE, FETCH, FOLLOWING, FOR, FROM, FULL |
| G | GRANT, GROUP, GROUPING |
| H | HASH, HAVING |
| I | IF, IGNORE, IN, INNER, INSERT, INTERSECT, INTERVAL, INTO, IS |
| J | JOIN |
| L | LATERAL, LEFT, LIKE, LIMIT, LOCALTIME, LOCALTIMESTAMP, LOOKUP |
| M | MERGE |
| N | NATURAL, NEW, NO, NOT, NULL, NULLS |
| O | OF, ON, OR, ORDER, OUTER, OVER |
| P | PARTITION, PRECEDING, PROTO |
| R | RANGE, RECURSIVE, RESPECT, REVOKE, RIGHT, ROLLUP, ROW, ROWS |
| S | SAMPLE, SELECT, SET, START, STRUCT |
| T | TABLE, TABLESAMPLE, THEN, TO, TREAT, TRIGGER, TRUE, TRY_CAST |
| U | UNBOUNDED, UNION, UNIQUE, UNNEST, UPDATE, USING |
| V | VALUES |
| W | WHEN, WHENEVER, WHERE, WINDOW, WITH, WITHIN |