MS-SQL connector as a destination
Last updated
Last updated
The ETL pipeline, to transfer customer data from a Langstack entity to an MS-SQL table, is created as follows.
To see step-by-step instructions of how to create an ETL pipeline, click here.
An ETL pipeline named “Tutorial_entity_to_MSSQL” is created.
To connect to the data source, the necessary information is added as follows:
The “Entity” tab is selected.
The “Test_MSSQL_Entity” entity is selected from the drop-down menu.
To connect to the data destination, the necessary information is added as follows:
The “Connector” tab is selected.
For this example, “Tutorial_MSSQL” is selected from the drop-down menu. The connector can be added by selecting a connector from the drop-down menu or a new connector can be created by clicking the [+] button.
To go to the settings, click the “Edit the settings” arrow.
To disallow multiple simultaneous runs of the ETL pipeline, the toggle button is left enabled for “skip execution while in progress”. Enabling this toggle button defines that the execution of this ETL pipeline will be skipped when there is one already in progress.
The default selection for ETL pipeline execution is “Immediate”.
To align the source fields with destination fields, the settings for the reader and writer format are defined in the “Data Format” tab. The “Reader” tab is selected by default.
Select the fields for the Reader. For this example, the fields are added as per the image below.
To update the settings for how the data should be written, select the “Writer” tab.
The writer stream is “Database Stream.”
To add the table name, click on the “Edit the settings” arrow.
To add details necessary to write the records, the settings in this section “MS-SQL format details” are defined as follows:
The MS-SQL table name is added "TEST_MSSQL".
On “Append” mode, the writer does not require the addition of the column names in "MSSQL format details". The user needs to enter source column names separated by a comma ‘,’ such as: “customer_id, customer_name,customer_email,active_status” in this example.
Click on "Accept & Collapse" to save the information.
If the writer mode is Add & Update, Update, Delete or Sync, then the user needs to enter primary key/unique key column names (highlighted as number '2' in the image). This is to help identify the target record based on the primary key/unique key.
In the Field Mapping section, all the “Mapped Fields” are added and aligned.
When the ETL pipeline is executed (after Save and Publish), the records will be added to the destination.
The records in the MS-SQL table will look as per the image below. The boolean data type in Langstack is mapped to the bit data type in MS-SQL in case of the field “active_status”. The false is converted to 0 and true is converted to 1.
