Executing SQL code from Data Factory
Azure Data Factory, and through Data Factory, Synapse integration, lets you choose the most suitable way to load data into a Synapse SQL pool. You just used the code-less option by implementing a Data Factory data flow. You already imported data into your Synapse SQL pool using T-SQL code in Chapter 8, Provisioning and Implementing an Azure Synapse SQL Pool. Let's have a quick look at how to automate the option where you use T-SQL code using Synapse pipelines.
In the following section, we will assume that you have implemented the dimDate, dimCustomer, dimProduct, and factOrder tables, as discussed and created in Chapter 8, Provisioning and Implementing an Azure Synapse SQL Pool. We will create a stored procedure in the SQL database and execute it using a pipeline activity. The stored procedure will do a full load of the factOrder table based on the exports of the dbo.Order and dbo.OrderDetail tables you created in the section regarding the...