Overview

A "SQL Script" data source enables the user to create a data source that will populate data based on a SQL script. This can be used for populating things like the Date table in the Cosmos Data Model, or to bring in smaller amounts of custom data that may not exist in other data sources. This article will cover how to add a SQL Script data source to your Cosmos Data Model. 

Details

Creating the SQL Script Data Source

To create a SQL Script data source, the user will log in to the Cosmos Portal and navigate to the Data Management section. From there, the user will click the New Data Source button. 

In the "New Data Source" window, type the name of the new data source and set the Type field to be "SQL Script" and click Create.

Defining the Data Source Schema

Once the data source has been created, you can expand it in the Data Source List to set up the schema. The schema is the definition of the fields that will be created in the staging area for the data source. For example, if you have a SQL script that will populate values for a set of "No" and "Name" fields, you will want to create matching fields in the data source schema. This way there are physical tables created in the SQL database behind the scenes for these values to be mapped into. 

To make changes to the schema, click the Edit button. This will enable you to edit the data source name as well as add or modify the schema fields. 

In the example below we have created a SQL Script data source named "Custom List" that currently does not have any fields associated with it.

To add fields, click the Add Field button. This will add a row that can be used to define a field for the data source.

In the example above, two fields have been added: "No" and "Name". For each field, a Data Type will be defined. This will determine the type of field that is created in the SQL database to hold the data for that field. The possible data types that can be specified are:

• Boolean
• Date
• Decimal
• Number
• Text

To remove a field, click the - button on the right. This will delete the corresponding row.

Inserting the SQL Script

Once the data source schema has been defined, you can paste in the SQL Script that will be used to generate the data in the Script section. 

The example below will insert a set of two records into the "No" and "Name" fields that were created above.

INSERT INTO $[Custom List] ([No], [Name])
VALUES ('1','Number 1'), ('2','Number 2');

Since this script will be used to populate the underlying table, the script is required to include a SQL "INSERT INTO" statement. The table specified must be in the format of $[<Data Source Name>]. In the example above, the data source in Cosmos is named "Custom List", so the table used in the script would need to be $[Custom List]. The names of the fields specified in the INSERT INTO will also need to exactly match the names of the fields defined in the data source schema.

The image below illustrates how the script table needs to correlate to the data source name and how the script fields need to correlate to the schema fields.

Once the schema has been defined and the script has been properly applied, click Save to save the changes.

When the Run Pipeline button is pressed, the corresponding SQL table will be created to match the name of the script, fields will be created to match the ones defined in the schema, and the SQL script will be run. It is important to note that the SQL script used to populate the table will only be run when the Run Pipeline button is pressed; the script will not be triggered every time that a scheduled or manual pipeline is run.

Working with the SQL Script Table and Fields in the Data Model

Once the SQL Script data source has been defined and saved, you can navigate to the Staging tab and see the data source and table/fields. The table and fields will need to be selected the same as any other data source.

Once the table and fields have been selected, they can be used in lookups, transformations, and mapped into the reporting database for business users to report on.