Question - Do we need a SSIS windows service to execute .dtsx file?Here is the scenario. We have a .dtsx package file and need to execute it. Should we need to have SQL Server Integration Services running in our machine to execute that package?
Answer - NoLets look at what is SQL Server Integration Services. By definition
"The SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages"
From Microsoft KB https://support.microsoft.com/en-us/kb/942176 & MSDN Page
It clearly says we don't need need SSIS running in our machine to execute a package. We simply need the DTExec.exe utility to run .dtsx packages. So what is the use of SSIS windows service?
It is just to keep the .dtsx packages safe, initiate, monitor and stop the execution. It really means that there can be different users and roles who can only execute packages. Also helps to control the packages in remote machine.
Question - Do we need SQL Server running to run integration services package(.dtsx)
The SSIS is related with SQL Server. So do we need to have any SQL Server instance running, if we want to run a .dtsx package?
Answer - No
Though the Integration Services is for data transformation, it is not developed with tight integration with SQL Server. We can develop any algorithm using integration services. As seen above DTExec.exe is the execution utility. It doesn't need any SQL Server instance running to execute dtsx package is the package is not establishing connection with SQL Server.
What is the confusion?
Why people often confuse .dtsx package with SQL Server? The reason is mainly everybody thinks that since Integration Services are for ETL and data related operations, it must be linked with SQL Server and SSIS. Another reason is the expansion of SSIS starts with SQL Server. The better way is to start calling it as simply "Integration Services". As of my understanding even MSFT has the confusion. Some places they use simply Integration Services, some places Microsoft Integration Services and sometime refer full SQL Server Integration Services. So we cannot blame anybody for getting confused :)
So lets consider this as another technology to achieve workflow execution platform recommended for building enterprise-level data integration and data transformations solutions. This technology has many tools and components to help us in various phases of development and deployment.
BIDS - Business Intelligence Development Studio
The tool which is used to create the workflow using drag and drop mechanism. It is Visual Studio itself. It gives option to define parameters, variables, connections, log provider and edit the script tasks. It captures the workflow steps in the form of xml and saved as simple file with extension .dtsx.
The file extension of file which contains the workflow in xml. This format is open and XSD can be found in MSDN. This file can be imported to SSIS package store to have better management. Integration Services .(dtsx) files can be created either from BIDS or using our own program.
Utility used to to execute any .dtsx package either stored in file system or SSIS package storage. Parameters are passed via command line. Location
[Drive]:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe
GUI utility to run .dtsx packages. Provides UI to enter the package parameters. Located at
[Drive]:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\DTExecUI.exe
A windows service used to store and track execution of Integration Service packages (.dtsx)
This is the .Net managed dll which provides API to create and execute .dtsx packages. This contains the namespace Microsoft.SqlServer.Dts.Runtime which holds the relevant classes. Located in below location if 2012 is installed
[Drive]:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll