The SSIS Integrated Development Environment - SQL Server 2008

If you’ve worked with Visual Studio, you’ll be familiar with the layout of the SSIS integrated development enviroment (IDE), which, like SQL Server development for Reporting Services and AnalysisServices, exists within Business Intelligence Development Studio (BIDS). In fact, BIDS is just a called downversion of Visual Studio, and therefore employs the Visual Studio concepts of projects and solutions. (A project is a container that groups related files and can contain one or more packages, and a solution is a container that groups and manages projects used to develop business solutions.)

Another advantage of developing within BIDS is that developers are typically much more comfortable with this environment and therefore much more productive. Any investment in application lifecycle management (ALM) technologies, such as Visual Studio Team Foundation Server, can now be leveraged not only for .NET application development, but also for SQL Server development. The source files for SSIS packages are all XML, so versioning within your source control system is possible.

Within this environment, development is performed in BIDS, and the management of SSIS packages becomes the realm of the SSIS command-line utility, SQL Server Agent jobs, and SQL Server Management Studio (the Integration Services node). Management Studio allows you to import packages to and export packages from the msdb database and file system; start, stop, and monitor both local and remote packages; and change package configuration settings.

Connecting to SSIS in Management Studio

After you have installed SSIS (typically, during the installation of SQL Server 2008) and started the service, you can connect to it from Management Studio. You can manage SSIS settings and its runningstatus within the SQL Server Configuration Manager under SQL Server Services. With the service running, here are the steps to connect from within Management Studio:

  1. In Object Explorer, click the Connect button and select Integration Services.
  2. In the Connect to Server dialog box, type in the name of the server where the Integration Services service is located (the machine name). SSIS doesn’t support the notion of named instances as does the SQL Server database engine.
  3. The Integration Services node will now appear in Object Explorer. Expand the Running Packages folder. Unless SSIS packages are currently running, you won’t see any packages.
  4. Expand the Stored Packages folder. Notice the File System folder and MSDB folders, as shown in Figure. By right-clicking either of these folders, you can create subdirectories for storage of your packages, and execute, rename, or perform other package operations. We’ll review how you create and edit packages in the next section.

The Integration Services node in Object Explorer

The Integration Services node in Object Explorer

Notably missing from Management Studio functionality is the ability to create, modify, debug, test, and deploy an SSIS package. For these tasks, you must use BIDS.

Creating a New SSIS Project in BIDS

To create a new SSIS project, open BIDS and select File ➤New Project. In the New Project dialog box, under Project types, select Business Intelligence Projects, and under Templates, select Integration Services Project. In the Name field, type a name for your project—MyFirst SSIS Project for the examples in this chapter. Leave the check box for Create Directory for Solution checked, and click OK. You’ll notice that there are many windows and design surfaces in BIDS. In the next section, we’ll review each of these design surfaces and windows, describing what they are and how they are used.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics