بهینه‌سازی SSIS Lookup: قدرت کامپوننت اسکریپت برای ادغام داده‌ها

یکی از نیازمندی‌های رایج در پروژه‌های ETL، نیاز به LookUp یا جستجو برای تطابق و ادغام داده‌ها از منابع مختلف است. SSIS قابلیت Lookup را از طریق “Lookup Transformation” ارائه می‌دهد، اما این کامپوننت محدودیت‌هایی دارد که می‌تواند بر عملکرد سیستم تأثیر بگذارد، به خصوص هنگام کار با مجموعه داده‌های بزرگ یا نیاز به منطق جستجوی پیچیده. این مقاله نشان می‌دهد که چگونه می‌توان از “Script Component” در SSIS برای ایجاد یک Lookup سفارشی و قدرتمندتر استفاده کرد، که امکان کنترل بیشتر و بهبود عملکرد را فراهم می‌آورد.

چرا کامپوننت اسکریپت؟

Lookup Transformation استاندارد SSIS برای Lookupهای ساده و کوچک کارآمد است. با این حال، هنگامی که جدول LookUp بسیار بزرگ می‌شود، حافظه زیادی مصرف می‌کند یا به منطق LookUp پیچیده‌تری نیاز داریم (مانند چندین شرط تطابق)، عملکرد آن کاهش می‌یابد. کامپوننت اسکریپت به ما این امکان را می‌دهد که منطق LookUp خود را با استفاده از C# یا VB.NET بنویسیم، داده‌ها را به صورت سفارشی کش (Cache) کنیم و تنها زمانی به آن‌ها دسترسی پیدا کنیم که واقعاً نیاز داریم، که به طور قابل توجهی کارایی را افزایش می‌دهد.

سناریوی مثال: ادغام اطلاعات مشتریان

فرض کنید دو منبع داده داریم: یک جدول مشتریان در SQL Server و یک فایل متنی (Flat File) حاوی اطلاعات تکمیلی. می‌خواهیم با استفاده از نام و نام خانوادگی، اطلاعات این دو منبع را تطبیق دهیم و داده‌های مربوط به شهر را از فایل متنی به رکوردهای SQL Server اضافه کنیم.

مراحل پیاده‌سازی Lookup با کامپوننت اسکریپت:

1. ایجاد یک Data Flow Task:

   ابتدا یک “Data Flow Task” جدید به پکیج SSIS خود اضافه کنید. این وظیفه مسئول جریان داده‌ها و اجرای عملیات ETL است.

2. تنظیم OLE DB Source:

   یک “OLE DB Source” اضافه کنید و آن را به پایگاه داده SQL Server متصل کنید. در این مثال، ما از یک جدول به نام Customers استفاده می‌کنیم که شامل ID, FirstName, LastName و City است. برای سهولت، فقط FirstName, LastName و City را از این منبع انتخاب می‌کنیم.

3. تنظیم Flat File Source:

   یک “Flat File Source” به Data Flow Task خود اضافه کنید. این منبع به فایل متنی ما اشاره می‌کند که شامل FirstName, LastName و City است. اطمینان حاصل کنید که فرمت فایل (delimited, fixed-width) و ستون‌ها به درستی پیکربندی شده‌اند.

4. اضافه کردن Script Component:

   اکنون، “Script Component” را از بخش “Other Transformations” به Data Flow Task اضافه کنید. نوع آن را به “Transformation” تغییر دهید زیرا قصد داریم داده‌های ورودی را پردازش و تغییر دهیم.

5. پیکربندی ورودی و خروجی کامپوننت اسکریپت:

   • ورودی‌ها (Inputs):

     کانکتور خروجی “OLE DB Source” را به “Script Component” وصل کنید. در تب “Input Columns” کامپوننت اسکریپت، ستون‌های FirstName و LastName را از OLE DB Source به عنوان ورودی انتخاب کنید. این ستون‌ها برای انجام LookUp استفاده خواهند شد.

   • خروجی‌ها (Outputs):

     در تب “Input and Output” کامپوننت اسکریپت، یک ستون خروجی جدید به نام Fullname از نوع Unicode string [DT_WSTR] با طول مناسب (مثلاً 50) و یک ستون خروجی دیگر به نام City از نوع Unicode string [DT_WSTR] با طول مناسب (مثلاً 50) اضافه کنید. این ستون‌ها داده‌های LookUp شده را نگه می‌دارند.

   • متغیرها (Variables):

     به تب “Script” بروید و لیست “ReadOnlyVariables” را ویرایش کنید. باید متغیرهای سیستمی را برای کانکشن به “Flat File Source” (مثلاً User::FlatFilePath) و هر متغیر دیگری که برای خواندن فایل نیاز دارید، اضافه کنید. در این مثال، از نام کانکشن “Flat File Source” به عنوان یک متغیر ReadOnly استفاده می‌کنیم.

6. نوشتن کد در Script Component:

   با کلیک بر روی “Edit Script” در تب “Script”، محیط کدنویسی باز می‌شود. کدهای زیر را وارد کنید:

   ابتدا، نیاز داریم که یک شیء Dictionary برای کش کردن داده‌های فایل متنی تعریف کنیم. این دیکشنری، نام کامل (ترکیبی از نام و نام خانوادگی) را به عنوان کلید و لیست شهرها (در صورتی که چندین شهر برای یک نام وجود داشته باشد) را به عنوان مقدار ذخیره می‌کند.

   در بالای کلاس ScriptMain، متغیر دیکشنری را اعلام کنید:

   
   Dictionary<string, List<string>> lookupData;
   

   سپس، داده‌های Flat File را در متد PreExecute بارگذاری کنید. این متد یک بار قبل از شروع پردازش ردیف‌ها اجرا می‌شود و برای مقداردهی اولیه و بارگذاری داده‌های LookUp ایده‌آل است:

   
   public override void PreExecute()
   {
       base.PreExecute();
       lookupData = new Dictionary<string, List<string>>();
   
       // فرض می‌کنیم که Flat File Connection Manager به عنوان یک متغیر در دسترس است
       // در اینجا فقط برای مثال کد را ساده می‌کنیم
       // مسیر فایل باید از یک کانکشن منیجر واقعی یا یک متغیر خوانده شود
       string flatFilePath = Variables.FlatFileConnection.ConnectionString; // مثال: فرض می‌کنیم متغیری به نام FlatFileConnection داریم
   
       // برای خواندن فایل CSV
       using (StreamReader sr = new StreamReader(flatFilePath))
       {
           string line;
           // پرش از هدر
           if (!sr.EndOfStream) sr.ReadLine(); 
           while ((line = sr.ReadLine()) != null)
           {
               string[] parts = line.Split(','); // فرض می‌کنیم با کاما جدا شده‌اند
               if (parts.Length >= 3)
               {
                   string firstName = parts[0].Trim();
                   string lastName = parts[1].Trim();
                   string city = parts[2].Trim();
                   string fullName = (firstName + " " + lastName).ToUpperInvariant();
   
                   if (!lookupData.ContainsKey(fullName))
                   {
                       lookupData[fullName] = new List<string>();
                   }
                   lookupData[fullName].Add(city);
               }
           }
       }
   }
   

   متد PostExecute برای آزادسازی منابع بعد از پردازش تمام ردیف‌ها استفاده می‌شود:

   
   public override void PostExecute()
   {
       base.PostExecute();
       // منابع را آزاد کنید اگر لازم است
       if (lookupData != null)
       {
           lookupData.Clear();
       }
   }
   

   سپس، منطق اصلی LookUp را در متد Input0_ProcessInputRow پیاده‌سازی کنید. این متد برای هر ردیف ورودی از OLE DB Source فراخوانی می‌شود:

   
   public override void Input0_ProcessInputRow(Input0Buffer Row)
   {
       // اطمینان حاصل کنید که نام و نام خانوادگی تهی نیستند
       if (Row.FirstName_IsNull == false && Row.LastName_IsNull == false)
       {
           string fullName = (Row.FirstName.Trim() + " " + Row.LastName.Trim()).ToUpperInvariant();
   
           // انجام LookUp
           if (lookupData.ContainsKey(fullName))
           {
               // اگر چندین شهر برای یک نام موجود بود، اولین مورد را انتخاب کنید یا منطق سفارشی خود را اعمال کنید
               Row.City = lookupData[fullName][0]; 
               Row.Fullname = fullName;
           }
           else
           {
               // در صورت عدم تطابق، می‌توانید مقادیر را به Null تنظیم کنید یا مقادیر پیش‌فرض دهید
               Row.City_IsNull = true;
               Row.Fullname_IsNull = true;
           }
       }
       else
       {
           Row.City_IsNull = true;
           Row.Fullname_IsNull = true;
       }
   }
   

7. اتصال مقصد (Destination):

   پس از کامپوننت اسکریپت، یک “OLE DB Destination” (یا هر مقصد دیگری) را به Data Flow Task اضافه کنید. خروجی “Script Component” را به این مقصد متصل کنید. در این مقصد، می‌توانید ستون‌های جدید Fullname و City را به جدول مقصد خود نگاشت (Map) کنید.

مزایا و معایب:

مزایا:

• عملکرد بهتر: با کش کردن داده‌های LookUp در حافظه (در یک دیکشنری)، می‌توانیم از دسترسی‌های مکرر به دیسک یا شبکه جلوگیری کنیم، که منجر به بهبود عملکرد می‌شود.
• انعطاف‌پذیری بالا: می‌توانید هرگونه منطق LookUp پیچیده‌ای را با C# یا VB.NET پیاده‌سازی کنید، که شامل چند شرط، تطابق‌های جزئی، و استفاده از RegEx می‌شود.
• مصرف بهینه حافظه: کنترل دقیق‌تری بر نحوه کش کردن داده‌ها دارید و می‌توانید تنها ستون‌های مورد نیاز را بارگذاری کنید.

معایب:

• پیچیدگی بیشتر: نیاز به دانش برنامه‌نویسی برای نوشتن اسکریپت دارد.
• پشتیبانی کمتر: نگهداری و دیباگ کردن کد در اسکریپت کامپوننت ممکن است دشوارتر از پیکربندی یک کامپوننت استاندارد باشد.

نتیجه‌گیری:

استفاده از “Script Component” برای عملیات LookUp در SSIS یک راه قدرتمند برای غلبه بر محدودیت‌های “Lookup Transformation” استاندارد است. این رویکرد به شما امکان می‌دهد تا LookUpهای بسیار سفارشی، کارآمد و انعطاف‌پذیر را برای نیازهای پیچیده ادغام داده‌های خود ایجاد کنید، به خصوص در محیط‌هایی که با حجم داده‌های بزرگ و نیازمندی‌های عملکردی بالا مواجه هستید.