Setting up a Dataflow
Explore how to set up a dataflow in Azure Data Studio by integrating PowerShell scripting with SQL commands. This lesson shows you how to orchestrate data copying between servers, overcoming SQL limitations, and using PowerShell's capabilities to enhance data movement and transformation across environments.
Introduction
When you are working directly on a database server, whether entering SQL queries or performing data manipulations, there is a really good chance you are using an SQL editor that provides a run button. Of course, this is the case with Azure Data Studio, SQL Server Management Studio, or any of the non-Microsoft SQL editing programs including Toad, MySQL Workbench, or Oracle SQL Developer.
This is fine if your task at hand is fully SQL-centric. However, oftentimes we need to go beyond the SQL language constructs and leverage capabilities better suited to other languages, computer applications, or existing system utilities. A common example of this would be a database backup (using the SQL BACKUP command) that needs to be copied over the network to another computer (using perhaps Windows’ XCOPY command).
Leaning on what you learned in the previous chapter, we can apply this BACKUP and COPY to the car_crash database with a Jupyter Notebook, using the PowerShell Kernel, and the following script:
This approach issues both a SQL command ( ...