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

آموزش جریان داده (Data Flow)  و تبدیل داده (Transforming)‌

در بخش‌اول به معرفی چند Component اصلی در SSIS و در بخش‌دوم به پیاده سازی بخشی از استخراج داده که اولین مرحله ETL می‌باشد پرداختیم.
در بخش‌سوم به معرفی بستر Data Flow و پیاده سازی یک بسته ETL (‌شامل حالت Transforming) خواهیم پرداخت.

جریان داده (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
مشاهده می‌شود که دو رکورد که دارای کد ۹۹۹۹ بودند به عنوان رکورد‌های مغایرت‌دار داخل فایل نوشته شده‌اند.
در بخش‌چهارم نحوه بارگذاری پکیج های ETL در SQL Server و Job کردن آن ها به منظور اجرای خودکار آموزش داده خواهد شد