What is DTS/SSIS in SQL Server?

Question

What are DTS/SSIS in SQL Server? Do I need to separately install these services?

What is the use of this DTS/SSIS in SQL Server? How to configure DTS/SSIS?

Help me in this regard please

Thanks in advance.

Answer 1

first explain about the services Ms Sql and SSDT, then will explain about the ssis

To work with SSIS,SSAS, the desired service (ssis: SQL Server Integration Services and SSAS:SQL Server Analysis Services) must be installed and SSDT Tools

services

MS SQL Server has two imporent services which is mandatory for databases creation and maintenance. Other services available for different purposes .

• Database Engine(default services used to store, manage and to access the data from the Database.)

• SQL Server Agent(uses SQL Server to store job information.)More information

Other Services

• SQL Server Integration Services(a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.) More information

• SQL Server Analysis Services(an analytical data engine used in decision support and business analytics) • Etc(example SQL Server Reporting Services, SQL Server Browser,SQL Server Full Text Search)

SSDT

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, databases in Azure SQL, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reportsMore Information

SSIS

stands for SQL Server Integration Services is a tool that we use to perform ETL operations

ETL (Extract, Transform, and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse.

The main uses of SSIS are,

1.Warehouses and Data Marts Cleaning and Standardizing Data Building

2.Merging Data from Multi source (sql,text,oracle,web Api,....)

3.Helps you to clean and standardize data

4.Identifying, capturing, and processing data changes

5.Building BI into a Data Transformation Process

6.Administrative Functions and Data Loading

7.etc

Better Site For Learn SSIS

Answer 2

DTS was the original (and quite buggy) ETL product. It was rebranded SSIS with one of the later releases of SQL Server.

Answer 3

On SSIS, take a look at this related question. I have posted useful links for SSIS on this question: What is the SSIS package and what does it do?

Answer 4

SSIS = SQL Server Integration Services. From Microsoft:

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations. Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

http://msdn.microsoft.com/en-us/library/ms141026.aspx

It's actually really cool stuff. Oracle has a pretty cool business intelligence editor for their dbms as well.