آموزش Data Flow Task – جلسه نهم آموزش هوش تجاری

همراهان گرامی در نهمین جلسه از آموزش هوش تجاری قصد داریم شما را با مفهوم Data Flow Task آشنا کنیم. Data Flow Task مهمترین Task موجود در SSIS جهت کار با داده می باشد , که در ادامه به معرفی آن می پردازیم با ما همراه باشید.

معرفی Data Flow Task و شروع کار با آن

Data Flow Task وظیفه انتقال داده بین انواع منابع داده را دارد و در این میان به کاربر اجازه می دهد که داده را پاکسازی و پالایش کند. در واقع Data Flow Task شامل مجموعه ای از Component ها است که وظیفه خواندن داده (Extract) و پاکسازی و پالایش داده (Transform) و بارگزاری (Loading) داده را دارند.

در مرحله اول و برای شروع کار یک Data Flow Task به Control Flow اضافه کنید و روی آن دابل کلیک کنید تا وارد محیط Data Flow شوید.

همان طور که مشاهده می کنید هنگامی که در تب Data Flow هستید در پنجره SSIS Toolbox لیست Component ها ظاهر می شود.

Component ها در SSIS Toolbox در سه دسته تقسیم می شوند:

Source : آن دسته Component هایی هستند که برای خواندن داده از منابع داده مختلف استفاده می شوند.
Transform : آن دسته Component هایی هستند که برای پاکسازی داده استفاده می شوند.
Destination : آن دسته Component هایی هستند که برای نوشتن (بارگزاری) داده در منابع داده مختلف استفاده می شوند.

# در این جلسه و جلسات بعدی قصد داریم در مثال های مختلف به معرفی Component های Data Flow Task بپردازیم .

معرفی Component های OLE DB Source و Sort و Merge Join و OLE DB Destination در قالب مثال

در مثال زیر قصد داریم داده موردنیاز را از جداول [Sales].[SalesOrderHeader] و [Sales].[SalesOrderDetail] از پایگاه داده AdventureWorks2017 را بخوانیم و سپس آن ها را join کنیم و در نهایت در یک جدول جدید بریزیم.

پایگاه داده AdventureWorks2017 را می توانید از این آدرس دانلود کنید.

برای شروع ابتدا 2 OLE DB Source و 2 Sort و یک Merge Join و یک OLE DB Destination به صفحه Data Flow اضافه کنید و سپس آن ها را به هم متصل نمایید. خطی که Component ها را به هم متصل می کند Data Flow Path نام دارد که در جلسه بعدی در مورد آن توضیح خواهیم داد.

تنظیم OLE DB Source
همانطور که گفتیم از component هایSource برای خواندن داده از منابع داده مختلف استفاده می کنیم. در این مثال برای خواندن جداول مورد نظر از OLE DB Source استفاده می کنیم. OLE DB Source توانایی خواندن داده از منابع داده مختلف مانند SQL Server یا Access که از OLE DB پشتیبانی می کنند را دارد.

برای تنظیم OLE DB Source روی آن دابل کلیک کنید.

گام اول : ابتدا باید در قسمت OLE DB Connection Manager یک OLE DB Connection ایجاد و یا از منوی بازشو انتخاب کنید. اگر از قبل OLE DB Connection ایجاد کردید می توانید از Combo Box موجود در قسمت OLE DB Connection Manager آن را انتخاب کنید و یا روی دکمه New کلیک کنید تا وارد صفحه Configure OLE DB Connection Manager شوید.

همان طور که مشاهده می کنید در قسمت Data Connection لیست Data Connection هایی که از قبل ایجاد شدند نشان داده شده اند. با کلیک بر روی هر یک از Data Connection ها می توانید خصوصیات آن را در قسمت Data Connection Properties مشاهده کنید. برای ایجاد یک Data Connection جدید روی دکمه New کلیک کنید تا وارد صفحه Connection Manager شوید.

گام دوم : ابتدا در قسمت Provider گزینه SQL Server Native Client را انتخاب کنید.
گام سوم : در قسمت Server Name آدرس یا نام سرور را وارد کنید.
گام چهارم : در قسمت Authentication نوع Authentication را انتخاب کنید. اگر از SQL Server Authentication استفاده می کنید در قسمت User Name نام Login و در قسمت Password رمز عبور را وارد کنید.
گام پنجم : در قسمت Select Or Enter Database Name نام Database مورد نظر خود را انتخاب کنید و سپس روی OK کلیک کنید. حال وارد صفحه Configure OLE DB Connection Manager می شوید , مجدد روی OK کلیک کنید.

نکته : تا این جای کار Connection مورد نظر ما ایجاد شد. حال در قسمت Data Access Mode باید مشخص کنید که نحوه دستیابی به داده مورد نظر چگونه است. در این قسمت گزینه Table Or View را انتخاب کنید و در قسمت Name Of The Table Or The View جدول [Sales].[SalesOrderHeader] را انتخاب کنید.

در حالت کلی Data Access Mode دارای 4 گزینه است که عبارتند از:

اگر گزینه Table Or View را انتخاب کنید باید در قسمت Name Of The Table Or The View نام جدول یا View مورد نظر خود را انتخاب کنید.
اگر گزینه Table Name Or View Name Variable را انتخاب کنید باید در قسمت Variable Name متغیر مورد نظر خود را انتخاب کنید. در واقع این گزینه می گوید که نام جدول یا View مورد نظر در یک متغیر می باشد.
اگر گزینه SQL Command را انتخاب کنید در قسمت SQL Command Text باید دستور T_SQL مورد نظر را وارد کنید.
اگر گزینه SQL Command Variable را انتخاب کنید باید در قسمت Variable Name متغیر مورد نظر خود را انتخاب کنید . در واقع این گزینه می گوید که دستور T_SQL مورد نظر در یک متغیر می باشد .

نکته : توجه داشته باشید که دو گزینه Table Name Or View Name Variable و SQL Command Variable معمولا برای Dynamic کردن استفاده می شوند.

گام ششم : حال به تب Columns بروید تا ستون های مورد نظر شناسایی شوند. همچنین در قسمت Available External Columns می توانید تیک ستون هایی را که نمی خواهید بردارید. در قسمت Output Column می توانید اسم مستعار (Alias) برای ستون مورد نظر خود تعیین کنید.

پس از انجام تنظیمات مورد نظر روی OK کلیک کنید.

برای این مثال

– ابتدا در قسمت OLE DB Connection Manager یک Connection جهت ارتباط با دیتابیس AdventureWorks2017 ایجاد کنید.

– در قسمت Data Access Mode گزینه Table Or View را انتخاب کنید.

– در قسمت Name Of The Table Or The View جدول [Sales].[SalesOrderHeader] را انتخاب کنید.

– در تب Columns فقط ستون های [SalesOrderID],[OrderDate],[DueDate],[ShipDate] را انتخاب کنید.

توجه : تنظیم OLE DB Source دوم نیز مانند OLE DB Source اول است با این تفاوت که به جای جدول [Sales].[ SalesOrderHeader] جدول [Sales].[SalesOrderDetail] را انتخاب کنید و در تب Columns فقط ستون های [SalesOrderID],[SalesOrderDetailID],[OrderQty],[ProductID] ,[UnitPrice] را انتخاب کنید.

تنظیم Sort
Merge Join یکی از Component های SSIS می باشد که برای پیوند زدن دو جدول به صورت افقی (Horizontal Join) استفاده می شود. Merge Join نیاز به ورودی مرتب شده بر اساس شروط Join دارد. برای مرتب سازی داده در SSIS از کامپونت Sort استفاده می کنیم. برای تنظیم Sort روی آن دابل کلیک کنید.

گام اول : در Check Box های سمت چپ (ستون اول) ستون های مورد نظر خود را برای مرتب سازی انتخاب کنید. در این مثال تیک ستون SalesOrderID را بزنید.
گام دوم : ستون Pass Through تعیین می کند که ستون مورد نظر در خروجی ظاهر شود یا نه. تیک هر ستونی را بردارید ستون مورد نظر در خروجی ظاهر نخواهد شد.
گام سوم : هنگامی که یک ستون را برای مرتب سازی انتخاب می کنید ستون مورد نظر در قسمت (Grid) پایین ظاهر می شود. این قسمت شامل ستون های زیر است:

Output Alias : در قسمت Output Alias می توانید برای ستون مورد نظر یک نام مستعار انتخاب کنید.

Sort Type : این قسمت تعیین می کند که ستون مورد نظر به صورت صعودی (Ascending) مرتب شود یا نزولی (Descending)

Sort Order : این بخش مشخص می کند ترتیب مرتب سازی ستون ها چگونه باشد. به عنوان مثال اگر ستون A , B را برای مرتب سازی داده ورودی انتخاب کرده باشید و Sort Order برای ستون A برابر 1 باشد و برای ستون B برابر 2 ؛ آنگاه داده ورودی ابتدا براساس ستون A مرتب می شود و سپس در مقادیر A های یکسان بر اساس B مرتب می شوند.

Comparison Flag : ستون Comparison Flag تنها برای ستون های از جنس متن فعال است که دارای مقادیر زیر است :

Ignore Case : حساس به حروف کوچک و بزرگ نمی شود. به عنوان مثال ABC برابر با abc می باشد.
Ignore kana type : این گزینه مربوط به زبان ژاپنی است. اگر تیک این گزینه را فعال کنید تفاوت بین hiragana و katakana ژاپنی نادیده گرفته می شود.
Ignore character width : اگر این گزینه را فعال کنید در هنگام مرتب سازی از حجم کاراکتر ها صرفه نظر می کند. همان طور که می دانید حجم برخی از کاراکتر ها یک بایت و برخی دو یا چند بایت است.
Ignore non spacing characters : اگر این گزینه فعال شود در هنگام مرتب سازی از کاراکتر های علامت (ئ ، ؤ ، إ ، أ ، ــًــٍــٌـ و …..) صرفه نظر می شود. به عنوان مثال ، “Ã ¥” برابر می شود با “a”.
Ignore symbols : اگر این گزینه فعال شود در هنگام مرتب سازی از کاراکتر های نماد ($ ، % ، & ، * و ….) صرفه نظر می شود . به عنوان مثال “* ABC” برابر می شود با “ABC”.

گام چهارم : اگر تیک گزینه Remove Rows With Duplicate Sort Values بزنید رکوردهای تکراری بر اساس ستون های مرتب سازی انتخاب شده در خروجی ظاهر نخواهد شد. پس از تنظیم Sort روی Ok کلیک کنید. تنظیمات Sort دوم نیز مانند همین Sort است. در Sort دوم نیز ستون SalesOrderID را انتخاب کنید و سپس روی OK کلیک کنید.

تنظیم Merge Join
حالا نوبت به تنظیم Merge Join میرسد. همانطور که گفتیم Merge Join یکی از Component های SSIS می باشد که برای پیوند زدن دو جدول به صورت افقی (Horizontal Join) استفاده می شود. برای تنظیم Merge Join روی آن دابل کلیک کنید.

گام اول : ابتدا باید نوع Join را در قسمت (Join Type) مشخص کنید. در این مثال Join Type را برابر Inner Join قرار دهید. – Join Type دارای 3 نوع است :
- Inner Join : فقط رکورد هایی در خروجی ظاهر می شود که کلید Join یا (Join Key) در دو جدول وجود داشته باشد. در واقع فقط اشتراک دو جدول در خروجی ظاهر می شود.
- Left Outer Join : تمام رکورد های جدول سمت چپ به علاوه رکورد های مشترک جدول سمت راست در خروجی ظاهر می شود.
- Full Outer Join : تمام رکورد های جدول سمت راست و چپ در خروجی ظاهر می شود.

نکته : Right Outer Join یکی از انواع Horizontal Join در SQL Server می باشد . Right Outer Join بر عکس Left Outer Join می باشد ، درواقع تمام رکورد های جدول سمت راست به علاوه رکورد های مشترک جدول سمت چپ در خروجی ظاهر می شود . در SSIS با توجه به وجود Left Outer join می توان آن را به راحتی پیاده سازی کرد ، تنها باید جای جدول سمت چپ و راست را تغییر دهید .این کار را می توانید با کلیک بر روی دکمه Swap Inputs انجام دهید .

نکته مهم : Cross Join یکی از انواع Horizontal Join در SQL Server می باشد .خروجی Cross Join حاصل ضرب دکارتی دو جدول می باشد. برای پیاده سازی Cross Join در SSIS ابتدا باید یک ستون به دوجدول با مقدار 1 اضافه کنید و بر اساس همان ستون دو جدول را Inner Join کنید. برای اضافه کردن ستون در SSIS از Component های مختلف مانند Derived Column استفاده می شود، در جلسات بعدی به معرفی آن خواهیم پرداخت.

گام دوم : در Combo Box های سمت چپ لیست ستون های مورد نیاز که لازم است در خروجی ظاهر شود را انتخاب می کنید.

گام سوم : ستون Join Key مشخص می کند که شرط Join بر اساس کدام ستون ها می باشد ، ستون هایی که نقش شروط Join را ایفا می کنند از طریق ورودی مرتب شده تشخیص داده می شود .

گام چهارم : هر ستونی را که برای ظاهر شدن در خروجی انتخاب می کنید در Grid پایین ظاهر می شود. این Grid شامل ستون های زیر می باشد.
- – در قسمت Input مشخص می شود که ستون مورد نظر از کدام جدول (ورودی) است .
- – در قسمت Input Column نام ستون مورد نظر ظاهر می شود .
- – در قسمت Output Alias می توانید یک نام مستعار برای ستون مورد نظر خود انتخاب کنید . این ستون بیشتر زمانی کاربرد دارد که دو ستون هم نام در دو جدول (ورودی) دارید می خواهید هر دو در خروجی ظاهر شوند .همان طور که می دانید در خروجی نمی توان دو ستون هم نام ظاهر شود و در این صورت می توانید نام یکی از ستون ها را تغییر دهید .

پس از تنظیم Merge Join روی OK کلیک کنید .

تنظیم OLE DB Destination
حال نوبت به تنظیم OLE DB Destination میرسد. از OLE DB Destination برای بارگزاری داده در منابع داده مختلف مانند SQL Server یا Access که از OLE DB پشتیبانی می کنند استفاده می کنیم . برای تنظیم آن روی آن دابل کلیک کنید.

گام اول : ابتدا باید در قسمت OLE DB Connection Manager یک Connection برای اتصال به دیتابیس مقصد انتخاب یا ایجاد کنیم .
گام دوم : در قسمت Data Access Mode نوع اتصال به جدول مقصد و نحوه بارگزاری داده در آن را انتخاب می کنید. در این مثال گزینه Table Or View- Fast Load را انتخاب کنید و در قسمت Name Of The Table Or the View نام جدول مقصد خود را انتخاب کنید. اگر جدول مقصد مورد نظر وجود ندارد می توانید روی دکمه New کلیک کنید تا جدول جدید مطابق با داده ورودی ایجاد شود. برای این مثال روی دکمه New کلیک کنید تا جدول مورد نظر ایجاد شود .

نکته : Data Access Mode دارای 4 حالت به شرح زیر است :

Table Or View : اگر این گزینه را انتخاب کنید باید در قسمت Name Of The Table Or the View نام جدول مقصد خود را انتخاب کنید.

Table Or View- Fast Load : اگر این گزینه را انتخاب کنید باید در قسمت Name Of The Table Or the View نام جدول مقصد خود را انتخاب کنید .

Table Name Or View Name Variable : با انتخاب گزینه Table Name Or View Name Variable و یا Table Name Or View Name Variable-Fast Load باید در قسمت Variable Name یک متغیر را انتخاب کنید که حاوی نام جدول مقصد باشد.

SQL Command : با انتخاب این گزینه باید در قسمت SQL Command Text جدول مقصد را با یک دستور Select فراخوانی کنید. توجه داشته باشید که این گزینه نیز برای بارگزاری داده از دستور sp_cursor استفاده می کند و سرعت پایینی دارد.

نکته مهم : تفاوت گزینه Table Or View با Table Or View- Fast Load در این است که Table Or View- Fast Load از دستور Bulk برای بارگزاری داده استفاده می کند. دستور Bulk لاگ کمتری تولید می کند و سرعت بسیار بالایی دارد. با انتخاب گزینه Table Or View برای بارگزاری داده از دستور sp_cursor می شود و درواقع دستور Insert به ازای هر رکورد تکرار می شود که سرعت بسیار پایینی دارد.

Fast Load Options : اگر در Data Access Mode از گزینه های Fast Load استفاده کنید یکسری Option در اختیار دارید که می توانید آن ها را تنظیم کنید. این Option ها عبارتند از :
- Keep Identity : به صورت پیشفرض شما نمی توانید در یک ستون Identity موجود در جدول مقصد داده بریزید . با فعال سازی این گزینه می توانید ستون Identity موجود در جدول مقصد را به وسیله داده ورودی پر کنید .

نکته مهم : اگر ستون Identity موجود در جدول مقصد Unique (یا Primary Key ) نباشد و داده ورودی دارای تکرار باشد و گزینه Keep Identity فعال باشد آنگاه داده تکراری در مقصد درج می شود و اگر ستون Identity موجود در جدول مقصد Unique (یا Primary Key ) باشد Task مورد نظر به خطا می خورد.

Keep Nulls : زمانی که برای یک ستون مقدار Default تعیین می کنید و هنگامی که در آن ستون مقدار Null ذخیره می کنید آنگاه به جای مقدار Null مقدار Default تعیین شده ذخیره می شود. اگر گزینه Keep Nulls را فعال کنید در ستون مقصد به جای مقدار Default مقدار Null ذخیره می شود.
Table Lock : جدول مورد نظر را تا پایان عملیات Lock می کند. فعال سازی این گزینه می تواند باعث افزایش سرعت شود.
Check Constraints : اگر این گزینه فعال باشد داده ورودی را هنگام بارگزاری در جدول مقصد با توجه به Check Constraint های ایجاد شده بر روی جدول بررسی می کند.
Rows per batch : مشخص می کند که در هر بسته ( Batch) حداکثر چند رکورد در جدول مقصد بارگزاری شود.
Maximum Insert Commit Size : مشخص می کند که حداکثر حجم هر بسته ( Batch) چقدر باشد.

پس از تنظیم تب Connection Manager به تب Mappings بروید تا ستون های ورودی را با ستون های جدول مقصد مورد نظر Map کنید . SSIS به صورت پیشفرض از روی نام ستون ها ، ستون های ورودی و جدول مقصد را باهم Map می کند . توجه داشته باشید که ممکن است که SSIS برخی از Mapping ها را تشخیص نداده باشد ، پس به دقت این تب را بررسی کنید.

پس از انجام تنظیمات روی OK کلیک کنید .

این مثال به پایان رسید . کافیست که F5 را بزنید تا پکیج اجرا شود . حال به دیتابیس و جدول مقصد رجوع کنید تا نتیجه کار را مشاهده کنید.

مشاورین فناوری اطلاعات چینود ارائه دهنده کلیه خدمات شبکه , پسیو شبکه , پشتیبانی شبکه , امنیت شبکه و … می باشد برای کسب اطلاعات بیشتر می توانید با شماره 02133816490 و 09195330535 تماس بگیرید.