تراکنش‌ها در SQL Server Integration Service

مقدمه

همانطور که می‌دانید تراکنش ها مجموعه ای از واحدهای کاری (Units Of Work) هستند که داده‌ها در آن‌ها دستخوش تغییر قرار می‌گیرند. SQL Server از این مفهوم جهت اعمال یکپارچگی (Consistency) و پایداری (durability) داده‌ها استفاده می‌کند. طبق ویژگی Atomicity تراکنش، یا تمامی تغییرات درون تراکنش همگی اعمال می‌شوند و تراکنش Commit می‌شود، یا هیچکدام از تغییرات انجام نمی‌شوند و تراکنش Rollback می‌شود.
در SQL Server Integration Services (SSIS) نیز همانند SQL Server جهت حفظ جامعیت داده‌ها از مفهوم تراکنش‌ها استفاده شده است. بنابراین پکیج‌ها جهت اتمیک کردن دستوراتی که توسط Task ها انجام می‌شوند از مفهوم تراکنش استفاده می‌کنند.
SSIS با استفاده از Containerها امکان تعریف تراکنش‌ها را فراهم کرده است. Packageها در بالاترین سطح و Control Flow Taskها در پایین ترین سطح Containerها قرار دارند، همچنین از دیگر انواع Containerها می‌توان به For Loop، Foreach Loop و Sequence containerها اشاره کرد.

پیش نیازها

کار کردن با تراکنش ها در SSIS چالش‌های خاص خود را دارد. فارغ از آنکه تراکنش از نوع Distributed است یا نه، بدلیل آنکه پشتیبانی از تراکنش ها در SSIS متکی بر سرویس ویندوز Microsoft Distributed Transaction Coordinator (MSDTC) می‌باشد باید نکات زیر را در زمان استفاده از تراکنش‌ها در SSIS مد نظر داشت:

• تمامی سیستم‌های درگیر باید از تراکنش‌های DTC پشتیبانی کنند
• سرویس DTC در تمامی طرفین (سرورها) باید در حال اجرا باشد

در غیر این صورت در حین اجرای پکیج با خطای زیر مواجه خواهید شد

“Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B The Transaction Manager is not available. The DTS transaction filed to start. This could occur because the MSDTC Service is not running”

انواع تراکنش‌ها در SSIS

پس از انتخاب Container (از جمله خود Package)، در پنجره Properties، میتوان نوع تراکنش را با تغییر ویژگی TransactionOption بر روی یکی از سه حالت زیر تنظیم نمود:

• Required در این حالت Container یک تراکنش جدید ایجاد خواهد کرد مگر آنکه Container والد (Parent Container) از قبل تراکنشی را ایجاد کرده باشد که در این صورت این Container در همان تراکنش بالایی اجرای خواهد شد و تراکنش جدیدی ایجاد نمی‌شود.
• Supported در این حالت Container تراکنش جدیدی ایجاد نخواهد کرد اما اگر Container والد (Parent Container) تراکنشی را ایجاد کرده باشد به آن ملحق خواهد شد.
• Not Supported در این حالت، نه تنها container تراکنشی ایجاد نمی‌کند بلکه در تراکنش Container والد نیز (در صورت وجود) شرکت نخواهد کرد. در چنین شرایطی بروز خطا در تراکنش باعث Rollback شدن این Container نخواهد شد.

حال با یک مثال کاربردی موارد ذکر شده را توضیح خواهیم داد:
تصور کنید که شما یک جدول بسیار حجیم جهت ذخیره سفارشات مشتریان دارید که حاوی اطلاعات فروش چندین سال می باشد و قصد دارید اطلاعات قدیمی این جدول را آرشیو نمایید. می خواهیم برای این منظور پکیجی طراحی کنیم و تاثیر انواع TransactionOptionهای مختلف را روی آن بررسی کنیم.
در طراحی این پکیج از دو جدول SalesOrder حاوی اطلاعات فروش و جدول SalesOrder_Archive با ساختار مشابه جهت آرشیو اطلاعات قدیمی استفاده کرده‌ایم. همچنین از دو جدول به نام‌های ArchiveConfig و PackageExecutionLog به ترتیب برای نگهداری آخرین دیتای آرشیو شده و ثبت لاگ اجرای پکیج استفاده خواهیم کرد.

طراحی پکیج

۱- ابتدا جداول ذکر شده را ایجاد می‌کنیم

--Create sample database
CREATE DATABASE [SSIS_Transactions]
GO
USE [SSIS_Transactions]
GO
--Create operatonal table
CREATE TABLE [dbo].[SalesOrder]
(
  SaleOrderId INT IDENTITY(1,1) PRIMARY KEY,
  CustomerId INT NOT NULL,
  SalesPersonId INT NOT NULL,
  OrderDate DATE NOT NULL,
  TaxAmt MONEY NOT NULL,
  TotalDue MONEY NOT NULL
)
CREATE NONCLUSTERED INDEX NCIX_OrderDate ON [dbo].[SalesOrder] ([OrderDate],[CustomerId])
GO

--Create archiving table with same structure as operational table, except IDENTITY option
CREATE TABLE [dbo].[SalesOrder_Archive]
(
  SaleOrderId INT PRIMARY KEY,
  CustomerId INT NOT NULL,
  SalesPersonId INT NOT NULL,
  OrderDate DATE NOT NULL,
  TaxAmt MONEY NOT NULL,
  TotalDue MONEY NOT NULL
)
GO

--Create config table to store last archiving date of each database table(s)
CREATE TABLE [dbo].[ArchiveConfig]
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  TableName VARCHAR(100),
  LastArchiveOrderDate DATE
)
GO
--Create log table for storing history of archiving package execution
CREATE TABLE [dbo].[PackageExecutionLog]
(
  Id INT IDENTITY(1,1) PRIMARY KEY,
  PackageName VARCHAR(100) NOT NULL,
  ExecutionTime DATETIME NOT NULL
)
GO

۲. در مرحله بعد از یک Execute SQL Task که نام آن را “Get Last Archived Date From Config Table” گذاشته ایم، برای بدست آوردن تاریخ مورد نظر جهت آرشیو استفاده می کنیم و مقدار برگردانده شده از این کنترل را در متغیری به نام ArchiveDate ذخیره می‌کنیم.

DECLARE	@LastArchivedDate	DATETIME = '2018-01-01'
SELECT	@LastArchivedDate = LastArchivedOrderDate
  FROM	dbo.ArchiveConfig
  WHERE	TableName = 'SalesOrder'
SELECT	DATEADD(DAY، ۱، @LastArchivedDate) ArchivedDate

۳-سپس از یک کنترل Data Flow Task با نام “Insert Data Into Archive Table” جهت خواندن اطلاعات فروش از جدول SalesOrder و انتقال آن به جدول آرشیو مقصد با نام SalesOrder_Archive استفاده می کنیم. از کد زیر جهت بدست آوردن اطلاعات جدول SalesOrder استفاده می‌شود.

SELECT
  SalesOrderID
  ، CustomerID
  ، SalesPersonID
  ، OrderDate
  ، TaxAmt
  ، TotalDue
FROM	dbo.SalesOrder
WHERE	OrderDate = ?

۴. در مرحله بعد با استفاده از یک کنترل Execute SQL Task دیگر با نام “Delete Archived Data From Source Table” اطلاعات آرشیو شده را از جدول مبدا حذف خواهیم کرد.

DELETE
  FROM	dbo.SalesOrder
  WHERE	OrderDate = ?

۵- سپس مقدار فیلد LastArchivedOrderDate در جدول ArchiveConfig را با استفاده از متغیر ArchiveDate بروزرسانی می‌کنیم. برای این کار از یک کنترل Execute SQL Task به نام “Update Config Table” استفاده می‌کنیم

UPDATE	dbo.ArchiveConfig
  SET	LastArchivedOrderDate = ?
  WHERE	TableName = 'SalesOrder'

۶- و در نهایت به ازای هر اجرای پکیج یک رکورد در جدول PackageExecutionLog درج می‌کنیم. برای این کار نیز از یک کنترل Execute SQL Task دیگر با نام “Log Package Execution” استفاده می‌کنیم.

INSERT	INTO	dbo.PackageExecutionLog	(PackageName، ExecutionTime)
  VALUES ('SalesOrder'، GETDATE())

تنظیم تراکنش‌های متفاوت در پکیج SSIS

سناریوی اول

• تنظیم Single Transaction به ازای Package
  در این مثال، پکیج خود شروع کننده تراکنش خواهد بود و ویژگی TransactionOption آن به مقدار Required تنظیم می‌شودهمچنین ویژگی TransactionOption سایر Task ها را نیز به مقدار Supported تنظیم خواهیم کرد سپس برای آنکه تاثیر خطا را بر اجرای پکیج متوجه شویم دستور Insert داخل Log Package Execution را تغییر میدهیم. این دستور به دلیل Null بودن مقدار فیلد ExecutionTime سبب بروز خطا خواهد شداجرای دستور زیر نشان می دهد که با اجرای پکیج باید سه رکورد به جدول SalesOrder_Archive انتقال یابدحال اگر پکیج اجرا شود با خطا مواجه شده و به دلیل آنکه Log Package Execution Task بخشی از تراکنش می‌باشد تمامی عملیات Rollback خواهد شداجرای مجدد دستور زیر این موضوع را تایید خواهد کرد.

سناریوی دوم

• تنظیم Single Transaction به ازای Container
  این بار در این مثال، قصد داریم که Task هایی را که عملیات اصلی آرشیو کردن را انجام می دهند و fail شدن آنها باید باعث لغو عملیات شود را داخل یک Sequence Container قرار داده و تراکنش را به ازای این container فعال کنیم.

به همین جهت ویژگی‌های TransactionOption را به صورت زیر تنظیم می‌کنیم.

• Package —-> Supported
• Sequence Container —-> Required
• Get Last Archived Date From Config Table —-> Supported
• Log Package Execution —-> Supported

هر چند با اجرای پکیج با خطا مواجه خواهیم شد اما تراکنش اصلی با موفقیت انجام می‌شود و خطای رخ داده تاثیری روی عملیات اصلی نخواهد داشت.اجرای دستور زیر این مطلب را تایید خواهد کردحال اگر دستور داخل Update Config Table Task را تغییر دهیم و مقدار فیلد ID را بروزرسانی کنیم این تسک (Task) با خطا مواجه خواهد شدو اکنون این خطا سبب Rollback شدن تراکنش خواهد شد

سناریوی سوم

حال برای آنکه تاثیر تراکنش روی Task و یا Containerهای NotSupported را بررسی کنیم به Sequence Container در پکیج مرحله قبل یک Execute SQL Task به نام “Create Temporary Table Temp_SalesOrder” صرفا جهت ایجاد یک جدول موقت اضافه می‌کنیم و ویژگی TransactionOption آن را به NotSupported تنظیم می‌کنیم.

سپس برای آنکه اطمینان حاصل کنیم که این جدول از قبل وجود نداشته باشد ابتدا دستور حذف و سپس ایجاد آن را اضافه می‌کنیم

DROP	TABLE	IF	EXISTS	Temp_SalesOrder
GO
CREATE	TABLE	Temp_SalesOrder
(
  SalesOrderID int NOT NULL،
  CustomerID int NOT NULL،
  SalesPersonID int NOT NULL،
  OrderDate date NOT NULL،
  TaxAmt money NOT NULL،
  TotalDue money NOT NULL،

جهت یادآوری، “Update Config Table” با بروزرسانی فیلد ID سبب بروز خطا خواهد شد.
حال پکیج را اجرا خواهیم کردهمانطور که مشاهده می‌کنید اجرای پکیج با خطا مواجه شده و تراکنش Rollback گردید.
اجرای دستور زیر این موضوع را تایید خواهد کرداما Rollback شدن تراکنش تاثیری بر “Create Temporary Table Temp_SalesOrder” Task نداشته و این جدول هنوز وجود دارد. اجرای مجدد دستور ایجاد جدول این مورد را ثابت خواهد کرد

سناریوی چهارم: تراکنش‌های نامرتبط در یک پکیج

بی دقتی در تنظیم ویژگی TransactionOption مربوط به Containerهای تودرتو می‌تواند منجر به شرایطی شود که چندین تراکنش نامرتبط در یک پکیج ایجاد شود. زمانی که یکی از containerهای داخلی در یک سلسله مراتب از container ها از تراکنش پشتیبانی نکند، به ازای هر یک از container های بالایی و پایینی آن در سلسله مراتب که دارای ویژگی Supported باشند یک تراکنش جداگانه ایجاد خواهد شد. commit و یا rollback شدن این تراکنش ها از داخلی ترین تراکنش شروع خواهد شد و rollback شدن یک تراکنش بیرونی تاثیری بر تراکنشِ داخلیِ commit شده نخواهد داشت
مثال زیر را در نظر بگیرید

Package —-> Required

Outermost Sequence Container —-> Supported

First & Second Sequence Container —-> NotSupported

Insert First & Second Record Into SalesOrder —-> Supported

Insert Third & Fourth Record Into SalesOrder —-> Supported

در این مثال به ازای این پکیج پنج تراکنش ایجاد می‌شود. یک تراکنش توسط پکیج و چهار تراکنش دیگر هر کدام به ازای هر یک از Execute SQL Taskها ایجاد خواهد شد.
حال تصور کنید که اجرای دومین و چهارمین Execute SQL Task با خطا مواجه شود. تصویر زیر اجرای پکیج را نشان می دهدهمانطور که توضیح دادیم، Rollback شدن Outmost Sequence Container Task تاثیری بر دو تراکنش commit شدۀ “Insert First Record Into SalesOrder” و “Insert Third Record Into SalesOrder” نخواهد گذاشت