Difference between SSDT in VS and Integration Services in SQL Server

Question

I'd like to know the difference between SSDT in Visual Studio and Integration Services in SQL Server. When developing SSIS package locally using SSDT in VS I could pass data to my local SQL Server without Integration Services being installed in SQL Server instance. I'm wondering if I need Integration Services to allow between servers communication. Let's say when SQL Server DB is on one server and .dtsx package is on another.

Answer 1

This is where Microsoft's marketing department has run amok. It is important to understand that Microsoft is not a company that writes code. Microsoft is a marketing company that happens to write code.

The simple answer is that SSDT is the package of project templates for what is intended to be Business Intelligence.

SSDT contains three template types:

1. SSIS: Integration Services
2. SSRS: Reporting Services
3. SSAS: Analysis Services

UPDATE

I failed to answer the question of running the packages. Basically, you can run any SSDT package against SQL Server within Visual Studio. However, if you want to deploy the SSDT packages to the SQL Server, then you must have those services installed. The services can be installed via the SQL Server instance installation wizard. You will need to be mindful of another hairy concept that is known as SQL Server Version Targeting: Click Here

For example, if you want to deploy and run SSIS packages to SQL Server, then you will need to install Integration Services (this will include DTExec.exe and ISDeploymentWizard.exe). Now, you will also need to install SSISDB to the SQL Server in order to be able to deploy SSIS packages to the SQL Server - this is performed via SQL Server Management Studio (SSMS). The actual packages are both deployed to, and managed from, a folder called Integration Services Catalog. The packages can then be automatically scheduled to run via the SQL Server Agent. It is extremely unlikely that you will ever work directly with the SSIDB, other than perhaps querying it for information: see here.

See Microsoft's instructions: click here

SSRS packages are managed through a separate Web-UI and I have not dealt with SSAS packages. Isn't this fun!?

A note on DTExec.exe

I have ran into purists who disdain SSIS, largely because they do not understand it. The general argument that I get is that it is slower than PowerShell or Stored Procedures. This may cause someone's head to explode.

Basically, PowerShell runs through the .NET Framework, which is in C# and has to run through a few layers in the OS in order to execute. While SSIS components are written in C#, the DTExec.exe application is written in C++, which can access system resources directly (C# cannot do this because it is managed code!). So, SSIS is going to blow PowerShell and Stored Procedures away in large tasks.

Stored Procedures are a different animal, but still slower because they lack the pipeline buffer (i.e. data flow tab). Another major limitation is how SQL Server executes Stored Procedures and that is sequentially. So, let us imagine that a comparable SSIS job is broken into multiple stored procedures and that those procedures are called by a main stored procedure - a super stored procedure so to speak. SQL Server will execute the stored procedures sequentially, one at a time - this is a huge performance bottleneck. SSIS's pipeline buffer obliterates that by processing a default of 10,000 rows (this is configurable btw) in each task and then passing them off to the next task. So, we can think of data flow tasks as their own stored procedure.

Additional Context

There is a long running confusion with what constitutes SSIS as it relates to Visual Studio, not necessarily SQL Server.

• Pre 2005: It was Data Transformation Services (DTS)
• 2005 & 2008: In 2005, DTS was substantially overhauled and renamed to SSIS late in the development. That is why everything in SSIS still references DTS (i.e., *.dtsx files). It remains the case to this day. Odd, only a masochist likes DTS. BUT! The package of templates was renamed to Business Intelligence Development Studio (BIDS)
• 2012 & 2013: It was renamed to SSDT-BI. Apparently, there was already another product named SSDT
• 2015 and forward: It is now named SSDT

See Microsoft's attempt to explain SSDT: click here

Up through Visual Studio 2017 (VS 2017), SSDT and its various incarnations have been largely treated like the idiot step child to Visual Studio. I say this because VS was installed as a standalone product for these project types only. I don't know why it was done this way - my best guess is because SSDT is free. Anyways, if you wanted to use Visual Studio for other application development then you had to install a separate instance of Visual Studio. So, us developers quite literally have two standalone installations on our dev box and we have to use the specific install for whatever we are doing (i.e., SSDT or non-SSDT development).

Now, with VS 2019, Microsoft is doing away with this model and has finally integrated the SSDT package into the product. Though, the initial roll out of VS 2019 for SSDT was a comedy of errors right out of the box. See my explanation by clicking here. Basically, SSIS does not install with the package and has to be added separately. Though, you still have one instance of VS 2019. Additionally, the SQL11 data provider has been deprecated. And, that too apparently does not come with the installation package either and needs to be installed separately. So, any existing packages that use it will need to be upgraded and re-deployed (see Known Issue #1).

I am holding off on upgrading to VS 2019 for now. VS 2017 has been a pain to say the least. I personally still use VS 2013 Update 5. All VS instances are targeting SQL Server 2014.

Answer 2

Beside of @JWeezy good and detailed answer, i'd like to add a brief explanation:

SQL Server Data Tools for Visual Studio are the development environment for the SQL Server business intelligence suite (SQL Server Integration Services, Reporting Services, Analysis Services).

SQL Server Integration Services (installed from SQL Server installation), install all the files needed to run SSIS packages on the local machine.

Both products can run .dtsx packages but the first one is only for development and testing purposes while the second one is for production server.

References

• Previous releases of SQL Server Data Tools (SSDT and SSDT-BI)
• SQL Server Integration Services