SSIS Introduction

  • SSIS means Microsoft SQL Server Integration Services.
  • ETL means Extraction, Transformation and Load.
  • SSIS is a software platform to create ETL solutions which include one or many ETL packages.
  • ETL package is a software or a service to run on the server side to process ETL.
  • ETL packages are DTSX files which are saved with extension dtsx.
  • ETL packages can be deployed in SQL Server database SSISDB and managed in Integration Services Catalogues in SQL Server Management Studio (SSMS).
  • ETL packages can be executed in command DTExec.exe or in SQL server.

The following is a typical diagram to demonstrate how to develop, deploy and run SSIS packages.

SSIS Package Diagram
  • Firstly, we'll use development tools to develop an SSIS package.
  • Secondly, the package will be deployed and saved in either a SQL Server database or a folder on the server. The SQL Server must have SSIS service running.
  • Thirdly, we'll use dtexec utility to run the package and generate reports or log files which are used to check if the package runs successfully.
  • At last, in most cases, we'll use scheduler to run the package on a regular basis.

SSIS is a SQL Server service so its version is the same as the SQL Server's. Actually it is packaged with SQL server product. The official development tool is called Business Intelligence Development Studio (BIDS) before SQL server 2012 and SQL Server Data Tools (SSDT) after SQL Server 2012. Visual Studio can also be used to develop SSIS packages after the business intelligence project templates are installed. The following is the database version and its corresponding development tools.

SQL ServerOfficial Development ToolVisual Studio
SQL Server 2005BIDS 2005Visual Studio 2005
SQL Server 2008BIDS 2008Visual Studio 2008
SQL Server 2008 R2BIDS 2008 R2Visual Studio 2008
SQL Server 2012SSDT 2012Visual Studio 2012
SQL Server 2014SSDT 2014Visual Studio 2013

Note Note
Unfortunately, Visual Studio 2010 does not support any BI development.

Mostly the old version packages can be upgraded to new versions by running a wizard when the SSIS project is opened in the development tool of new versions. And it is not supported to deploy a package to a SQL Server with different versions.

In this tutorial, we'll use Visual studio 2012 to demonstrate how to develop SSIS packages. If you have Visual studio 2012 installed, you can download and install the BI templates here to support BI development. If you are using Visual studio 2013 and SQL Server 2014, you can download the templates here.