آموزش SSIS به صورت گام به گام – راهنمای کامل

آموزش SSIS، در این مقاله خدمات یکپارچه سازی یا همان SSIS شامل مراحلی چون: Extract: استخراج، Transform: تبدیل، Load: بارگذاری در پلتفرم‌‌های مایکروسافت می‌‌باشد. همچنین موارد استفاده از SSIS

• به طور معمول در پروژه‌ها و سناریوهای Data Warehousing مورد استفاده قرار می‌گیرند.
• اما می‌توانند در سناریوهایی چون انتقال اطلاعات یا تغییر فرمت دیتا نیز استفاده شوند.که مثالی برای این مورد می‌‌توان به قسمت:
  Maintenance Plans و Import /Export wizard در SQL SERVER اشاره کرد.

کاربرد های SSIS

• انتقال داده‌ها از یک منبع اطلاعاتی نظیر پایگاه داده اوراکل به یک منبع اطلاعاتی دیگر مانند SQL SERVER، این انتقال در حافظه (Memory) صورت می‌‌گیرد و زمانی که دیتا در حافظه می‌باشد عملیات تغییر فرمت دیتا یا هر تغییر مورد نیاز (Data Manipulation) بر روی دیتا اعمال می‌شود همین کار باعث می‌شود تا SSIS ابزاری با سرعت بسیار بالا نسبت به بقیه ابزار انتقال اطلاعات باشد.
• با استفاده از SSIS می‌‌توان اطلاع رسانی‌های عمومی انجام داد مانند استفاده از کامپوننت Script Task که یکی از قدرتمندترین کامپونتت‌های SSIS است و می‌توان با استفاده از آن نوتیفیکشن‌های مورد نیاز را از طریق ایمیل به کاربران فرستاد.
• می‌توان از کامپوننت FTP Task برای انتقال اطلاعات و فولدرها از سرورها به سیستم‌های Local استفاده کرد.
• SSIS ابزار بسیار قدرتمندی برای Error Handling می‌باشد، به صورتی که می‌توان با استفاده از قابلیت‌‌هایی چون OnError, OnInformation و OnPostExecute برای خطاگیری استفاده کرد و بزرگترین پکیج‌هایSSIS را با این قابلیت به سادگی خطاگیری کنید.
• این ابزار قابلیت مدیریت بسیار بالایی دارد به صورتی که می‌توان در یک پکیچ چندین عملیات را به صورت موازی انجام داد و حتی می‌توان با استفاده از قابلیت Precedence Constraint عملیات را به صورت شرطی انجام داد.
• لازم به ذکر می‌‌باشد که اگه تمامی موارد که در بالا ذکر شد هم راهی برای حل مسئله شما نباشد می‌‌تواند با استفاده از کدهای .Net کامپوننت‌‌های مورد نیاز را کدنویسی کرد و استفاده نمود.

• در این مقاله نحوه نصب SSIS 2019 بر روی Visual Studio 2019 و ساختن یک پکیج انتقال اطلاعات که دارای مراحل استخراج، تغییرشکل، بارگذاری می‌‌باشد و همین طور بارگذاری پکیج بر روی SQL Server و نحوه مانیتورینگ پکیج، آموزش داده می‌‌شود.

این آموزش شامل چند فصل می‌‌باشد که به شرح زیر می‌باشد:

• استخراج دیتا (Extract)
• کنترل جریان (Control Flow)
• تغییر شکل دیتا(Transform)
• بارگذاری دیتا (Load)
• استقرار بسته انتقال (Deploy Package)
• اجرای بسته (Executing Package)
• مانیتورکردن مراحل اجرا(Monitoring Execution)

 
لازم به ذکر است که ممکن است به صورت پیوسته باشند و دو قسمت در قالب یک بخش آورده شود

بخش اول: نحوه نصبSSIS 2019 بر رویVisual Studio 2019

تغییری که در نرم افزار Visual Studio 2019 نسبت به Visual Studio 2017 ایجاد شده و یکی از تغییرهای مهم این نرم افزار است این است که دیگر نیازی نیست یک نرم افزار جداگانه برای پروژه‌‌های Business Intelligence که به اصطلاح Microsoft SQL Server Data Tools نام دارد نصب کرد و باید از طریق اضافه کردن افزونه‌‌ها، مطابق تصاویر زیرعمل کرد:

۱- مرحله اول

برای اضافه کردن افزونه مخصوص SSIS باید از تب Extensions استفاده کرد مطابق شکل.

۲- مرحله دوم

زمانی که بر روی نوار افزونه‌ها کلیک می‌کنیم این صفحه نمایش داده می‌شود که باید مطابق مراحل در شکل عمل کرد که ابتدا باید در نوار جست و جو کلمه SSDT را سرچ کرد و سپس از گزینه‌‌های پیشنهادی باید مطابق مرحله سوم این افزونه رو دانلود نمود که لینک دانلود نمایش داده شده و قابل استفاده نیز می‌باشد(با کلیک کردن بر روی تصویر).

۳- مرحله سوم

در این مرحله بعد از اتمام دانلود افزونه مطابق شکل زیر عمل می‌کنیم.۴-مرحله چهارم:
صبرمی‌‌کنیم تا مرحله نصب کامل شود.۵- مرحله پنجم۶- مرحله ششم
۷- مرحله نهایی
با پیمودن این مراحل حالا SSIS 2019 را بر روی نصب کرده ایم و آماده ایجاد یک پیکج انتقال اطلاعات هستیم.
لازم به ذکر است که برای استفاده از SSIS نیاز داریم که در زمان نصب SQL Server 2019 نیز تنظیمات لازم برای این افزونه را انجام داد.
این تنظیمات به شرح ذیل می‌‌باشد:
• نحوه تنظیم SQL Server 2019 برای استفاده از سرویس SQL Server Integration Services :
با استفاده از فایل نصبی برنامه SQL Server 2019 با توجه به شکل زیر عمل می‌کنیم:
زمانی که طبق شکل عمل می‌‌کنیم و از طریق SQL Server Installation Center برای اعمال تنظیمات استفاده می‌کنیم باید زمانی که در قسمت دوم تصویر هستیم آدرس فایل نصبی SQL Server رو به نرم افزار بدهیم تا نرم افزار بتواند تغییراتی که قرار است بر روی Instance مورد نظر اعمال شوند را اجرا و ذخیره کند.
لینک دانلود SQL Server نیز بر روی تصویر گذاشته شده و قابل استفاده می‌‌باشد، پیشنهاد می‌‌شود از مرحله DownLoad Media مرحله دانلود را پیموده و نیز به صورت ISO نرم افزار را دانلود کنیدزمانی که آدرس فایل نصبی را به نرم افزار دادیم باید مطابق شکل زیر عمل کنیم
در تب Feature Selection باید حتما این دو گزینه را تیک بزنیمDataBase Engineوظیفه ذخیره سازی، بازیابی اطلاعات و همین طور امنیت داده‌‌های بانک اطلاعاتی را تامین می‌‌کند در واقع این ویژگی موتور SQL Server می‌‌باشد.
Integration Servicesهم توضیح داده شد، زمانی که DataWarehous داریم برای انجام عملیاتETL باید از این سرویس و فرآیند استفاده نمود.با تیک زدن این دو مورد و نصبشان حالا سیستم آماده انجام عملیات ETL می-باشد.
حالا بعد از انجام تنظیمات مربوطه به ساختن یک پکیج SSIS می­پردازیم

آموزش ساختن یک بسته انتقال اطلاعات

در این قسمت از خدمات اصلی SSIS که شامل استخراج، تغییرات دیتا، بازگذاری می‌‌باشد، استفاده می-شود. برای انجام مراحل ساختن پکیج از دیتابیس Wide World Importers که یک دیتابیس نمونه Open-Source که در واقع نمونه اطلاعات یک شرکت واقعی در آن گنجانده شده توسط مایکروسافت ساخته شده استفاده می‌‌شود. است.
تمامی اطلاعات مورد نیاز در این پایگاه داده را می‌‌توانید در لینک زیر مشاهد کنید:

https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers

 برای شروع پروژه می‌‌بایست بک آپ این پایگاه داده را Restore کنیم، لینک دانلود بک آپ پایگاه داده در زیر آورده شده و می‌‌توانید دانلود کنید.

https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak

 بعد از دانلود فایل بک آپ نحوه Restore کردن دیتابیس مطابق شکل‌‌های زیر توضیح داده می‌‌شود:
با استفاده از نرم افزار SSMS بر روی فولدر دیتابیس کلیک راست کرده و گزینه Restore Database … را کلیک می‌‌کنیم.
مرحله بعد باید گزینه Device رو تیک زده و آدرس فایل بک آپ را به نرم افزار بدهیمدر قسمتSelect Backup Device روی گزینه Add کلیک می‌-کنیم.و در File Explorer باید آدرس فایل بک آپ را انتخاب کنیم.بعد از انجام این مرحله تنها یک مرحله دیگر تا Restore پایگاه داده پیش رو داریمزمانی که کلیک تایید را میزنیم باید در قسمت تب Files کلیک کرده و آدرس فایل‌‌های پایگاه داده را که به صورت پیش فرض در راه نصب SQL 2019 تنظیم شده اند، عوض کرد.بعد از این مراحل ما پایگاه داده Wide Word Importers را Restore کرده‌‌ایم و می‌‌توان برای دانلود فایل بک آپ Data Warehouse این پایگاه داده نیز از لینک زیر استفاده بفرمایید.

https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImportersDW-Full.bak

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

در این مقاله به آموزش اسنخراج دیتا و کنترل جریان به صورت گام به گام می پردازیم. با ما همراه باشید:

در بخش اول آموزش SSIS

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

در بخش دوم آموزش SSIS

مرحله استخراج دیتا که اولین مرحله ETL می¬باشد را شروع کرده و فصل را به پایان می رسانیم. افراد علاقه‌مند می‌توانند با مطالعه مقاله پرکاربردترین دستورات SQL Server، دانش خود را در زمینه کوئری‌نویسی گسترش دهند.

کنترل جریان (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 را اجرا کند.

آموزش SSIS، این کامپوننت علاوه بر اینکه می‌تواند دستورات را به صورت دستی داخلش واردکرد می‌تواند با استفاده 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

چرا تراکنش‌‌های توزیع شده به این هماهنگ کننده نیاز دارند؟

آموزش SSIS، چون پروتکلی که این تراکنش ها دارند از نوع ۲ Phase Commit (TPC) اند که یعنی اینطور رفتار می‌کند که چک می‌کند هر سرور، تراکنشی را که ازش خواسته شده را انجام داده باشد و این عمل انجام شده اگر برای همه تراکنش های، همه ی سرورها موفقیت آمیز بود دستور می دهد که تمامی تراکنش ها Commit شوند. یا اگر حتی یکی از این تراکنش ها Commit نشده بود،تمامی آن ها Rollback شوند. شما می‌توانید کوئری نویسی را می‌توانید به صورت گام‌به‌گام و به آسانی از نیک آموز یاد بگیرید.
برای اینکه با این خطا مواجه نشویم باید در قسمت Services این سرویس را از حالت دستی خارج کنیم و حالا با Start کردن این سرویس خواهیم داشت.
آلان حالت تراکنشی کانتینر کاملا درست عمل کرده و تراکنش ها RollBack شده اند. در این مثال نیز سعی بر آن شد تا پیش زمینه ایی از قسمت مانیتورکردن مراحل اجرا(Monitoring Execution) نیز به تصویر کشیده شود. For Loop Container: دقیقا همان ویژگی هایی که برای Sequence توضیح داده شده را داراست به علاوه یک سری ویژگی های بیشتر، این ویژگی ها همان طور که اسم این کانتینر پیداست حالت حلقه یی دارد و دقیقا مانند حلقه های تکرار زبان های برنامه نویسی عمل می کند. حال به یک مثال برای درک بهتر نحوه استفاده از این کانتینر می پردازیم :
فرض کنید قصد داریم تا با استفاده از این کانتینر حاصل سه به توان ده را ببینیم: برای اینکار ابتدا باید دو متغیرتعریف کنیم یکی برای تعداد تکرار کانتینر و یکی ام برای معرفی کردن عدد سه، برای انجام این عملیات نیز از Component Expression Task استفاده می نمایم. با توجه به تصویر داریم.

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

دقت داشته باشید که برای علامت منطقی فاصله به کار نبرید.چرا باعث بروز خطا می‌شود.حال به سراغ تنظیم کامپوننت مورد نظر می‌رویم.حال با استفاده از این کامپوننت می‌توان از متغیرهایی که در ابتدا تعریف کرده‌ایم استفاده نمود.حال بر روی عدد پایه کلیک کرده و به صورت Drag and Drop عمل می‌کنیم، هر زمان عملیاتی که مد نظر بود را نوشتید برای اعتبارسنجی آن حتما گزینه Evaluate Expression را بزنید تا نتیجه عبارت شما نمایش داده شود. در صورت نشون نداد خطا عبارت شما درست نوشته شده است و در غیر این صورت، عبارت باید اصلاح شود.
حال OK را کلیک کرده و بسته قابل اجرا می‌باشد، برای دیدن نتیجه این توان از حالت Breakpoints در کانتینرها استفاده می‌کنیم که بسیار در قسمت Debugging And Monitoring پر کاربرداند.

و در نهایت با اجرا کردن بسته خواهیم داشت:

این حالت نمایش داده می‌شود و با ده بار کلیک کردن بر روی گزینه Continue خواهیم داشت.

آموزش SSIS، نکته بسیار مهم برای حالت 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 استفاده کرده تاجدولی که قرار است اطلاعات خوانده شده داخلش ذخیره شود ساخته شود. برای شناخت بهتر از محیط نرم افزار SSMS مقاله 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

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

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

فقط باید توجه داشته باشید که مقابل name ما باید علامت سوال استفاده کنیم چرا که باید Execute SQL Task یک Input Parameter بگیرد که در واقع اینجا همان متغیر Table Name می باشد. این دستور در واقع نتایجی که از طریق متغیر محلی ما با استفاده از استورپروسیجر 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 اجرا می­کنیم.

جریان داده (Data Flow)‌

جریان داده بستری است که اطلاعات را منابع اطلاعاتی مبدا (اکسل، Flat File، پایگاه دادهSQL Server،پایگاه داده Oracle و …) خوانده و به حافظه سیستم اجرا کننده ETL  وارد می‌کند، تا زمانی که اطلاعات داخل حافظه می‌باشند با استفاده از Component‌های مختلف می‌توان عمل تبدیل داده (یکپارچه سازی، پاک سازی) را انجام داد و در نهایت اطلاعات را به یک یا چند منبع اطلاعاتی مقصد انتقال داد.
در بستر جریان داده چندین Component مرتبط با منابع اطلاعاتی وجود دارد که به معرفی رایج‌ترین آن‌ها می‌پردازیم:

• OLE DB: پر استفاده‌ترین Component برای اتصال به انواع منابع اطلاعاتی می باشد.بیشترین سرعت برای اتصال به منبع اطلاعاتی SQL Server را دارد
• NET: امکان اتصال به منابع اطلاعاتی که ارائه‌دهندگان آن Microsoft Azure SQL Database) .NET) می‌باشد را فراهم می‌کند. امکانات زیادی دارد اما نسبت به OLEDB سرعت پایین‌تری دارد.
• ODBC: این Component قدیمی‌تر می‌باشد و همانند OLEDB به منابع اطلاعاتی Relational متصل می‌شود و به نسبت OLE DB تنظیمات دشوارتری به منظور استفاده دارد.
• Excel: توانایی اتصال به انواع فرمت‌های اکسل و استخراج اطلاعات از Worksheets‌ها را دارد.
• Flat File: امکان اتصال به فایل‌های با فرمت  Delimited, Fixed Width, or Mixed Format) Text) ‌و CSV را دارد.
• Raw File: توانایی خواندن و نوشتن اطلاعات با سرعت بیشتر نسبت به OLEDB و Flat File.

حال به معرفی چند Component مرتبط با تغییر داده (Transforming) می‌پردازیم:

• Aggregate: توانایی اعمال توابع تجمیعی (Max، Min، Avg، Sum، Count، Count Distinct) و در نهایت عملیات Group By همانند زبان T-SQL بر روی ستون‌های انتخاب شده.
• Conditional Split: توانایی دسته‌بندی و هدایت اطلاعات با استفاده از شرط‌های وضع شده(همانند CASE) را فراهم می‌نماید.
• Data Conversion: امکان تغییر شکل اطلاعات (Data Type، Length، Output Alias، Code page و …) را فراهم می‌نماید.
• Derived Column: توانایی ایجاد ستون جدید از طریق استفاده از expression‌ها که این عبارات می توانند شامل: متغیر، توابع، عمل کننده و حتی ترکیب دو یا چند ستون موجود باشند.
• Lookup: یکی از مهم‌ترین و پراستفاده‌ترین Component‌ها در ETL می‌باشد و می‌تواند با استفاده از عمل Join بین اطلاعات ورودی و یک Dataset مرجع (یک فایل Cache شده، یک جدول یا View موجود یا حتی نتیجه یک پرس‌و‌جو ”Select”) مقادیر معتبر و نامعتبر را استخراج کند، این Component به طور مفصل در بخش پیاده‌سازی ETL توضیح داده خواهد شد .
• Merge: امکان ترکیب کردن (زیر هم قرار دادن ) دو داده ورودی را فراهم می‌کند تنها در صورتی می‌توان از این Component استفاده نمود که دو جریان ورودی به صورت Sort شده وارد آن شوند.
• Merge Join: امکان متصل کردن دو داده ورودی به یکدیگر با استفاده از عمل Left، Right، Inner، Full) Join) را به کاربر می‌دهد.تنها در صورتی می‌توان از این Component استفاده نمود که دو جریان ورودی به صورت Sort شده وارد آن شوند.
• Multicast: این همانند عمل می‌کند با این تفاوت که داده ورودی را بدون شرط دریافت می‌کند و به چندین مقصد دریافت کننده متنوع منتقل کند این عمل به وسیله‌ی Duplicate کردن داده توسط این افزونه صورت می‌گیرد.
• Script Component: یکی از قدرتمند‌ترین در فضای ETL می‌باشد که به وسیله آن می‌توان عملیات Transforming با استفاده از زبان C# پیاده سازی نمود.
• Sort: امکان مرتب سازی داده و حذف داده‌های تکراری را به کاربر می‌دهد.
• Union All: همانند Merge Component عمل می‌کند با این تفاوت که محدودیتی در تعداد داده ورودی از منابع اطلاعاتی را ندارد و همین طور نیازی به Sort بودن داده‌های ورودی نیست.

در این بخش قصد داریم با متصل شدن به یک منبع اطلاعاتی SQL Server اطلاعات شهرها را استخراج کرده و با استفاده از  Derived‌Column دو ستون جدید به جریان داده اضافه کنیم و در مرحله بعد با استفاده از Lookup نام ایالت شهرهای استخراج شده را پیدا کرده و در نهایت داخل منبع اطلاعاتی مقصد منتقل کنیم.

خواندن از بانک اطلاعاتی مبدا

به دلیل توضیحات داده شده در بخش‌قبل از چگونگی اتصال به بانک‌های اطلاعاتی (WideWorldImporters) در این مرحله توضیحات به صورت کوتاه و در قالب متن خواهد بود.
با استفاده از Component OLE DB به بانک اطلاعاتی (WideWorldImporters) متصل شده و از ویژگی SQL Command برای Select داده استفاده می‌کنیم.

SELECT
    [CityID]
   ,[CityName]
   ,[StateProvinceID]
   ,[LatestRecordedPopulation]
FROM [WideWorldImporters].[Application].[Cities]

ایجاد ستون جدید و عمل پاک سازی داده(Transforming)

با استفاده از کد SQL بالا اطلاعات شهرها را واکشی می‌کنیم لازم به ذکر است با توجه به تصویر بالا به دلیل Sort  بودن اطلاعات جدول نیازی به استفاده از Sort Component نیست و در مرحله بعد از Derived Column برای اضافه کردن دو ستون جدید استفاده می‌کنیم.
دقیقا مطابق شکل برای تنظیم ستون‌ها عمل می‌کنیم، ابتدا ستون جدید LoadDate که از جنس تاریخ و زمان است را ایجاد می‌کنیم برای قسمت Expression همان طور که در ابتدای مقاله توضیح داده شد می توان از توابع متنوع استفاده نمود که برای این ستون از یکی از توابع  Date / Time Function که GETDATE می‌باشد استفاده کردیم و هدف از ایجاد این ستون در واقع نمایش دقیق زمان اطلاعات خوانده شده از سیستم اطلاعاتی مبدا می‌باشد . برای ستون بعدی اگر دقت کنید ستون CityName را که از قبل موجود بوده انتخاب شده چرا که این ستون مقادیر نامعتبری از قبیل Space دارد. بدین منظور باید عمل Transforming  بر روی این ستون صورت پذیرد، برای انجام این عمل از یکی از توابع String Function که TRIM می‌باشد استفاده می‌کنیم، با توجه به شکل متوجه می‌شویم که هر گاه اگر تابعی را انتخاب کنیم نرم افزار SSIS توضیحی کوتاه در مورد نحوه عملکرد تابع انتخاب شده به ما نشان می‌دهد که بسیار کاربردی است

استفاده از LookUp Component

یکی از مهم‌ترین و پراستفاده‌ترین Component ها در ETL می‌باشد و می‌تواند با استفاده از عمل join بین اطلاعات ورودی و یک Dataset مرجع (یک فایل Cache شده، یک جدول یا View موجود یا حتی نتیجه یک پرس و جو”Select”) مقادیر معتبر و نامعتبر را استخراج کند.
برای تنظیم کردن LookUp  مطابق شکل زیر عمل می‌نماییم.

• مدل Cache Mode‌:

برای انتخاب کردن نحوه بارگذاری جدول مورد نظر می‌باشد که شامل سه گزینه می‌باشد:

• Full Cache: جدول مورد نظر به صورت کامل در RAM بارگذاری بشود و داخل RAM جست و جو انجام شود که بالاترین سرعت را نسبت به بقیه گزینه‌ها خواهد‌داشت.
• Partial Cache: به صورت بخش بخش داده داخل RAM بارگذاری می‌شود.و در مواقعی که اصطلاحا cache miss پیش می‌آید بخش بعدی داده را داخل RAM ، Fetch می‌کند، این گزینه برای زمانی مناسب می‌باشد که جدول مورد نظر ما در حد میلیارد، رکورد داشته باشد و ما امکان انتقال تمامی داده به RAM را نداریم.
• No Cache: استفاده از این گزینه توصیه نمی‌شود، زیرا پایین ترین سرعت را دارد چرا که این گزینه باعث می‌شود که LookUp ما بر روی Disk صورت گیرد.

• مدل Connection Type‌:

برای انتخاب کردن نحوه اتصال به جدول مورد نظر که شامل دو گزینه می‌باشد:

•  Cache Connection Manager: همان حالت Cache Mode توضیح داده شده در قسمت قبل می‌باشد. 
•  OLE DB Connection Manager: استفاده از ارتباط OLE DB Component

• مدل No match behavior‌:

برای انتخاب کردن نحوه رفتار با داده دارای مغایرت که شامل چهار رفتار می‌باشد:

•  Ignore Failure: صرف نظر کردن از رکورد دارای مغایرت.
•  Redirect Rows to Error Output: انتقال رکورد دارای مغایرت به خروجی خطا (Red Path).
•   Fail Component: متوقف کردن کل عملیات LookUp در صورت پیدا شدن رکورد دارای مغایرت.
•  Redirect Row to No Match Output: انتقال رکورد‌های دارای مغایرت به خروجی مخصوص رکورد‌های دارای مغایرت، این گزینه بهترین و بیشترین استفاده در عملیات LookUp را دارد و توصیه می‌شود.

بعد از تنظیم کردن این صفحه به تنظیم  بخش Connection می پردازیم:
Connection ارتباط به پایگاه داده را تنظیم کرده و سپس از گزینه Use Results Of An SQL Query برای واکشی جدول مورد نظر (Dimension، DataSet) استفاده می‌کنیم.
Connection ارتباط به پایگاه داده را تنظیم کرده و سپس از گزینه Use Results Of An SQL Query برای واکشی جدول مورد نظر (Dimension، DataSet) استفاده می‌کنیم.
با استفاده از کد SQL زیر جدول را واکشی می‌نماییم.

SELECT
 [StateProvinceID]
,[StateProvinceName]
FROM [Application].[StateProvinces]

در آخرین مرحله از تنظیم LookUp خواهیم داشت :
از جدول مبدا ID ایالت را انتخاب می‌کنیم و سپس با استفاده از موس ارتباط بین ID جدول مبدا و ID جدول LookUp را برقرار می‌کنیم، برای برگرداندن اسم ایالت‌ها چک باکس فیلد نام ایالت‌ها در جدول LookUp را می‌زنیم.

بارگذاری داده در مقصد(Load)

برای بارگذاری داده از دو مقصد استفاده می‌کنیم اولین مقصد برای بارگذاری رکوردهای بدون مغایرت(Match Output) و دومین مقصد برای بارگذاری رکوردهای دارای مغایرت (No Match Output) که یک فایل Text می‌باشد.
برای تنظیم Match Output از یک OLE DB  استفاده می کنیم :
ابتدا ارتباط با پایگاه داده مقصد () را ایجاد می‌کنم و سپس برای دسترسی داده از گزینه Table or View را انتخاب کرده و سپس برای ساخت یک جدول جدید که مطابق داده‌های موجود در جریان داده است اقدام می‌کنیم.
با انتخاب گزینه New، SSIS کد SQL متناسب با داده ورودی به مقصد را ایجاد می‌کند، همچنین برای پرهیز از این کار نیز می‌توان از کمبو باکس Name of the table or the view  جدول موجود را انتخاب کنیم.

) CREATE TABLE [WideWorldImportersDW_City]
    [CityID] int,
    [CityName] nvarchar(50),
    [StateProvinceID] int,
    [LoadDate] datetime,
    (  [StateProvinceName] nvarchar(50)

حتما در نظر داشته باشید که بعد از انتخاب جدول مقصد به بخش Mappings رفته تا یک بار Map کردن فیلدهای انتخابی انجام شودحال برای رکورد‌های No Match Output  از یک Flat File Destination استفاده می‌نماییم:در نهایت بعد از ذخیره کردن مراحل انجام شده، ETL را اجرا می‌کنیم و خواهیم داشت:
مشاهده می‌شود که دو رکورد از رکوردهای خوانده شده دارای مغادیر نامعتبر بودن و به فایل Text منتقل شده‌اند.
Match Output
No Match Output

سخن پایانی

در این مقاله به آموزش SSIS روش نصب بستر و ابزار کار با SSIS پرداختیم در مقاله بعدی قصد داریم شما را با روش استخراج اطلاعات و محیط کنترل جریان (Control Flow) آشنا کنیم. همچنین پایگاه داده WideWorldImporters شامل ۴۹ جدول می‌باشد که با استفاده از کد های اعمال شده می‌توان اطلاعات و حجم مورد استفاده هریک از این جداول را مشاهد نمود؛ در نهایت مشاهده شد که دو رکورد که دارای کد ۹۹۹۹ بودند به عنوان رکورد‌های مغایرت‌دار داخل فایل نوشته شده‌اند.

ما در نیک آموز منتظر نظرات ارزشمند شما درباره این مقاله هستیم.