مبانی Event Handlersدر SSIS Microsoft

مقدمه

کنترل‌کننده‌های رویداد SSIS ساده‌ترین وسیله برای تبدیل یک اسکریپت SSIS به یک سیستم قابل اعتماد است که قابل بازرسی است، به شرایط خطا واکنش مناسب نشان می‌دهد، پیشرفت را گزارش می‌دهد و امکان ابزار دقیق و نظارت بر بسته‌های SSIS شما را فراهم می‌کند. پیاده سازی آنها آسان است و انعطاف پذیری زیادی را ارائه می دهند. راب شلدون یک بار دیگر مقدمه آسان و واضح را ارائه می دهد.

از زمان انتشار SQL Server 2005، SQL Server Integration Services (SSIS) ثابت کرده است که ابزاری موثر برای مدیریت عملیات استخراج، بارگذاری و تبدیل (ETL) است. با این حال، بیشتر مطالبی که در مورد توسعه بسته‌های SSIS پیدا می‌کنید، بر روی data flow و و control flow تمرکز می‌کنند، همانطور که در استودیوی توسعه هوش تجاری (BIDS) ارائه می‌شوند. اما یکی دیگر از ویژگی های مهم و اغلب نادیده گرفته شده در بسته SSIS، کنترل کننده رویداد است.

کنترل‌کننده‌های رویداد به شما این امکان را می‌دهند که اجزای SSIS را بر اساس هر اجرا و بر اساس هر رویداد اجرا کنید. به عنوان مثال، فرض کنید بسته شما شامل یک محفظه حلقه Foreach است. شما می توانید یک یا چند جزء SSIS را با هر رویدادی که توسط آن containerایجاد می شود در هنگام اجرا مرتبط کنید. این شامل رویدادهایی مانند OneError، OnInformation و OnPostExecute می شود. اجزایی که با رویدادهایcontainerمرتبط می‌کنید از جریان control flow جدا هستند. در نتیجه، می‌توانید آن‌ها را به‌طور خاص بر اساس نیازهای کنترل‌کننده رویداد مرتبط پیکربندی کنید. با نشان دادن نحوه پیکربندی یک کنترل کننده رویداد، همه اینها واضح تر می شود.

نکته مهم:

در SSIS، یک فایل اجرایی هر مؤلفه ای است که به جریان control flow اضافه می کنید، به علاوه خود بسته. componentبه عنوان فایل های اجرایی child درpakage عمل می کنند. اگر Componenet را به یک Container اضافه کنید، container ، اجرایی والد (اماChild فایل اجراییpackage)، و Componentدرون Container، اجرایی child است.

در این مقاله نحوه اضافه کردن Event handler به بسته SSIS را توضیح می دهم. بسته در این مورد، داده ها را در دو جدولی که من در پایگاه داده نمونه AdventureWorks2008 در یک نمونه محلی از SQL Server 2008 ایجاد کردم، وارد می کند. اولین جدول، People، پس از استخراج داده ها از جدول Person.Person، هدف بسته خواهد بود. در پایگاه داده AdventureWorks2008. جدول دوم، RunInfo، داده‌هایی را ذخیره می‌کند که توسط کنترل‌کننده‌های رویداد که من به بسته اضافه می‌کنم تولید می‌شود. اسکریپت Transact-SQL زیر شامل دستورات CREATE TABLE است که برای افزودن هر دو جدول ضروری است:

IF OBJECT_ID('People', 'U') IS NOT NULL
DROP TABLE dbo.People;

CREATE TABLE dbo.People
(
PersonID INT NOT NULL,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
CONSTRAINT PK_People PRIMARY KEY CLUSTERED (PersonID ASC)
);

IF OBJECT_ID('RunInfo', 'U') IS NOT NULL
DROP TABLE dbo.RunInfo;

CREATE TABLE dbo.RunInfo
(
RunID INT NOT NULL IDENTITY,
TaskID NVARCHAR(50) NOT NULL,
TaskName NVARCHAR(50) NOT NULL,
TaskTime DATETIME NOT NULL DEFAULT(GETDATE())
CONSTRAINT PK_RunInfo PRIMARY KEY CLUSTERED (RunID ASC)
);

بعد از اینکه دو جدول را به پایگاه داده اضافه کردم، بسته SSIS را ایجاد کردم. شکل ۱ control flow بسته را پس از اضافه کردن اجزای لازم نشان می دهد. همانطور که شکل نشان می دهد، جریان کنترل شامل یک کار SQL Execute برای کوتاه کردن جدول People و شامل یک ظرف Sequence است که شامل دو data flow tasksاست.

تنظیمات control flow را نمایش می دهد

توجه داشته باشید که شکل ۱ – Connection Manager از دیتابیس Adwenture Works 2008 را نشان می دهد، که یک connection manager OLEDBاست که به پایگاه داده AdventureWorks2008 در نمونه محلی SQL Server 2008 متصل می شود. من از این connection manager برای همه اتصالات خود استفاده می کنم.

بعد من دو Data Flow را تنظیم کردم. جریان داده Load Data 1 که در شکل ۲ نشان داده شده است، از یک منبع OLE DB برای بازیابی داده ها از جدول از اسکیمای Person جدول Personو یک مقصد SQL Server برای درج داده ها در جدول People استفاده می کند.

تنظیمات load data1 از Data Flow

زمانی که source OLEDB را تنظیم کردیم از دستور DQL از نوع Select فیلدهای کد پرسنلی و نام و نام خانوادگی از اسکیمای Person جدول Person استخراج کرده به شرطی که رکوردهایی را می خواهیم که کد پرسنلی آن ها کمتر از۱۰۰۰ باشد برای این منظور کوئری زیر را می نویسیم:

SELECT
BusinessEntityID,
FirstName,
LastName
FROM
Person.Person
WHERE
BusinessEntityID < 10000;

توجه داشته باشید که من فقط ردیف هایی را بازیابی می کنم که مقدار BusinessEntityID آنها کمتر از ۱۰۰۰۰ باشد. جریان داده Load Data 2 با Load Data 1 یکسان است، با این تفاوت که از عبارت SELECT زیر استفاده کردم:

برای این منظور کوئری زیر را می نویسیم:

فیلدهای کد پرسنلی و نام و نام خانوادگی از اسکیمای Person جدول Person استخراج کرده به شرطی که رکوردهایی را می خواهیم که کد پرسنلی آن ها بیشتر از ۱۰۰۰ باشد برای این منظور کوئری زیر را می نویسیم.

SELECT
BusinessEntityID,
FirstName,
LastName
FROM
Person.Person
WHERE
BusinessEntityID >= 10000;

همانطور که می بینید، این بار من فقط ردیف هایی را بازیابی می کنم که مقدار BusinessEntityID آنها بزرگتر یا مساوی ۱۰۰۰۰ است. من دو جریان داده را به این ترتیب تنظیم کردم تا ارتباط بین اجرایی و event handlersرا در package ssisبهتر نشان دهم. می توانید بسته تکمیل شده را از حباب گفتار در بالای این مقاله دانلود کنید یا به سادگی می توانید package را خودتان ایجاد کنید. اگر مطمئن نیستید که چگونه یک Package SSIS کنید یا هر یک از این مؤلفه ها را پیکربندی کنید، حتماً SQL Server Books Online را بررسی کنید. هنگامی که Package خود را تنظیم کردید، آماده هستید تا event handlers را اضافه کنید.

انتخاب یک اجرا و رویداد

کنترل‌کننده‌های رویداد خود را در تب Event Handlers در SSIS Designer پیکربندی می‌کنید. برگه نشان داده شده در شکل ۳، دسترسی به فایل های اجرایی بسته و رویدادهای مرتبط با هر فایل اجرایی را فراهم می کند. این برگه همچنین سطح طراحی لازم برای افزودن مؤلفه‌ها به کنترل‌کننده رویداد را فراهم می‌کند، درست همانطور که اجزایی را به جریان کنترل اضافه می‌کنید.

 دسترسی به تنظیمات Event Handler

هنگامی که برای اولین بار به تب Event Handlers دسترسی پیدا می کنید، فایل اجرایی انتخاب شده خود بسته است که در این مورد، من آن را EventHandlersPkg نامیده ام. علاوه بر این، رویداد انتخاب شده OneError است. در نتیجه، هر مؤلفه ای که در این مرحله به سطح طراحی اضافه می کنید، مختص این ترکیب از این جفت اجرایی و رویداد خواهد بود. برای مشاهده تمامی فایل های اجرایی، روی فلش رو به پایین در کادر متن Executable کلیک کنید و سپس لیست فایل های اجرایی را مانند شکل ۴ گسترش دهید.

مشاهده فایل های اجرایی package

توجه داشته باشید که فایل های اجرایی به صورت سلسله مراتبی فهرست شده اند، با EventHandlersPkg در بالای سلسله مراتب و اجرای وظیفه SQL (جدول Truncate People) و ظرف Sequence (Load People data) در سطح دوم سلسله مراتب. در سطح سوم، زیر ظرف Sequence، دو وظیفه data flow (Load Data 1 و Load Data 2) قرار دارند. برای هر فایل اجرایی، پوشه ای به نام Event Handlers فهرست شده است. هر event handelerکه برای یک فایل اجرایی پیکربندی می‌کنید در آن پوشه فهرست می‌شود و کنترل‌کننده‌های رویداد بر اساس رویداد مرتب شده‌اند.

نکته مهم:

منابع، تبدیل‌ها و مقصدهایی که به یک جریان داده اضافه می‌کنید، قابل اجرا نیستند. همه آنها بخشی از فایل اجرایی Data Flow هستند، به همین دلیل است که این مؤلفه ها در لیست فایل های اجرایی در تب Event Handlers قرار داده نشده اند

برای هر فایل اجرایی، لیستی از رویدادها را در لیست مدیریت رویداد پیدا خواهید کرد. لیست شامل تمام رویدادهای مرتبط با فایل اجرایی انتخاب شده است. برای انتخاب یک رویداد برای یک فایل اجرایی، روی فلش رو به پایین در لیست کنترل رویداد که در شکل ۵ نشان داده شده است کلیک کنید و سپس رویداد را انتخاب کنید.

نمایی از Event های Package

هنگام کار بر روی سطح طراحی تب Event Handlers، همیشه با یک جفت رویداد اجرایی خاص کار می کنید. این بدان معناست که برای پیکربندی یک کنترلر رویداد، ابتدا باید یک فایل اجرایی و سپس رویداد را انتخاب کنید. به عنوان مثال، اگر به شکل ۶ مراجعه کنید، خواهید دید که من جدول اجرایی Truncate People را انتخاب کرده ام و سپس رویداد OnError را انتخاب کرده ام. در نتیجه، هر کامپوننتی که به این جفت رویداد اجرایی اضافه کنم، هر زمان که جدول اجرایی Truncate People یک رویداد OnError ایجاد کند، اجرا خواهد شد.

تنظیمات Event Handler

اگر به شکل ۶ مراجعه کنید، متوجه خواهید شد که سطح طراحی شامل پیوندی است که به شما دستور می دهد روی آن کلیک کنید تا یک کنترل کننده رویداد برای آن رویداد خاص و قابل اجرا ایجاد کنید. برای هر جفت رویداد اجرایی که کنترل کننده رویداد برای آن پیکربندی نشده است، این پیوند به شما نشان داده می شود. برای ایجاد مدیریت رویداد و افزودن هر مؤلفه، باید روی این پیوند کلیک کنید.

نکته مهم:

هنگامی که روی پیوند روی سطح طراحی برای یک جفت رویداد اجرایی خاص کلیک کردید، حتی اگر مؤلفه ای اضافه نکنید، یک کنترل کننده رویداد ایجاد می شود. برای حذف یک کنترل کننده رویداد برای یک جفت اجرایی-رویداد خاص، روی دکمه حذف در سمت راست کادر نوشتاری کنترل کننده رویداد کلیک کنید.

اولین کنترل کننده رویدادی که ایجاد می کنیم برای فایل اجرایی Load Data 1 و رویداد OnPostExecute است. پس از انتخاب جفت رویداد اجرایی و کلیک روی پیوند، آماده حرکت هستید. همانطور که در شکل ۷ نشان داده شده است، برای بسته EventHandlersPkg، یک Execute SQL را اضافه کردم.

 اضافه کردن task اجرایی SQL در Event handeler

هنگامی که یک رویداد کنترل کننده را پیکربندی می کنید، می توانید از هر یک از متغیرهای سیستم یا کاربر موجود برای فایل اجرایی استفاده کنید، بنابراین بیایید به متغیرهای موجود برای فایل اجرایی Load Data 1 نگاه کنیم. برای مشاهده این متغیرها، با کلیک بر روی متغیرها در منوی SSIS، پنجره Variables را در SSIS Designer باز کنید. سپس، در پنجره Variables، روی Show System Variables کلیک کنید تا تمام متغیرهای موجود برای اجزای رویداد handler شما فهرست شود، همانطور که در شکل ۸ نشان داده شده است.

مشاهده متغیرها در variable pane

در این مورد، می‌خواهیم از متغیرهای سیستم SourceID و SourceName برای شناسایی مؤلفه‌هایی که رویدادهای OnPostExecute را ایجاد می‌کنند، استفاده کنیم، این همان چیزی است که به جدول RunInfo وارد می‌کنیم. ما متغیرها را در کار Execute SQL اضافه می کنیم، بنابراین بیایید به نحوه پیکربندی آن کار نگاه کنیم. شکل ۹ صفحه عمومی ویرایشگر Execute SQL Task را نشان می دهد.

 پنجره Execute SQL Task Editor

نکته مهم: توجه داشته باشید که در Connection Manager در دیتابیس AdventureWorks2008 را در ویژگی Connection مشخص کرده ام. سپس، در ویژگی SQLStatement، عبارت INSERT زیر را اضافه کردم:

حال در ادامه با استفاده از دستور insert into-values به جدول Run info به فیلدهای task id, task name مقادیر ؟ تخصیص می دهیم

INSERT INTO RunInfo
(TaskID, Taskname)
VALUES (?, ?);

همانطور که می بینید، من دو علامت سوال را در بند VALUES قرار داده ام. نگهدارنده‌ها به شما اجازه می‌دهند که مقادیر متغیرهای سیستم SourceID و SourceName را در جدول RunInfo وارد کنید. با این حال، برای انجام این کار، باید متغیرها را نیز به عبارت نگاشت کنید. شکل ۱۰ صفحه Parameter Mapping ویرایشگر Execute SQL Task را نشان می دهد که شامل فهرستی برای هر متغیری است که توسط عبارت INSERT استفاده می شود.

پنجره parameter mapping برای اجرای SQL Task Editor

همانطور که شکل ۱۰ نشان می دهد، هر دو متغیر متغیرهای ورودی هستند که با نوع داده NVARCHAR پیکربندی شده اند. علاوه بر این، نام متغیر اول (SourceID) 0 و نام متغیر دوم (SourceName) 1 است. این از قراردادهای نامگذاری لازم برای ارسال مقادیر پارامتر به عبارت INSERT پیروی می کند. این تمام چیزی است که برای پیکربندی Execute SQL وجود دارد. و این تنها مؤلفه ای است که به فایل اجرایی Load Data 1 اضافه کردم. با این حال، من همان رویداد را برای فایل اجرایی Load Data 2 پیکربندی کردم و سپس یک کار Execute SQL را به کنترل کننده رویداد اضافه کردم، درست مانند وظیفه در Load Data 1 تنظیم شده است. این بدان معناست که وقتی بسته SSIS اجرا می شود، دو رویداد را اجرا می کند. کنترل کننده ها، یکی برای هر جریان داده.

اجرای Package SSIS

هنگامی که کنترل کننده رویداد خود را پیکربندی کردید، آماده اجرای بسته SSIS هستید. اجرای بسته ای که شامل کنترل کننده رویداد است با اجرای هر نوع بسته دیگری تفاوتی ندارد. تا زمانی که قابل اجرا رویدادی را که اجزای SSIS برای آن پیکربندی شده اند صادر کند، کنترل کننده رویداد اجرا می شود. این بدان معناست که در این مورد، تا زمانی که وظایف جریان داده با موفقیت اجرا شوند، رویدادهای OnPostExecute صادر می‌شوند و وظایف SQL اجرا می‌شوند. سپس عبارات INSERT در آن وظایف، اطلاعات متغیر را به جدول RunInfo اضافه می کند.

پس از اینکه اولین بار بسته EventHandlersPkg را اجرا کردم، جدول RunInfo را جستجو کردم و نتایج زیر را دریافت کردم:

همانطور که مشاهده می کنید task id و task name به جدول اضافه شده است و همانطور که انتظار می رود برای هر کار یک ردیف وجود دارد. اگر بسته را چندین بار اجرا کنید، ردیف های اضافی را مشاهده خواهید کرد.

اگرچه مثالی که در این مقاله نشان دادم بسیار ابتدایی است، اما به شما نشان می‌دهد که کنترل‌کننده‌های رویداد می‌توانند اطلاعات مختلفی را تحت شرایط خاص ثبت کنند. و می توانید اقدامات دیگری را نیز انجام دهید. به عنوان مثال، علاوه بر اینکه می‌توانید سیستمی برای بازرسی بسته‌های خود راه‌اندازی کنید، می‌توانید اقداماتی مانند ارسال ایمیل در صورتی که یک فایل اجرایی رویداد OnError را صادر کند، انجام دهید. کنترل‌کننده‌های رویداد SSIS انعطاف‌پذیر هستند و گزینه‌های زیادی را برای ممیزی و نظارت بر پکیج SSIS شما ارائه می‌دهند. و با توجه به اینکه پیاده سازی آنها چقدر آسان است، ارزش زمان و تلاش برای استفاده کامل از تمام مواردی که گردانندگان رویداد ارائه می دهند را دارد.

منابع

https://www.red-gate.com/simple-talk/databases/sql-server/bi-sql-server/ssis-event-handlers-basics/.