Management Studio

In SSIS, there’s delineation between development and administration. This makes the development model much more like developing a regular program. In a web application, you would never make a code change to the C# application on the production server. Instead, if you wanted to make a change, you would check it out of Source Control, make the change, and then redeploy. The same C# model applies to SSIS.

When you open Management Studio, select the Connect drop-down box in the Object Explorer window and select Integration Services. When you connect, you will see all the different stores that are available for you to explore.

A connection to that store isn’t made until you expand one of the folders, as shown with the File System store in Figure 9-2. At that point, you may experience a timeout if you’re trying to connect to an msdb database that isn’t online, or when the server is offline. Otherwise, when you expand the folder, you will see a list of folders and packages that are stored in that particular store.

Figure 9-2: File System store

You can also access all the packages that are running under the Running Packages folder. From there, you can stop packages that are running too long by right-clicking the package and selecting Stop. You can also right-click the folder and select Reports General to see a report of all the packages running and for how long. This method of accessing the Reports menu is only available in SQL Server 2005 Service Pack 2. Previously, you’d have to access the reports through the Reports drop-down box in the right Summary pane.

In Figure 9-3, you can see that there are two instantiations of the FileWatcher package. Both were executed by the bknight account and the Execution Duration is the number of milliseconds since the start time.

Figure 9-3: Two instantiations of the FileWatcher package

The package name shown in this report is the package name from the package properties in BIDS. If your package’s file name is Package1.dtsx, but the package name in properties is called FileWatcher, then FileWatcher will show in this window, not Package1.dtsx. Because of this, it’s very important to keep your package’s name in synch with the file. Otherwise, it can become quite complex to have to find and manage your packages.

You can right-click any item or folder in the tree to produce item-specific reports. At a package level, you can see all the details for the package that the designer has exposed in BIDS. At the folder level, you can see all the packages in the folder and their build numbers.