Tag Archives: Microsoft SQL Server 2008 Integration Services

Transaction Support in Integration Services

A transaction is a core concept of relational database systems. It is one of the major mechanisms through which a database server protects the integrity of data, by making sure that the data remains internally consistent. Within a transaction, if any part fails you can have the entire set of operations within the transaction roll back, so that no changes are persisted to the database. SQL Server has always had rich support for transactions, and Integration Services hooks into that support.

A key concept in relational database transactions is the ACID test. To ensure predictable behavior, all transactions must possess the basic ACID test, which means:

  • Atomic: A transaction must work as a unit, which is either fully committed or fully abandoned when complete.
  • Consistent: All data must be in a consistent state when the transaction is complete. All data integrity rules must be enforced and all internal storage mechanisms must be correct when the transaction is complete.
  • Isolated: All transactions must be independent of the data operation of other concurrent transactions. Concurrent transactions can only see data before other operations are complete or after other transactions are complete.
  • Durable: After the transaction is complete, the effects are permanent even in the event of system failure

Integration Services ensures reliable creation, updating, and insertion of rows through the use of ACID transactions. For example, if an error occurs in a package that uses transactions, the transaction rolls back the data that was previously inserted or updated, thereby keeping database integrity. This eliminates orphaned rows and restores updated data to its previous value to ensure that the data remains consistent. No partial success or failure exists when tasks in a package have transactions enabled. They fail or succeed together.

Tasks can use the parent container’s transaction isolation or create their own. The properties that are required to enable transactions are as follows:

  • TransactionOption: Set this property of a task or container to enable transactions. The options are:
    • Required: The task or container enrolls in the transaction of the parent container if one exists; otherwise it creates a new transaction for its own use.
    • Supported: The task uses a parent’s transaction, if one is available. This is the default setting.
    • Not Supported: The task does not support and will not use a transaction even if the parent is using one.
  • IsolationLevel: This property determines the safety level, using the same scheme you can use in a SQL Server stored procedure. The options are:
    • Serializable: The most restrictive isolation level of all. It ensures that if a query is reissued inside the same transaction, existing rows won’t look any different and new rows won’t suddenly appear. It employs a range of locks that prevents edits or insertions until the transaction is completed.
    • Read Committed: Ensures that shared locks are issued when data is being read and prevents “dirty reads.” A dirty read consists of data that is in the process of being edited, but has not been committed or rolled back. However, you can change data before the end of the transaction, resulting in nonrepeatable reads (also known as phantom data).
    • Read Uncommitted: The least restrictive isolation level, which is the opposite of READ COMMITTED, allows “dirty reads” of the data. Ignores locks that other operations may have issued and does not create any locks of its own. This is called “dirty read” because underlying data may change within the transaction and this query would not be aware of it.
    • Snapshot: Reads data as it was when the transaction started, ignoring any changes since then. As a result, it doesn’t represent the current state of the data, but it represents a consistent state of the database as of the beginning of the transaction.
    • Repeatable Read: Prevents others from updating data until a transaction is completed, but does not prevent others from inserting new rows. The inserted rows are known as phantom rows, because they are not visible to a transaction that was started prior to their insertion. This is the minimum level of isolation required to prevent lost updates, which occur when two separate transactions select a row and then update it based on the selected data. The second update would be lost since the criteria for update would no longer match.

Integration Services supports two types of transactions. The first is Distributed Transaction Coordinator (DTC) transactions, which lets you include multiple resources in the transaction. For example, you might have a single transaction that involves data in a SQL Server database, an Oracle database, and an Access database. This type of transaction can span connections, tasks, and packages. The down side is that it requires the DTC service to be running and tends to be very slow.
The other type of transaction is a Native transaction, which uses SQL Server’s built-in support for transactions within its own databases. This uses a single connection to a database and T-SQL commands to manage the transaction. Integration Services supports a great deal of flexibility with transactions.

Integration Services supports a great deal of flexibility with transactions. It supports a variety of scenarios, such as a single transaction within a package, multiple independent transactions in a single package, transactions that span packages, and others. You’ll be hard pressed to find a scenario that you can’t implement with a bit of careful thought using Integration Services transactions.

ldn-expertdkielyThis post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Integration Services course written by expert Don Kiely.

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.

SSRS 2008: Reporting Services and SharePoint

SQL Server 2008 Reporting Services supports two different installation options:

  • Native mode. The Report Server controls all report execution, rendering, and management.
  • SharePoint integrated mode. Report Server runs as part of a SharePoint server farm. SharePoint provides front-end access to content, management, and security. The Report Server takes care of report execution and rendering.

In order to use SharePoint integrated mode, you must have installed Windows SharePoint Services 3.0 or Office SharePoint Server 2007. You also need to configure Report Server for SharePoint integrated mode.

TIP:An alternative to deploying Reporting Services in SharePoint Integrated mode is to deploy Reporting Services in Native mode, but to use the Reporting Services Web parts to find and view reports from SharePoint.

Why Use SharePoint Integrated Mode?

SharePoint Integrated mode makes the most sense when your organization has made a strong investment in SharePoint and you want to leverage that investment when creating, executing, and managing reports.

A number of Reporting Services features are only available when working in SharePoint Integrated mode. When in integrated mode, you can:

  • Manage reports, data sources, and report history using SharePoint.
  • Use the document management and collaboration features of SharePoint with reports.
  • Make use of SharePoint managed authentication and authorization for reports. This includes the use of Forms authentication.
  • Distribute reports outside of a firewall using SharePoint.
  • Deliver reports using a SharePoint delivery extension.
  • Create custom integrations between a SharePoint site and Reporting Services using the ReportingServices Web services API.

A number of Reporting Services features are not supported when the Report Server is configured for Integrated mode. These include:

  • Report Manager
  • My reports
  • Linked reports
  • The rs.exe command-line utility

NOTE While there is much in common across Native and Integrated modes, where there is a difference, this course will cover the Native mode approach.

TIP: See SQL Server 2008 Books Online and your SharePoint documentation for guidance on SharePoint-specific features and interfaces.

paul_LitwinPaul Litwin is a developer specializing in ASP, ASP.NET, Visual Basic, C#, SQL Server, and related technologies. He is an experienced trainer, has written articles for MSDN Magazine and PC World, and is the author of several books including ASP.NET for Developers (SAMS) and Access 2002 Enterprise Developer’s Handbook (SYBEX). Paul is a Microsoft MVP, the chair of the Microsoft ASP.NET Connections conference, and a member of the INETA Speakers Bureau.

SQL Server 2008: The BIDS Interface and Components

SQL Server Integration Services is not the only member of the Microsoft Business Intelligence suite to use BIDS for development. Analysis Services and Reporting Services developers also use BIDS to create their work. Using project templates supplied by Microsoft, Analysis Services developers build cubes, data mining models, and other analytical objects, while report developers build reports and report models in BIDS.

BIDS supports any or all of these project types within the same solution, as
shown in Figure A.

bids1

Figure A. The BIDS Solution Explorer window showing multiple SQL Business Intelligence project types.

TIP: You can create as many Integration Services packages as you want in a single solution. Modularizing a complex ETL workflow into multiple packages makes it easier to develop and debug the individual components. When all the packages are working correctly, you can group them to execute all at once using an Execute Package task.

Creating a new Integration Services project—rather than just opening a package as a standalone file—exposes some additional sections of the interface, such as access to the Data Sources and Data Source Views folders in Solution Explorer, as you can see in the Integration Services Project in Figure A.

Items that you add to a Project are visible in the Solution Explorer window, and can be accessible to any part of the project.

  • Data Sources contain a connection string, and are available in Solution Explorer in the Data Sources folder. They are created and maintained at the project level, outside of any package definition, but can be referenced by a package’s Connection Manager. Connection Managers depend on Data Sources in Integration Services and can be used to share connection definitions among multiple packages in the same solution.

TIP: When you use Data Sources, you can change the data connection strings for multiple packages at once. Adding Connection Managers directly to a package creates local data links, which you must edit per package.

  • Data Source Views are based on Data Sources, but support further filtering of the database schema. By creating package objects based on Data Source Views, you streamline working with lists of database objects.
  • The Miscellaneous folder can contain any support files you need, such
    as documentation, flat files that contain data for import, etc.

You access Integration Services functions in BIDS or Visual Studio through the SSIS menu, the tabbed designer windows, and their related toolboxes:

  • SSIS Menu: Choose options for setting up package logging, configurations, variables, and other options, as shown in Figure B.

bids2

Figure 2. The SSIS menu.

  • Tabbed Designer: Lay out the logic of the package by dragging tasks from the toolbox to the designer to control the overall flow of processing steps. For example, you could download files via FTP before importing them into a database table.
  • Toolboxes: The toolboxes associated with some of the designer tabs let you drag and drop components that perform the many potential actions of a complete Integration Services solution. If you are familiar with Web or Windows forms development in .NET, you’ll be right at home with adding components to a package.

In this course, you will learn about the BIDS tools that enable you to build powerful ETL solutions in Integration Services. The major components of the BIDS interface include:

  • Control Flow designer
  • Data Flow designer
  • Connection Managers
  • Event Handler designer
  • Package Explorer
  • Progress pane


ldn-expertdkiely

This post is an excerpt from the online courseware for our Microsoft SQL Server 2008 Integration Services course written by expert Don Kiely. 

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor and consultant who travels the country sharing his expertise in SQL Server and security.