From the course: SQL Server Integration Services

What is SSIS? - SQL Server Tutorial

From the course: SQL Server Integration Services

Start my 1-month free trial

What is SSIS?

“

- [Instructor] It makes sense to start any course with an understanding of what exactly is about to be covered. So let's begin by getting to know what SQL Server Integration Services is. Integration Services, or SSIS, is a platform for building workflows that can automate and standardize complex data related tasks. As the name would imply, it can help integrate data from a variety of sources including other SQL databases, Excel spreadsheets, FAT files, such as CSV and XML files, and pull it all together into a single destination database. It does this through a process called ETL, or extract, transform, and load. The extract process fetches data and copies it from a source location, through transforms you manipulate that data, filter it, cleanse it, standardize its formatting, and perform other organizational tasks to prepare it for the final stage, loading it into a destination database. SSIS can also perform additional tasks beyond those covered in an ETL process. You can automate downloading files from an FTP server, email status reports, or create a backup of a database before and after a load. There's a long list of different tasks that you can add to an Integration Services package. To do this you'll need to work in two different environments. In order to develop a package of tasks, you'll use a graphical designer called SQL Server Data Tools, which is a component of Microsoft's flagship application development program, Visual Studio. Here you'll specify what tasks you want to run, connect them to the appropriate data sources and destinations, and define the sequence in which they'll execute. At the end of the design phase you'll have a flowchart-like diagram that describes how data moves into the system, what happens to it, how it exits, and all of the additional actions that are triggered along the way. The other environment that you'll work in is SQL Server Management Studio. If you've worked with SQL Server databases before, then you're almost certainly familiar with this program. In SSMS, you'll manage the Integration Services database that stores the packages that you've developed in Visual Studio. From here, you'll set schedules and other triggering events that'll run the packages and execute the tasks that you've put together in your production databases. In order to get all of this to work, you first need to install the Integration Services service as a shared feature of SQL Server. The service will run in the background just like SQL Server and allow you to connect to multiple data sources and destinations and store your developed task packages in a secure, access controlled environment.

Contents