Visual Studio 2019 Configuration for the Production DBA


By:   |   Updated: 2020-09-22   |   Comments   |   Related: More > Install and Uninstall


Problem

As a Production SQL Server Database Administrator, you do not usually find yourself in Visual Studio to perform your work. However, there are those few times you are called into troubleshooting an SSIS, SSRS or SSAS problem. The steps below will have you on your way to getting Visual Studio 2019 configured for you to save the day!

You might have noticed that it is not as easy as opening Visual Studio 2019 and getting started. "Back in my day" as the old schoolers would say, you just opened BIDS or SSDT, the tools you needed were right in front of you. Now, there are extensions, downloads, and installers that you must navigate through to get a proper environment setup for these actions.

Solution

The options available to the SQL Data Professional to edit/view Integration and Reporting services files are limited. I have found that the steps below offer a stepping-stone to getting your environment built to perform these actions.

Install Visual Studio 2019

Getting started, navigate to your preferred method for downloading Visual Studio 2019.

For this example, I am downloading the Professional edition trial from the Visual Studio Download page.

Once you have downloaded the installer file from the website, run the .exe by Double-Clicking the file to start the installer.

install visual studio

Since we are doing a simple install, I will choose the ASPNET and Web Development installation package. Notice on the right side you will be shown the options that will be installed during the process.

Click the Install button to begin the installation with the option chosen.

The installer will begin and proceed to download and install the components needed for the installation type you chose previously.

Once the installation is complete, go ahead and reboot your computer.

Now that your computer is back online, open Visual Studio 2019 from the Start Menu.

install visual studio

Now we will create a new Integration Services project by Clicking Create a New Project.

create new project visual studio

You can narrow down the list of project types by entering SQL in the search bar.

create new project visual studio

As you see there is no option in the list for Integration Services to be selected. You will need to install the proper extensions to get your Visual Studio 2019 environment configured for the services you might be asked to troubleshoot.

Installing Visual Studio Extensions

From here, Click the Back button to get back to the main Visual Studio landing page.

install visual studio extensions

Now Click on the Continue without code link to get into a blank Visual Studio editor.

From the top menu choose Extensions > Manage Extensions.  In the search box type SQL Server to narrow the list of extensions down.

Scroll through the list and find the extensions listed below to download. Click the Download button on each item.

  • SQL Services Integration Services Projects
  • Microsoft Reporting Services Projects
  • Microsoft Analysis Services Projects
install visual studio extensions ssis
install visual studio extensions ssrs
install visual studio extensions ssas

Installing SQL Services Integration Services Extension

When you Click Download for the Integration Services extension you will be prompted to download an .exe file.

After the download completes, Double-Click the file to start the installation.

install visual studio extensions ssis

Choose your language settings and Click Install to begin the extension installation.

install visual studio extensions ssis

When the installation is complete, you will need to Click Close to finish.

install visual studio extensions ssis

Installing SQL Services Reporting Services and Analysis Services Extension

From the Manage Extensions screen, Click download on each extension. Next, close Visual Studio 2019 and relaunch prompting the extensions to download.

install visual studio extensions ssis

Once the download is complete the installation will begin.  After a couple minutes you will receive a message like the one below.

install visual studio extensions ssis

This means that now all the expected extensions are downloaded and installed into your Visual Studio 2019 environment and ready to use.

Perform one final restart of your computer and then we will get started creating projects.

Using Visual Studio 2019 to add an existing RDL to a Reporting Services Project

Launch Visual Studio 2019 from the Start Menu.

create new project

As you can see, now you have a list of tools we can use to customize your environment.

Click Report Server Project Wizard and then Next.

configure new project

Now enter your Project Name and Location then Click Create.

configure new project

You now have an empty SSRS project that you can create new Data Sources or Report and even add existing RDL files to review and check for problems.

To add an existing RDL file you might have downloaded from a Reporting Services Server, begin by Right-Clicking on Reports.

add existing item

Then choose the Add > Existing Item option.

You will be prompted to browse for the *.RDL file you want to add to the project.

add existing item

Choose the filename and then Click Add.

You now have the RDL file added to your local Reporting Services Project.

To begin troubleshooting, Double-Click on the report in the Solution Explorer window.

add existing item

With the Report now loaded in the Designer you can begin your troubleshooting efforts.

report development

Using Visual Studio 2019 to add an existing DTSX to an Integration Services Project

Launch Visual Studio 2019 from the Start Menu.

create new project

From the project template list choose Integration Services Project and Click Next.

You can now enter the information for your new Integration Services Project.

configure new project

Once everything is entered as you want it Click Create.

You can see a new Integration Services Project is created with an empty Package.dstx file for you to begin work.

solution explorer

However, if you are looking to troubleshoot an existing Integration Services Package you will want to Right-Click SSIS Packagesand choose Add Existing Package.

add existing package

Based on the location of the Package that you want to add from choose the correct option from Package Location.

add existing package

For this example, we are going to use the File System location and browse to the *.DTSX file(s) on the local computer and Click Open.

add existing package

Once you Click Open, the package will be added to the existing Integration Services Project and upgraded automatically if required.

add existing package

Click Ok to complete the activity.

edit package

You can now see that the imported *.DTSX package has been added to our project.

If you Double-Click the Integration Services file it will load the definition in the editor on the left. From here you can see the Control Flow, Data Flow, Parameters and Connection Manager. This will allow you to start troubleshooting this Integration Services Package.

Next Steps


Last Updated: 2020-09-22


get scripts

next tip button



About the author
MSSQLTips author Garry Bargsley Garry Bargsley is a SQL Server Database Administrator with over 20 years experience in the technology field. His interests and specializations are SQL, Azure, PowerShell and Automation.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Cleanly Uninstalling Stubborn SQL Server Components

SQL Server Setup Error - There was an error generating the XML document. Error code 0x84B10001.

SQL Server install error Wait on the Database Engine Recovery Handle Failed

Fix SQL Server Installation issue Wait on the Database Engine recovery handle failed with Single User Mode Login Option

SQL Server Installation Best Practices








get free sql tips
agree to terms