Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 199 of 212

187 C H A P T E R 9 | Introducing Azure SQL Data Warehouse Note You can also click the Monitor & Manage tile in the data factory blade to open a new browser tab containing a view of data factory assets and the status of activities, among other information. You can learn more about using this monitoring and management app at "Monitor and manage Azure Data Factory pipelines using new Monitoring and Management App," You can also use ADF to execute stored procedures in your SQL Data Warehouse on a scheduled basis. To do this, you use the Stored Procedure activity in a pipeline. First, use the CREATE PROCEDURE statement in SQL Data Warehouse as you would in SQL Server. Then, in your data factory, define a linked service for your SQL Data Warehouse and an output dataset to hold the results of the stored procedure, which you can then use in a downstream process. At the time of this writing, an output dataset is required even if your stored procedure writes data directly into a table. In that case, create a dummy dataset to use as the output. Next, create a pipeline to execute the stored procedure, as shown in Example 9-6. The optional storedProcedureParameters property contains a list of the stored procedure's parameter names and values. Note that the parameter name is case-sensitive and that you can use an ADF variable like SliceStart to pass the execution date and time to the stored procedure. You can also pass a static value by enclosing the value in double quotation marks. Example 9-6: Creating a pipeline to execute a stored procedure { "name": "SQLDWStoredProcPipeline", "properties": { "description": "Execute a stored procedure in SQL Data Warehouse every day", "activities": [ { "type": "SqlServerStoredProcedure", "name": "SQLDWStoredProc", "description": "Stored procedure activity", "typeProperties": { "storedProcedureName": "sp_DoSomething", "storedProcedureParameters": { "DateTime": "$$Text.Format('{0:yyyy-MM-dd HH:mm:ss}', SliceStart)" } }, "outputs": [ { "name": "SQLDWStoredProcOutput" } ], "scheduler": { "frequency": "Day", "interval": 1 } } ], "start": "2016-04-01T00:00:00Z", "end": "2016-04-15T01:00:00Z" } }

Articles in this issue

Links on this page

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016