Just out of curiosity, here is top level comparison; first one is managed instance, second one is Single database;
Top level comparison;
Databases level comparison;
System level databases;
Single database has just master database;
master database object layout
System level security
Single database does not have these root level objects;
Server Objects, Replication, Management, SQL Server Agent, XEvent Profiler
Integration Services Catalogs doesn’t exists on both services. Azure Data Factory integration services need to be provisioned to create SSIS database under Integration Services Catalogs.
Reporting services does not exists here. Power BI integrated Reporting services needs to be provisioned.
This quickstart demonstrates how to use SQL Server Management Studio (SSMS) to connect to the SSIS Catalog database, and then use Transact-SQL statements to run an SSIS package stored in the SSIS Catalog.
SQL Server Management Studio is an integrated environment for managing any SQL infrastructure, from SQL Server to SQL Database. For more info about SSMS, see SQL Server Management Studio (SSMS).
An Azure SQL Database server listens on port 1433. If you’re trying to connect to an Azure SQL Database server from within a corporate firewall, this port must be open in the corporate firewall for you to connect successfully.
Supported platforms
You can use the information in this quickstart to run an SSIS package on the following platforms:
To run the package on Azure SQL Database, get the connection information you need to connect to the SSIS Catalog database (SSISDB). You need the fully qualified server name and login information in the procedures that follow.
Select SQL Databases from the left-hand menu, and then select the SSISDB database on the SQL databases page.
On the Overview page for your database, review the fully qualified server name. To see the Click to copy option, hover over the server name.
If you forget your Azure SQL Database server login information, navigate to the SQL Database server page to view the server admin name. You can reset the password if necessary.
Connect to the SSISDB database
Use SQL Server Management Studio to establish a connection to the SSIS Catalog on your Azure SQL Database server.
Open SQL Server Management Studio.
In the Connect to Server dialog box, enter the following information:TABLE 1SettingSuggested valueMore infoServer typeDatabase engineThis value is required.Server nameThe fully qualified server nameIf you’re connecting to an Azure SQL Database server, the name is in this format: <server_name>.database.windows.net.AuthenticationSQL Server AuthenticationWith SQL Server authentication, you can connect to SQL Server or to Azure SQL Database. If you’re connecting to an Azure SQL Database server, you can’t use Windows authentication.LoginThe server admin accountThis account is the account that you specified when you created the server.PasswordThe password for your server admin accountThis password is the password that you specified when you created the server.
Click Connect. The Object Explorer window opens in SSMS.
In Object Explorer, expand Integration Services Catalogs and then expand SSISDB to view the objects in the SSIS Catalog database.
Run a package
Run the following Transact-SQL code to run an SSIS package.
In SSMS, open a new query window and paste the following code. (This code is the code generated by the Script option in the Execute Package dialog box in SSMS.)
Update the parameter values in the catalog.create_execution stored procedure for your system.
Make sure that SSISDB is the current database.
Run the script.
In Object Explorer, refresh the contents of SSISDB if necessary and check for the project that you deployed.
You can package the assembly into a nuget package, Create a Lib folder inside your solution to hold the nuget package, then, create a nuget.config file to set the package sources to include the Lib folder inside your solution.
The following links contains more details about creating nuget package and hosting it locally:
In the nuget package explorer add a lib folder, inside that add a .net folder dnxcore50 and add the above DLLs. Click on tools analyse package and save the nuget.
After which you will be able to add the nuget package using nuget package manager and select local package as source
This started today. The edit script stopped working in VS 2017. Here is the work around;
The issue may occur because the Script Task is not installed properly.
VS 2017 solution
Here are two options that might help;
Option # 1
Open control panel and try to repair “Microsoft Visual Studio Tools for Applications 2017”.
This options has helped me after upgrading VS SSDT 2017 recently. The funny things is that you don’t need to repair “Microsoft SQL Server Data Tools for Visual Studio 2017 (SSDT). Anyway it worked.
Option # 2
Please check whether the Microsoft.SqlServer.ScriptTask.dll file exists in the following folders (supposing it is a 64-bit platform):
If the Microsoft.SqlServer.ScriptTask.dll file is missing in any of the folders, copy one (from the other folders or a different working SSIS server) to the folder. Alternatively, you can also use the command line tool gacutil.exe to install the task assembly into the global assembly cache (GAC)
If above work around does not work, then do this;
Uninstall SSDT from your machine
Reboot the machine so that no cache issue
Reinstall SSDT with the option Install new SQL Server Data Tools for Visual Studio 2017 instance (SSDT), like below. Do not install in your existing Visual Studio instance again.