Example 2: Using MS-SQL Connector as a Destination
Transfer customer information from an entity to MS-SQL table.
Solution
To create an ETL pipeline that transfers customer data from a Langstack entity to an MS-SQL table, follow the steps below.
To go to the ETL pipeline template, click on “ETL pipeline” under the “Data management” menu on the left side panel.
To create a new ETL pipeline, click the [+ ETL pipeline] button under the “ETL pipeline” tab.
To label the ETL pipeline and execution log entity, add the following information:
Enter the ETL pipeline name as “Tutorial_entity_to_MSSQL”.
Optionally add a description for this ETL pipeline.
Enter a name for the execution log for this ETL pipeline as “Tutorial_entity_to_MSSQL”. This log will be updated each time the ETL pipeline is executed and can be viewed by clicking “Go to records”.
To connect to the data source, add the necessary information needed in the “Data source section:
Select the “Entity” tab.
Select “TEST_MSSQL_Entity” from the drop down menu.
To connect to the data destination, add the necessary information needed in the Data destination” section:
Select the “Connector” tab.
For this exercise, “MSSQLconn” 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.
Click the “Edit the settings” arrow. In the settings, to add or update the details for the MSSQL Connector, update the required fields.
To disallow multiple simultaneous runs of the ETL pipeline, leave the toggle button 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”. For this exercise, keep it as is.
To align the source fields with destination fields, click on the “Data Format” tab. In this tab, the settings for the reader and writer format are defined (based on the settings for the data source and the data destination). The “Reader” tab is selected by default.
In the Source fields section, three fields are added by default: _modified_date, _created_date, _id.
To add source fields, click the [+Field] button. Add all required fields.
For this exercise, four fields have been added as per the image below.
To update the settings for how the data from the source should be written to the target destination, select the “Writer” tab.
Select writer stream as “Database Stream”.
Click the “Edit the settings” arrow.
To add details necessary to write the records, define the settings in this section “MS-SQL format details”:
Add the MS-SQL table name. For this exercise, the table used as a source is “TEST_MSSQL”. This is the name of the table that the ETL pipeline will write the data in. This name must be exactly the same as the name for the destination table name.
Since, the writer mode is selected as “Append” in the next step, adding the column names is optional. If the writer mode is Add & Update, Update, Delete or Sync, then the addition of primary key/unique key column names is required as based on the primary key/unique key, the record to be updated will be identified. For this example, the source column names are added separated by a comma ‘,’ as: “customer_id, customer_name,customer_email,active_status”.
Click the [Accept & Collapse] button. This will save the information related to the destination table.
To define that the data is written to the destination as mentioned in reader source fields without transforming it, select “Writer Mode” as “Append”.
To map the reader source fields to the entity destination fields, select the “Field Mapping” tab.
To add the Mapped Fields, click the [+ Field] button and add relevant information.
To align the fields, add the following information:
Select the “Reader” field in “Mapping Sources” by clicking the drop down menu. e.g. in case of Customer ID, click on the “Mapping sources” drop down menu, select Variables>reader>customer_id.
Enter destination column to map the reader field to in Mapped to e.g. in case of customer ID, enter “customer_id”. It is important that the destination table column name is exactly the same as the name entered in this field.
Select data type reflecting the data type of the destination field.
Add all the “Mapped Fields”.
To save the ETL pipeline, click [Save].
To publish the ETL pipeline, click [Publish].
Ensure the ETL pipeline is enabled.
Click the [Run] button.
The Run ETL pipeline dialog box displays. Click [Run] to execute the ETL pipeline.
The ETL pipeline will be executed and the records will be added to the destination file.
To verify, go to the MS-SQL table and add the command to retrieve all records from the Entity. The column names will be displayed as per 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.
“activity_status” field in source: IMAGE
“activity_status” field in destination: IMAGE
Last updated