Visual Studio 2017: It is an integrated development environment (IDE) which allows us to develop various project types in different programing languages. SQL Server Data Tools: It is also known as SSDT, another required tool to develop SQL unit testing projects. SSDT allows us to develop SQL Server database projects and it also provides. SSDT will install on its own a Visual Studio Shell which contains all that is needed to execute the program but without allowing the users to use functionality other than that contained on SSDT.
-->Use Visual Studio to query dedicated SQL pool using Azure Synapse Analytics. This method uses the SQL Server Data Tools (SSDT) extension in Visual Studio 2019.
Note
Serverless SQL pool is not supported by SSDT.
Prerequisites
To use this tutorial, you need to have the following components:
- An existing Synapse workspace, If you need to create one see Creating a Synapse workspace
- A dedicated SQL pool. If you do not have one, see Create a dedicated SQL pool.
- SSDT for Visual Studio. If you have Visual Studio, you probably already have this component. For installation instructions and options, see Installing Visual Studio and SSDT.
- The fully qualified SQL server name. To find this server name, see Connect to a dedicated SQL pool.
1. Connect to a dedicated SQL pool
Open Visual Studio 2019.
Open the SQL Server Object Explorer by selecting View > SQL Server Object Explorer.
Click the Add SQL Server icon.
Fill in the fields in the Connect to Server window.
- Server name: Enter the server name previously identified.
- Authentication: Select SQL Server Authentication or Active Directory Integrated Authentication:
- User Name and Password: Enter your user name and password if SQL Server Authentication was selected above.
- Click Connect.
To explore, expand your Azure SQL server. You can view the databases associated with the server. Expand AdventureWorksDW to see the tables in your sample database.
2. Run a sample query
Now that a connection has been established to your database, you'll write a query.
Right-click your database in SQL Server Object Explorer.
Select New Query. A new query window opens.
Copy the following T-SQL query into the query window:
Run the query by clicking the green arrow or use the following shortcut:
CTRL
+SHIFT
+E
.Look at the query results. In this example, the FactInternetSales table has 60398 rows.
Next steps
Now that you can connect and query, try visualizing the data with Power BI.To configure your environment for Azure Active Directory authentication, see Authenticate to dedicated SQL pool.
By: Sergey Gigoyan | Updated: 2020-07-09 | Comments (23) | Related: More >Integration Services Development
Free MSSQLTips Webinar: A Powerful and Secure Alternative to SSIS
SSIS is the de facto solution for SQL Server data integration and transformation. However, standard ETL processes are typically slow and result in data that is not always current which impedes decision making and business processes. Organizations are increasingly in need of better performing real-time data access without sacrificing critical access controls and data activity monitoring.
Problem
SQL Server Integration Services is Microsoft’s powerful platform for implementingExtract, Transform and Load (ETL) solutions. It allows SQL Server Professionalsto solve complex business intelligence tasks and work with a wide range of datasources. Microsoft provides a graphical tool for designing and developing IntegrationServices projects. This tool has always been an integral part of the Visual Studio.However, the name of this tool as well as the method of its installation variesfrom version to version of Visual Studio (VS). In this article, we are going toillustrate how to install the SQL Server Integration Services development tool forthe latest version of Visual Studio.
Solution
As mentioned above, Microsoft’s tool for developing Integration Servicesprojects is named differently in different versions of the Visual Studio. For example,for the SQL Server 2008/2008R2 integration services, this tool was VS 2008’sBusiness Intelligence Development Studio (BIDS) and came with the SQL Server installationmedia. In VS 2010, the tool was renamed as the SQL Server Data Tools (SSDT) andshipped as a part of the SQL Server 2012 installation media. In VS 2012/2013, thistool was slightly renamed as SSDT-BI and needed a separate download. In VS 2015,it was called SSDT and needed a separate download. In VS 2017, this tool could beinstalled during the VS installation. It is worth mentioning that BIDS, SSDT-BI,and SSDT can be used not only for working with SSIS projects, but also SQL ServerAnalytical Services and SQL Server Reporting Services projects. Additionally, itis important to note that SSDT is backward compatible, which means that projectsdesigned on the newer versions of SSDT can run on older versions of SQL Server.
Now, after that historical review, let’s move to the latest release ofVisual Studio – VS 2019 and see what this version provides as an SSIS developmenttool. For this version of the VS, there is no standalone installer for SSDT. Whilein this version the core components of SSDT for creating database projects haveremained integrated in Visual Studio, the Integration Services, Analysis Services,and Reporting Services projects have become just an extension of the VS. Thus, inorder to install the tool for SSIS development, we first need to install VS 2019.Therefore, we will divide our article into two parts. In the first part we willdescribe the installation of VS 2019 and in the second part, will illustrate theSSIS’s extension installation.
Installing Visual Studio 2019
To download Visual Studio 2019 we will use thefollowinglink.
For our example, we will download the free, Community edition of this product:
Then, we will click on 'Free download'. After downloading the.exe file, we will double click on it to start the process:
The Visual Studio installer starts and after some time the following window isopened:
To add SSDT, we scroll down and choose 'Data storage and processing'.Then, we press the 'Install' button and wait for the installationto complete:
After installation, restart is required, so we press 'Restart':
After having our computer rebooted, we start Visual Studio:
On the next screen, just to keep our example simple, we just click 'Notnow, maybe later.' instead of signing in:
After that, we choose the theme and click on 'Start Visual Studio':
Well, we have installed Microsoft Visual Studio 2019 on our computer. Now, it’stime to add the extension for developing Integration Services projects.
Adding the SSIS Projects extension to the Visual Studio 2019
When Visual Studio is opened, we click on 'Continue without code'to add the necessary extension:
I Could Not Find Ssdt In Visual Studio 2017
In this window, we click on 'Extensions' > 'Manage Extensions':
In the search bar of the opened window, we type 'Integration Services'to easily locate the extension. From the appeared list we choose 'SQL ServerIntegration Services Projects' and press 'Download':
Then, we will execute the downloaded .exe file:
The installation of the extension begins. Now, we will follow some simple steps.In the next window we click 'OK':
Using Ssdt In Visual Studio 2019
After that, we click 'Next' to continue:
If you receive the following message, you probably have SQL Server ManagementStudio opened:
Close it and click 'OK'. The process should continue:
Finally, the setup is completed and we have our extension installed:
Now, we are ready to create Integration Services projects. In Visual Studio,we choose 'Create a new project':
In the next window, we type 'integration' to find 'Integration ServicesProject' and click on it:
We choose a name for our project:
Hence, it is ready! We opened the interface where we can design and develop SSIS2019 packages:
Conclusion
To sum up, we reviewed the updates related to the tool for SQL Server IntegrationServices projects development in the VS 2019. As we have seen, in this version ofthe VS, the tool for designing SSIS projects is installed as an extension of theVisual Studio. We have also explored how to install Visual Studio 2019 and how toadd the Integration Services Projects extension to it.
Next Steps
Please find additional, topic-related information using the following links:
Last Updated: 2020-07-09
About the author
View all my tips