SSIS Introduction

Introduction

Microsoft® SQL Server™ Integration Services (SSIS) has gone through a complete redesign and rewrite from Data Transformation Services (DTS) in SQL Server 2000. As part of that process, many of the product design and administration paradigms were rethought. SSIS is now no longer a stand-alone designer; SSIS adopts Microsoft Visual Studio® as its development environment, but continues to leverage the standard management tool for SQL Server 2005. The new development environment is called the Business Intelligence (BI) Development Studio, while the administration environment is aptly named SQL Server Management Studio. The separation of the two environments allows developers and database administrators (DBAs) to focus on specific tasks of development or administration.

Both environments are capable of executing packages, but the BI Development Studio can only execute on the developer console, while the Management Studio executes those packages on the server that have been imported. BI Development Studio solutions contain one or more projects, while projects contain data sources, data source views, SSIS packages, and other miscellaneous files. For example, a new project can contain the SSIS package you’re developing and also any packages that you may be calling out to. It also might contain all of your supporting files such as data definition language (DDL) and data manipulation language (DML) files for databases you’re working with. When it comes time to deploy to a production environment, you’ve got all the necessary files in one location.

BI Development Studio doesn’t require a direct connection to the SQL Server RDBMS to design packages, nor does it require a connection to save your work. BI Development Studio saves projects into project folders, just like Visual Studio does. BI Development Studio can also have direct integration to Visual SourceSafe (and other source control systems) from the design environment. As changes are made to projects, they can be immediately checked into VSS.

Management Studio, unlike BI Development Studio, is aimed primarily at DBAs and is used to manage SQL, Analysis Services, and Reporting Services servers. It supports the executing and scheduling of SSIS packages, but not the editing or designing of them. However, it does allow users to use the SSIS framework to import and export data and to perform routine maintenance tasks.