آموزش استفاده از SQL Server Integration Services 2019 (SSIS 2019) [بخش دوم]

آموزش استخراج دیتا (Extract) و کنترل جریان (Control Flow)

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

در بخش اول

در محیط SSIS 2019 به دلیل تعداد بالای Component های قابل استفاده، تنها توضیح مختصری از مهم ترین Component ها داده می‌شود. تا بتوان با استفاده از آن ها مثال کاربردیمان را پیاده¬سازی کرد.

در بخش دوم

مرحله استخراج دیتا که اولین مرحله ETL می¬باشد را شروع کرده و فصل را به پایان می¬رسانیم.

کنترل جریان (Control Flow)

کنترل جریان در واقع محیط مدیریت کارهایی است که قصد داریم در پکیج ETL مان انجام دهیم و گردش کار و ترتیب بین کارها را می‌توان با استفاده از این محیط انجام داد.با توجه به توضیحات ابتدای فصل، در این محیط می‌توان برای مدیریت کارها از Component های مختلفی کمک گرفت.که به ترتیب توضیح داده می‌شوند.
مهم ترین کامپوننت‌هایی که در SSIS وجود دارند و خود نرم افزار Visual Studio 2019 نیز آن دو را در قسمت Favorites قرارداده است به شرح زیر هستند:Data Flow Task: در واقع هسته اصلی انجام مراحل در ETL می‌باشد و تمامی کارهای مهم برای استخراج و بارگذاری دیتا و تغییر شکل اطلاعات در این کامپوننت انجام می‌گیرید.که توضیح مفصل این Component زمانی که قسمت Transform شروع می‌شود به طور کامل داده می‌شود.
Execute SQL Task: این کامپوننت در عین اینکه ساده است می‌تواند کارهای بسیار مهمی را با استفاده از T-SQL برای کاربر انجام دهد،و در زیرمثالی آورده شده است:
برای تنظیم کردن این کامپوننت به ترتیب مراحل زیر عمل می‌کنیم:
روی کامپوننت کلیک راست کرده و ادیت را انتخاب می کنیم.برای کنترل کردن Execute SQL Task باید یک ارتباط با پایگاه‌داده که قرار است بر رویش T-SQL اجرا کنیم، ایجاد کنیم چرا که این قابلیت را داشته باشد که بتواند اطلاعات را از پایگاه داده موردنظر بخواند.زمانی که روی New Connection کلیک می‌کنیم پنجره جدیدی نمایش داده می‌شود که لیست ارتباطاتی را که از قبل ایجاد کرده ایم را نشان می‌دهد و می‌توان با کیک کردن بر روی گزینه New یک ارتباط جدید تعریف کرد، این کار را انجام می‌دهیم.حال باید تنظیمات لازم جهت انتخاب سرور مورد نظر و پایگاه داده را انجام دهیم:که سرور SQL2019 را انتخاب می‌کنیم.و در عکس بعدی پایگاه داده مورد نظر را انتخاب می‌کنیم:زمانی که این انتخاب را کردیم برای اطمینان از تنظیمات صورت گرفته می‌توان از گزینه Test Connection اطمینان پیدا کرد که ارتباط با منبع برقرار است و OK را انتخاب می‌کنیم.
و طبق تصویر زیر یک ارتباط در قسمت Connection Managers قابل مشاهده است که می‌توان چندین بار دیگر نیز از این ارتباط استفاده نمود.حالا می توان کد T-SQL را مانند شکل زیر در این کامپوننت نوشت و در مراحل ETL استفاده کرد لازم به ذکر است که این کامپوننت محدودیت کد ندارد و می‌تواند تمامی چهار دستور اصلی CRUD را اجرا کند.این کامپوننت علاوه بر اینکه می‌تواند دستورات را به صورت دستی داخلش واردکرد می‌تواند با استفاده Build Query نیز به صورت مکانیزه به شما این امکان را بدهد که دستور Select مورد نظر را با وجود جداول بسیار زیاد انتخاب کرده و بنویسید.
حال با توجه به اینکه گفته شد محیط Control Flow در واقع قابلیت ترتیب بندی انجام کارها را دارد به توضیح این موضوع می‌پردازیم:
برای این کار می‌توان از کامپوننت‌هایی به نام Containers استفاده نمودابتدای توضیح باید گفته شود که در محیط Control Flow برای ترتیب بندی کارها باید از SSIS Precedence Constraints استفاده کرد که در تصویر با رنگ¬های سبز و قرمز قابل مشاهده هستند. منطقی است که رنگ سبز به معنی این است که پردازش موفقیت آمیز بود و اجازه ورود به مرحله بعدی را دارد و با استفاده از رنگ قرمز مشخص می¬شود که باید یک کار دیگر انجام پذیرد.
Container: در واقع فضایی است که می¬توان هر تعدادTask بخواهیم داخلش جایگذاری کنیم
Sequence Container: یکی از فایده های کلی کانتینرها مرتب کردن و خلوت کردن یک پکیج SSIS است و به نیز در قسمت Debug کردن بسته بسیار کمک می¬کند.با استفاده از این کانتینر می¬توان به اختصار یک سری پردازش هایی که قرار است فعلا انجام نشود را غیرفعال کرد.و همچنین هنگامی که در مرحله ساختن پکیج هستیم می¬توان به طور اختصار فقط همان کانتینر مورد نظر را اجرا کرد و از اجرای کل بسته جلوگیری کرد. اصلی ترین اهمیت Sequence Container این می¬باشد که می توان رفتار تراکنشی برایش تعریف کرد.
نکته : رفتار پیش فرض کانتینرها تراکنشی نیست و باید این تنطیم را به صورت دستی انجام داد.بدین منظور بر روی کانتینر کلیک راست کرده و گزینه Properties را انتخاب می¬کنیم و بعد در قسمت نوار ظاهر شده در مورد رفتار تراکنشی سه انتخاب داریم که به اختصار توضیح داده می شوند:
Supported: این گزینه در واقع همان حالت پیش فرض می‌باشد که بیان می‌کنید که اگر Parent Container هر حالتی داشت از آن تبعیت کند. چرا که می‌توان چندین کانتینر در دل یکدیگر قرار داد و این نکته را هم بیان کنیم که خود محیط Control Flow در واقع یک Container می‌باشد.
Not Supported: این حالت در واقع به Parent توجهی ندارد و حالت تراکنشی نیست.
Required: در این حالت تراکنشی بودن کانتینر الزامی می¬شود اصلا رفتار هیچ بستگی به Parent یا Control Flow ندارد.
نکته بسیار مهم:
زمانی که حالت تراکنشی بودن را برای یک کانتینر انتخاب می‌کنیم باید به این نکته توجه داشته باشیم که، از دید SSIS تمامی تراکنش‌ها توزیع شده(Distributed) هستند.
تراکنش توزیع شده به تراکنشی می‌گویند که بیش از یک سرور را دربر می‌گیرد.
علت این نکته در مثال زیر پیاده سازی شده است: فرض کنید که یک کانتینر داریم و قصد داریم که با استفاده از SQL Execute Task یک آپدیت بر روی یکی از فیلدها انجام دهیم طبق شکل داریم (حالت تراکنشی فعال شده است):کدهای وارد شده در Task ها به ترتیب زیر هستند:

update [Sales].[Orders]
set
[OrderDate] = '2020-04-09'
where OrderID =  1
update [Sales].[Orders]
set
[OrderDate] = 0000/00/00
where OrderID =  1

متن خطا:

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

این خطا توضیح دهنده این موضوع می‌باشد که تراکنش‌های SSIS همگی نیاز به یک Coordinator یا هماهنگ کننده دارند(Distributed Transaction Coordinator) یا به اختصار DTC
چرا تراکنش‌‌های توزیع شده به این هماهنگ کننده نیاز دارند؟
چون پروتکلی که این تراکنش ها دارند از نوع ۲ Phase Commit (TPC) اند که یعنی اینطور رفتار می‌کند که چک می‌کند هر سرور، تراکنشی را که ازش خواسته شده را انجام داده باشد و این عمل انجام شده اگر برای همه تراکنش های، همه ی سرورها موفقیت آمیز بود دستور می دهد که تمامی تراکنش ها Commit شوند. یا اگر حتی یکی از این تراکنش ها Commit نشده بود،تمامی آن ها Rollback شوند.
برای اینکه با این خطا مواجه نشویم باید در قسمت Services این سرویس را از حالت دستی خارج کنیم و حالا با Start کردن این سرویس خواهیم داشت آلان حالت تراکنشی کانتینر کاملا درست عمل کرده و تراکنش ها RollBack شده اند.
در این مثال نیز سعی بر آن شد تا پیش زمینه ایی از قسمت مانیتورکردن مراحل اجرا(Monitoring Execution) نیز به تصویر کشیده شود.
For Loop Container: دقیقا همان ویژگی هایی که برای Sequence توضیح داده شده را داراست به علاوه یک سری ویژگی های بیشتر، این ویژگی¬ها همان طور که اسم این کانتینرپیداست حالت حلقه¬ یی دارد و دقیقا مانند حلقه¬های تکرار زبان های برنامه نویسی عمل می کند.حال به یک مثال برای درک بهتر نحوه استفاده از این کانتینر می پردازیم :
فرض کنید قصد داریم تا با استفاده از این کانتینر حاصل سه به توان ده را ببینیم: برای اینکار ابتدا باید دو متغیرتعریف کنیم یکی برای تعداد تکرار کانتینر و یکی ام برای معرفی کردن عدد سه، برای انجام این عملیات نیز از Component Expression Task استفاده می نمایم.
با توجه به تصویر داریمبرای تعریف کردن متغیر از مرحله یک شروع کرده تا به مرحله سوم که تنظیم کردن کانتینر است می‌رسیم، بدین منظور روی کانتینر کلیک راست کرده و گزینه Edit را زده و داخل این سه سطر این کد هارو جایگذاری می‌کنیم.دقت داشته باشید که برای علامت منطقی فاصله به کار نبرید.چرا باعث بروز خطا می‌شود.حال به سراغ تنظیم کامپوننت مورد نظر می‌رویم.
حال با استفاده از این کامپوننت می‌توان از متغیرهایی که در ابتدا تعریف کرده‌ایم استفاده نمود.حال بر روی عدد پایه کلیک کرده و به صورت Drag And Drop عمل می‌کنیم،هر زمان عملیاتی که مد نظر بود را نوشتید برای اعتبارسنجی آن حتما گزینه Evaluate Expression را بزنید تا نتیجه عبارت شما نمایش داده شود.در صورت نشون نداد خطا عبارت شما درست نوشته شده است و در غیر این صورت، عبارت باید اصلاح شودحال OK را کلیک کرده و بسته قابل اجرا می‌باشد،برای دیدن نتیجه این توان از حالت BreakPoints در کانتینرها استفاده می‌کنیم که بسیار در قسمت Debugging And Monitoring پر کاربرداند.و در نهایت با اجرا کردن بسته خواهیم داشت:این حالت نمایش داده می‌شود و با ده بار کلیک کردن بر روی گزینه Continue خواهیم داشت نکته بسیار مهم برای حالت BreakPoint این است که باید کل بسته ETL را اجرا کنیم و در غیراین صورت اگر تنها بر روی کانتینر کلیک کرده و گزینه Execute را بزنیم حالت BreakPoint فعال نخواهد شد.
ForEach Loop Container:
این رفتاری همانند کانتینرها در زبان C# از خودشان نشان می‌دهند، که به این صورت هست که ForEach ها اعضای یک کانتینررا Enumerate می کنند، به این معنا هست که یکی یکی اعضا را در اختیار کاربر قرار می‌دهد، در این کانتینر هشت نوع Enumeration می‌توانیم داشته باشیم و در این مقاله قصد داریم که حالت SMO Enumerator را پیاده‌سازی کنیم. مثال ما به این صورت است: کانتینری طراحی می‌کنیم که بتواند با هر بار اجرا شدن حافظه مورد استفاده شده جداول را در یک جدول خاص ذخیره کند.به منظور پیاده‌سازی این مثال به یک متغیر نیاز داریم که اسم جدول داخلش ذخیره شود.بعد از تعریف کردن متغیر بر روی کانتینرکلیک راست کرده و Edit را انتخاب می‌کنیم:ابتدا باید ارتباط با سرور را برقرار کنیم و سپس برای Enumerate روی قسمت Table ها کلیک کرده و OK را می‌زنیم.
سپس باید در قسمت Variable Mappings متغیری را که تعریف کردیم، معرفی کنیم.حال برای دیباگ کردن تا به اینجای مثال می‌توان مانند توضیحات قبلی از BreakPoint استفاده کرده و چک به صورتی که باید در هر مرحله نام جداول پایگاه داده WideWorldImporters به صورت ترتیبی نشان داده شوند.
بعد از چک کردن نحوه کار کردن کانتینر سراغ مرحله نهایی رفته، نیاز داریم که از یک Execute SQL Task استفاده کرده و طبق مراحل زیر عمل کرده:
برای اینکه یک گزارش داشته باشیم تا اطلاعات تمام جداول را نشان دهد باید یک متغیر دیگر تعریف کرده تا در واقع تعداد سطرهای گزارش تهیه شده داخلش ذخیره شود.در ادامه از کدهای زیر در محیط SSMS استفاده کرده تاجدولی که قرار است اطلاعات خوانده شده داخلش ذخیره شود ساخته شود

Create Table SpaceUsed
(
TableName NVARCHAR(200),
TotalRows NVARCHAR(200),
Reserved NVARCHAR(200),
TableData NVARCHAR(200),
Index_Size NVARCHAR(200),
Unused NVARCHAR(200)
)

حال به سراغ تنظیم Execute SQL Task می¬رویم:
ابتدا ارتباط با پایگاه داده را مانند مثال قبل تعریف کرده و نیاز است در قسمت کد دستوارت زیر را کپی کرد:
این خط در واقع متغیری که ما تعریف کردیم را گرفته و شمای مخصوص آن جدول را به اول اسم جدول اضافه کرده و در یک متغیر محلی Fullnameذخیره می کند.

Declare @fullname varchar(200) = (SELECT ‘[‘+SCHEMA_NAME(schema_id)+’].[‘+name+’]’ FROM sys.tables where name = ?)

فقط باید توجه داشته باشید که مقابل name ما باید علامت سوال استفاده کنیم چرا که باید Execute SQL Task یک Input Parameter بگیرد که در واقع اینجا همان متغیر TableName می¬باشد.
این دستور در واقع نتایجی که از طریق متغیر محلی ما با استفاده از استورپروسیجر SQL Server را گرفته است در جدول SpaceUsed ذخیره میکند.

Insert SpaceUsed EXEC sp_spaceused @fullname

این دستور نیز تعداد کل جدول هایی که پردازش شده است را برمیگرداند

Select Count(*) as TotalRowCount from SpaceUsed

این سه دستور را داخل کامپوننت ذخیره می¬کنیم و چون یک متغیری تعریف کردیم که تعداد سطرهای خوانده شده را به برگرداند(تعداد جداولی که خوانده است) باید حالت Result Set رو به صورت Single Row تغییردهیم و همان طور که گفته شد در قسمت Parameter Mapping داریم:و سپس در تب Result Set نیز متغیر TotalRowCount را معرفی می‌کنیم و تنظیم Execute SQL Task را به پایان می‌رسانیم.در این مثال برای اینکه اهمیت و کاربرد بسیار زیاد Execute SQL Task را نشان بدهیم یک حالت شرطی ایجاد می-کنیم به این صورت که اگر تعداد سطرها خوانده شده برابر ۲۰۰ بود حالت شرطی داشته باشد کانتینر به سراغ اجرا کردن یک Task دیگر برود.حال بسته را اجرا کرده و طبق شکل زیر خواهیم داشت مشاهده می­شود که به مرحله بعد نرفته است، به این معنا است که تعداد سطور و در واقع جداولی که خوانده شده است از ۲۰۰ کمتر بوده و کانتینر در همان مرحله اول کار خودش را به اتمام رسانده است.برای اطمینان از نحوه­ی عملکرد بسته کد زیر را در محیط SSMS اجرا می­کنیم.مشاهده می‌شود که پایگاه¬داده WideWorldImporters شامل ۴۹ جدول می‌باشد که با استفاده از کد های اعمال شده می‌توان اطلاعات و حجم مورد استفاده هریک از این جداول را مشاهد نمود.
با این توضیحات به اتمام فصل اول می‌رسیم و سپس در فصل دوم به سراغ آموزش Data Flow رفته و یک بسته ETL را که شامل حالت Transforming است، پیاده سازی خواهیم نمود.