Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 31, 2007

Administering SQL Server 2005 Integration Services - Page 3

By Staff

Scheduling Packages

Packages can be scheduled to run automatically using the SQL Server Agent. The packages need to be stored in the SSIS package store, either the MSDB or the file system, to be scheduled.

In the example, the Patient Status Data package needs to be run every day at 6 a.m. to update the patient status. To schedule a package for execution, follow these steps:

  1. Open SQL Server Management Studio.

  2. Connect to the Database Engine of the SQL Server.

  3. Right-click on SQL Server Agent and select New, Job.

  4. In the General options page, enter the name of the job, in this example Daily Patient Status Update.

  5. Select the Steps option page.

  6. Click New to create a new step.

  7. Enter the Step name, in the example Update Patient Status.

  8. In the Type pull-down, select SQL Server Integration Services Package.

  9. In the Package Source pull-down, select the SSIS package store.

  10. In the Server pull-down, select the server name.

  11. Click on the Package selection button to the right of the Package field.

  12. Browse the Select an SSIS Package window to find the package, in this example the PatientStatusImport package imported earlier.

  13. Click OK.

  14. Click OK to save the step.

  15. Select the Schedules option page.

  16. Click New to create a new job schedule.

  17. In the Name field, enter Daily at 6am.

  18. In the Occurs pull-down, select Daily.

  19. Change the Daily Frequency to 6:00:00 AM.

  20. Click OK to save the schedule.

  21. Click OK to save the job.

The job will now run the SSIS package at 6 a.m. every day. The job is saved in the database and can be reviewed in the Jobs folder within the SQL Server Agent. You can test it by right-clicking on the job and selecting Start Job.

Jobs can run a series of packages in a sequence of steps and even with conditional branches that depend on the output of the preceding packages. This allows packages to be chained together to complete a larger task.

Managing the Running Packages

You can view the running packages in the SQL Server Management Studio connection to Integration Services. The list of running packages is located under the Running Packages folder.

To generate a report of the running package, right-click on the running package and select General. The report, shown in Figure 5.3, is not very detailed. The main information is in the Execution Started and Executed By fields.

Figure 5.3
Running package.

You also can stop the package from the Integration Services Object Explorer Running Packages window. Simply right-click on the running package and select Stop.

Unfortunately, the management of running packages within the SQL Server Management Studio is limited and the information presented minimal. The tool gives only a general sense of what the packages are doing, rather than the details you might be looking for.

Removing Packages

To remove the packages from the Integration Services package store, follow these steps:

  1. Open SQL Server Management Studio.

  2. Select Integration Services from the Server Type drop-down.

  3. Click Connect.

  4. Expand the Stored Packages folder.

  5. Expand the File System folder.

  6. Right-click the PatientStatusImport package imported earlier and select Delete.

  7. Click on Yes to confirm the deletion of the package.

When a package is deleted, it cannot be retrieved except by restoring from a backup. There is no "undo" function within the tool, so delete packages carefully and only after exporting the packages for safekeeping.

Administering Integration Services

Administering the SQL Server 2005 Integration Services consists of administering the Integration Services service and packages. There are relatively few administration activities directly related to SSIS itself because it is mainly a vehicle for running and storing packages.

Administering the Integration Services Service

The default behavior of the Integration Services service can be modified to suit the needs of the application. Three options can be configured for the Integration Services service:

  • Stopping packages when the service stops

  • Changing the File System folder default location

  • Changing the default root folders to display

The configuration file, named MsDtsSrvr.ini,xml, is located in %Program Files%\ Microsoft SQL Server\ 90\ DTS\ Binn\ . The configuration file is loaded when the service starts.

The default configuration file for the Integration Services service is

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd=
  <Folder xsi:type="SqlServerFolder">
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\ Packages</StorePath>

You can change the service configuration simply by modifying the configuration file and then restarting the service.

For example, if you want to configure the service not to stop packages on shutdown and to add the SQL Server folder (MSDB database) to the Object Explorer view, your modified configuration file would look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd=
  <Folder xsi:type="SqlServerFolder">
  <Folder xsi:type="SqlServerFolder">
  <Folder xsi:type="FileSystemFolder">
   <Name>File System</Name>
   <StorePath>..\ Packages</StorePath>

This example shows how the file would be modified for a SQL Server named SQL01 to be able to explore the packages in the MSDB on SQL02. After you modified the configuration file, there would be an additional root folder SQL02MSDB showing packages stored in the SQL Server Store of SQL02. And packages running on SQL01 would not stop when the service was stopped.

Monitoring Package Execution

Integration Services exposes a number of performance counters that allow the monitoring of package execution in real-time or the capture for historical reference. This shows the number of rows being processed, the memory being used in different capacities, and the impact of certain data types on the package performance. The counters can be used to tune the performance of the package and see the impact of the changes.

A couple of terms used in the counters bear explanation. Flat buffers are blocks of memory that a component uses to store data. A flat buffer is a large block of bytes that is accessed byte by byte. Binary large objects (BLOBs) are data types used in SQL to store large binary data such as images or unstructured text. They can be large objects and can affect the performance of a package, so understanding how much data is from this type is important.

The object is the SQLServer:SSIS Pipeline, and the various counters are listed in Table 5.1.

Table 5.1 Integration Services Package Counters

Performance Counter


Rows read

The number of rows that a source produces. This counter is for the life of the SSIS instance and resets if the service is restarted.

Rows written

The number of rows offered to a destination. This counter is for the life of the SSIS instance and resets if the service is restarted.

Buffer memory

The amount of memory in use.

Buffers in use

The number of buffer objects that all data flow components and the data flow engine are currently using.

Buffers spooled

The number of buffers currently written to the disk.

Private buffer memory

The total amount of memory in use by all private buffers.

Private buffers in use

The number of buffers that transformations use.

BLOB bytes read

The number of bytes of BLOB data that the data flow engine has read from all sources.

BLOB bytes written

The number of bytes of BLOB data that the data flow engine has written to all destinations.

BLOB files in use

The number of BLOB files that the data flow engine currently is using for spooling.

Flat buffer memory

The total amount of memory, in bytes, that all flat buffers use.

Flat buffers in use

The number of flat buffers that the data flow engine uses. All flat buffers are private buffers.

The counters can be used directly from the Performance Microsoft Management Console (MMC) for a real-time view of the package performance or can be used with a monitoring tool such as Microsoft Operations Manager (MOM) 2007.

MS SQL Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM