با سلام به شما دوستان گرامی امیدوارم که خوب باشید . امروز در مورد جامع (SSIS)سرویس یکپارچه سازی SQL Server آموزش درایم .SQL Server Integration Services یا SSIS، ابزاری است که برای اجرای عملیات های ETL مانند extract کردن، transform کردن، و load کردن داده ها مورد استفاده قرار می گیرد. درحالیکه فرایند ETL در برنامه های Data Warehousing رایج است، SSIS به هیچ وجه محدود به DW نیست؛ مثلاً وقتی یک با استفاده از SQL Server Management Studio، یک Maintenance Plan ایجاد می کنید، یک پکیج SSIS ایجاد می شود. در سطحی بالاتر، SSIS قابلیت های زیر را در اختیار می گذارد:
بازیابی داده ها از هر منبعی
اجرای transformationهای مختلف روی داده ها، مانند تبدیل از نوعی به نوع دیگر، تبدیل حروف بزرگ به کوچک، اجرای محاسبات، و غیره.
بارگذاری داده ها درون هر منبعی
تعریف یک جریان کاری (workflow)
نسخه اول SSIS در SQL Server 2005 معرفی شد. SSIS جایگزینی برای Data Transformation Services یا DTS بود که در SQL Server 7.0 و SQL Server 2000 دردسترس بود. SSIS بر اساس قابلیت های معرفی شده در DTS ساخته شده است.
در این مقاله، به موضوعاتی خواهیم پرداخت که برای ساختن موفقیت آمیزیک پکیج SSIS، نیاز به فهم آنها دارید.
مباحث مورد بحث در این مقاله به عبارت زیر است :
ایجاد پکیج های SSIS با SQL Server Management Studio (SSMS)
Business Intelligence Development Studio (BIDS)
ایجاد پکیج ساده در BIDS
نصب پکیج های SSIS
اجرای پکیج های SSIS
ایجاد پکیج های SSIS با SQL Server Management Studio
SSMS، تسک های ویزارد Import و Export را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه مختلف منبع و منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. استفاده از ویزارد Import و Export، نقطه شروع خوبی برای یادگرفتن پکیج های SSIS است. می توانید پکیج های جنریت شده SSIS را به این صورت اجرا کنید: آنرا زمان بندی کنید بعدا، اجرا شود، یا هر تغییری را که برای برآورده کردن نیازهایتان نیاز است، روی آن ایجاد کنید.
راهی ساده برای export کردن داده ها از SQL Server
SSMS، تسک ویزارد Export را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه منبع و انواع منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. در این بخش مروری خواهیم کرد بر Export Wizard و داده ها را از دیتابیس SQL Server به Excel spreadsheet اکسپورت می کنیم.
برای شروع، با کلیک روی SQL Server Management Studio از گروه برنامه Microsoft SQL Server، برنامه SSMS را باز کنید. هنگام باز کردن SSMS، پیامی به شما برای ایجاد یک کانکشن داده می شود، به Database Engine متصل شوید. در این مقاله از دیتابیس AdventureWorksDW استفاده شده است. برای دانلود کردن دیتابیس نمونه AdventureWorksDW،
http://msftdbprodsamples.codeplex.com /
مراجعه کنید .
دیتابیس AdventureWorksDW را در آبجکت اکسپلورر SSMS پیدا کنید؛ اگر Object Explorer را نمی بینید، روی منوی View کلیک و سپس Object Explorer را انتخاب کنید:
در Object Explorer روی دیتابیس AdventureWorksDW کلیک راست کنید، Tasks را، و سپس Export Data را از منوی context انتخاب کنید تا Export Wizard شروع شود. در صورتی که پنجره Welcome نمایش داده شود، روی Next کلیک کنید. در ادامه، مراحل ویزارد را قدم به قدم مرور خواهیم کرد.
یک Data Source انتخاب کنید
پنجره Choose a Data Source به شما اجازه تعیین منبع داده هایتان را می دهد. از آنجاییکه در حال اجرای Export wizard هستیم، این پنجره با مقادیری که از قبل وجود دارد، نمایش داده می شود، مانند شکل زیر:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
یک Destination انتخاب کنید
پنجره Choose a Destination به شما اجازه تعیین منبع داده های مقصد را برای داده هایی که export می کنید، می دهد. تقریباً آپشن های زیادی برای destination وجود دارد، inputهای اضافی بر اساس منبع داده های مقصد انتخاب شده، متنوع خواهد بود. ما برای مثالمان، داده ها را به برنامه اکسل export می کنیم و بعداً از این Excel spreadsheet به عنوان منبع در ویزارد Import استفاده خواهیم کرد. فیلدها را مانند زیر پرکنید:
Table Copy یا Query را مشخص کنید
پنجره Specify Table Copy or Query به شما اجازه انتخاب اکسپورت کردن داده ها با سلکت کردن tables و یا views از منبع داده ها یا مشخص کردن یک کوئری برای extract کردن داده ها را می دهد. Copy data from one or more tables or views را همانطور که در زیر نشان داده شده انتخاب کنید:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
Source Tables and Viewsرا انتخاب کنید
پنجره Select Source Tables and Views به شما اجازه انتخاب tableها و viewهایی را که می خواهید export کنید می دهد. در این اینجا ما جدول Dimg alt=”SQL SERVER آموزش” title=”SQL SERVER آموزش”eography را همانگونه که در زیر نشان داده شده انتخاب می کنیم:
می توانید روی دکمه Preview کلیک کنید تا ۱۰۰ ردیف اول داده ها را در منبع داده ها مشاهده کنید:
می توانید روی دکمه Edit Mappings (در پنجره Select Source Tables and Views) کلیک کنید تا column mappings را از منبع داده ها به مقصد داده ها بررسی کنید. اگر جدولی در منبع داده های مقصد وجود نداشته باشد، می توانید mappingها را نیز ویرایش کنید.
می توانید روی Option کلیک کنید تا جدول را در منبع داده های مقصد drop و recreate کنید؛ این آپشن بطور پیش فرض تیک نخورده است. اگر این جدول در منبع داده های مقصد وجود نداشته باشد، دکمه Create destination table radio انتخاب خواهد شد و دکمه های دیگر رادیو غیر فعال خواهند شد. اگر جدول از قبل در منبع داده های مقصد وجود داشته باشد، دکمه Create destination table radio غیرفعال خواهد شد و می توانید یا آپشن Delete یا Append را انتخاب کنید.
می توانید روی دکمه Edit SQL کلیک کنید تا SQL را جهت ایجاد جدول در منبع داده های مقصد بررسی یا ویرایش کنید، مانند شکل زیر:
دوبار روی OK کلیک کنید تا به صفحه Select Source Tables and Views برگردید، سپس برای رفتن به مرحله بعد، روی Next کلیک کنید.
پکیج را Save و Execute کنید
پنجره Save and Execute Package به شما امکان اجرای عملیات اکسپورت و ایجاد پکیج SSIS و ذخیره آن در SQL Server یا file system را می دهد، مانند زیر:
ما پکیج SSIS را طوری ذخیره می کنیم که بتوانیم در بخش های بعدی مقاله مشاهده کنیم. برای رفتن به مرحله بعد، روی Next کلیک کنید.
پکیج SSIS را ذخیره کنید
اگر تصمیم بگیرید عملیات اکسپورت را در پنجره Save and Execute Package بصورت پکیج SSIS ذخیره کنید، Save SSIS Package فراخوانده می شود. پنجره مذکوررا مانند زیر پر کنید:
برای رفتن به مرحله بعد،روی Next کلیک کنید.
ویزارد را کامل کنید
پنجره Complete the Wizard، خلاصه ای از آپشن هایی را نشان می دهد که برای عملیات اکسپورت انتخاب کرده اید، مانند شکل زیر:
روی Finish کلیک کنید تا پکیج SSIS اجرا شود. می توانید Excel spreadsheet را باز کنید و جداولی اکسپورت شده را مشاهده کنید. بخشی از Excel spreadsheet در زیر نشان داده شده:
راهی ساده برای ایمپورت کردن داده ها به SQL Server
SSMS، تسک ویزارد Import را، که می توانید برای کپی کردن داده ها از منبعی به منبع دیگر استفاده کنید، در اختیار می گذارد. می توانید از میان چندین گونه منبع و انواع منبع داده های مقصد، یکی را انتخاب کنید، جداولی را برای کپی کردن انتخاب کنید، یا کوئری خودتان را برای extract کردن داده ها مشخص کنید، و کارتان را بصورت پکیج SSIS ذخیره کنید. در این بخش مروری خواهیم کرد بر Import Wizard و داده ها را از Excel spreadsheet به جدولی در دیتابیس SQL Server ایمپورت می کنیم.
برای شروع، با کلیک روی SQL Server Management Studio از گروه برنامه Microsoft SQL Server، برنامه SSMS را باز کنید. هنگام باز کردن SSMS، پیامی به شما برای ایجاد یک کانکشن داده می شود، به Database Engine متصل شوید.
دیتابیس MSSQLTipsSSISTutorial را در آبجکت اکسپلورر SSMS پیدا کنید (اگر لازم است، این دیتابیس را ایجاد کنید)؛ اگر Object Explorer را نمی بینید، روی منوی View کلیک و سپس Object Explorer را انتخاب کنید:
در Object Explorer روی دیتابیس MSSQLTipsSSISTutorial کلیک راست کنید، Tasks را، و سپس Import Data را از منوی context انتخاب کنید تا Export Wizard شروع شود. در صورتی که پنجره Welcome نمایش داده شود، روی Next کلیک کنید. در ادامه، مراحل ویزارد را قدم به قدم مرور خواهیم کرد.
یک Data Source انتخاب کنید
پنجره Choose a Data Source به شما اجازه تعیین منبع داده هایتان را می دهد. فیلدها را همانطور که در زیر نشان داده شده است، پر کنید تا یک Excel spreadsheet را به عنوان منبع داده هایتان تعیین کنید:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
یک Destination انتخاب کنید
پنجره Choose a Destination به شما اجازه تعیین منبع داده های مقصد را برای داده هایی که import می کنید، می دهد. از آنجاییکه در حال اجرای Import wizard هستیم، این پنجره با مقادیری که از قبل وجود دارد، نمایش داده می شود، مانند شکل زیر:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
Table Copy یا Query را مشخص کنید
پنجره Specify Table Copy or Query به شما اجازه انتخاب ایمپورت کردن داده ها با سلکت کردن tables و یا views از منبع داده ها یا مشخص کردن یک کوئری برای extract کردن داده ها را می دهد. Copy data from one or more tables or views را همانطور که در زیر نشان داده شده انتخاب کنید:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
Source Tables و Views را انتخاب کنید
پنجره Select Source Tables and Views به شما اجازه انتخاب tableها و viewهایی را که می خواهید import کنید می دهد. در این اینجا ما جداول Dimg alt=”SQL SERVER آموزش” title=”SQL SERVER آموزش”eography را همانگونه که در زیر نشان داده شده انتخاب می کنیم:
می توانید روی دکمه Preview کلیک کنید تا ۱۰۰ ردیف اول داده ها را در منبع داده ها مشاهده کنید:
می توانید روی دکمه Edit Mappings (در پنجره Select Source Tables and Views) کلیک کنید تا column mappings را از منبع داده ها به مقصد داده ها بررسی کنید. اگر جدولی در منبع داده های مقصد وجود نداشته باشد، می توانید mappingها را نیز ویرایش کنید.
می توانید روی Option کلیک کنید تا جدول را در منبع داده های مقصد drop و recreate کنید؛ این آپشن بطور پیش فرض تیک نخورده است. اگر جدول مقصد دارای ستون identity باشد و منبع داده ها دارای مقادیری باشد که می خواهید برای identity column تخصیص دهید، می توانید روی Enable identity insert کلیک کنید.اگر این جدول در منبع داده های مقصد وجود نداشته باشد، دکمه Create destination table radio انتخاب خواهد شد و دکمه های دیگر رادیو غیر فعال خواهند شد. اگر جدول از قبل در منبع داده های مقصد وجود داشته باشد، دکمه Create destination table radio غیرفعال خواهد شد و می توانید یا آپشن Delete یا Append را انتخاب کنید.
می توانید روی دکمه Edit SQL کلیک کنید تا SQL را جهت ایجاد جدول در منبع داده های مقصد بررسی یا ویرایش کنید، مانند شکل زیر:
دوبار روی OK کلیک کنید تا به صفحه Select Source Tables and Views برگردید، سپس برای رفتن به مرحله بعد، روی Next کلیک کنید.
پکیج را Save و Execute کنید
پنجره Save and Execute Package به شما امکان اجرای عملیات ایمپورت و ایجاد پکیج SSIS و ذخیره آن در SQL Server یا file system را می دهد، مانند زیر:
پکیج SSIS را ذخیره می کنیم. برای رفتن به مرحله بعد، روی Next کلیک کنید.
پکیج SSIS را ذخیره کنید
اگر تصمیم بگیرید عملیات ایمپورت را در پنجره Save and Execute Package بصورت پکیج SSIS ذخیره کنید، Save SSIS Package فراخوانده می شود. پنجره مذکور را مانند زیر پر کنید:
برای رفتن به مرحله بعد، روی Next کلیک کنید.
ویزارد را کامل کنید
پنجره Complete the Wizard، خلاصه ای از آپشن هایی را نشان می دهد که برای عملیات ایمپورت انتخاب کرده اید، مانند شکل زیر:
روی Finish کلیک کنید تا پکیج SSIS اجرا شود. می توانید جدول ایمپورت شده را در SSMS باز و آنها را مشاهده کنید. بخشی از این در زیر نشان داده شده:
SQL SERVER آموزش
Business Intelligence Development Studio (BIDS)
BIDS، ابزاری است که برای develop کردن پکیج های SSIS استفاده می کنیم، که اینترفیسی را بر اساس محصول Visual Studio مایکروسافت در اختیار قرار می دهد که سالهاست مورد استفاده برنامه نویسان قرار گرفته شده است. BIDS، تغییر DTS است که از SQL Server Enterprise Manager بعنوان ابزاری برای طراحی پکیج استفاده می کرد. در حالی که هنوز تفاوتهایی وجود دارد، احتمالاً متوجه خواهید شد که استفاده از BIDS بمراتب آسانتر است، گرچه ممکن است کمی سختی در یادگیری آن وجود داشته باشد. این بخش، مقدمه ایست بر BIDS و مروری بر پکیج SSIS موجود.
BIDS با SQL Server باز می شود. وقتی شما ابزارهای کلاینت را زمان نصب SQL Server انتخاب می کنید، BIDS نیز نصب می شود. بعد از نصب BIDS، می توانید آن را از گروه برنامه های SQL Server باز کنید.
ایجاد projectها و solutionها با SSIS
دیاگرام BIDS برای توسعه پکیج های SSIS براساس مفهوم projectها و solutionهاست. project، دربرگیرنده یک یا چند پکیج SSIS است. می توان از projectها برای ساماندهی پکیج های SSIS استفاده کرد. مثلاً اگر من تعدادی پکیج SSIS داشته باشید که برای بارگذاری data warehouse از یک سیستم منبع مشخص استفاده می شود، ممکن است یک پروژه SSIS برای همه پکیج های SSIS که برای در فرآیند ETL برای آن سیستم منبع استفاده می شود، ایجاد کنم. ممکن است پروژه های SSIS دیگری برای سیستم های منبع مختلف دیگری ایجاد کنم که برای populate کردن data warehouse استفاده شوند.
solution، دربر گیرنده یک یا چندین project است. هنگامی که چندین پروژه SSIS، برای ساماندهی پکیج هایتان ایجاد می کنید، این پکیج ها را با هم به یک یا چند solution ترکیب می کنید. مثلاً اگر من چندین پروژه SSIS داشته باشم که هرکدام نمایانگر یک سیستم منبع هستند، که برای populate کردن data warehouse استفاده می شوند، ممکن است یک solution واحد ایجاد کنم تا همه این پروژه های SSIS مرتبط را نگهداری کنم. در ادامه به چگونگی ایجاد یک project و solution، و اضافه کردن یک پکیج SSIS موجود به پروژه خواهیم پرداخت.
برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. روی File، New، Project در منوی بالا کلیک کنید تا پنجره New Project نمایش داده شود. Business Intelligence Projects را بعنوان نوع پروژه، و سپس Integration Services Project را بعنوان template انتخاب کنید؛ فیلدها را مانند شکل زیر پر کنید:
بعد از ایجاد پروژه و سلوشن جدید، می توان به پوشه نمونه (samples folder) در Windows Explorer رفت و شکل زیر را دید:
پوشه نمونه، حاوی فایل سلوشن و پوشه Tutorial-Sample-1 است، که حاوی پروژه من است. پوشه نمونه، در نتیجه کلیک کردن روی Create directory for solution است.
با برگشتن به BIDS، شکل زیر را در Solution Explorerخواهید دید که بالای گوشه راست پنجره وجود دارد:
وقتی یک Integration Services Projectایجاد می کنید، یک پکیج SSIS جدید بطور پیش فرض اضافه می شود؛ می توانید با کلیک راست کردن روی آن نامش را تغییر دهید.
حالا که با موفقیت یک پروژه و سلوشن جدید ایجاد کرده اید، بیایید پروژه جدید دیگری به این سلوشن اضافه کنیم. در منوی File، روی Add، و سپس New Project کلیک کنید، و فیلدها را بصورت زیر پر کنید:
من این پروژه اضافی را ایجاد کرده ام تا پکیج های SSIS را که با اجرای ویزارد Import and Export ایجاد شده است، نگه دارد.این کار، یک پوشه SSMS-Samples ایجاد می کند تا پروژه را نگه دارد؛ این پوشه زیر پوشه نمونه ها است. هنگام ایجاد پروژه های جدید به موارد زیر دقت کنید: مطمئن شوید مکان و نام پروژه همان چیزهایی است که می خواهید. مقادیر پیش فرضی که بطور اتوماتیک در فیلدها وجود دارند، بندرت استفاده می شوند.
حالا وقتی به Solution Explorer درBIDS نگاه می کنیم، شکل زیر را می بینیم:
حالا سلوشن ما حاوی دو پروژه است. ویزاردهای Import و Export، امکان ذخیره کردن کار را بصورت پکیج SSIS می دهد. اگر تصمیم به ذخیره کردن کارتان گرفتید، شاید بخواهید پکیج را به یک پروژه موجود اضافه کنید. روی node پکیج های SSIS، در Solution Explorer کلیک راست کنید، سپس Add Existing Package را انتخاب کنید، و فیلدها را مانند زیر پر کنید:
همانطور که در title پنجره می بینید، شما در حال اضافه کردن یک کپی از پکیج SSIS به پروژه هستید.
مروی بر projectها و solutionها در SSIS
پکیج SSIS شبیه برنامه ایست که بعضی taskهای ویژه را اجرا می کند. یکی از کارهایی که می توانیم با پکیج SSIS دهیم، این است که محتویات جدولی در دیتابیس AdventureWorksDW را به یک Excel spreadsheet کپی کنیم. اینکار را در ابتدای مقاله انجام دادیم و کارمان را بصورت پکیج SSIS ذخیره کردیم. در ادامه آن پکیج را بعنوان راهی برای فهم پکیج SSIS بررسی خواهیم کرد.
برای شروع، BIDSرا با انتخاب SQL Server Business Intelligence Development Studio از گروه برنامه Microsoft SQL Server باز کنید. در منوی بالا، روی File، Open، Project/Solution، کلیک کنید تا پنجره Open Project نمایش داده شود. به مکان سلوشن بروید و روی Open کلیک کنید، مانند شکل زیر:
ند پکیج SSIS را در زیر پروژه SSMS-Samples درSolution Explorer بسط دهید و شکل زیر را خواهید دید:
SQL SERVER آموزش
روی پکیج SSMS-Export-to-Excel.dtsx دابل کلیک کنید و آنرا باز کنید. بیایید نگاهی به جنبه های مختلف پکیج SSIS که در BIDS نمایش داده می شوند، بیندازیم:
Designer
Connection Managers
Toolbox
Properties Window
designer ، ناحیه ای در وسط پنجره است که در زیر نشان داده شده
tabهای زیر در designer قابل دسترس هستند:
تب Control Flow (که در عکس بالا نشان داه شده)، حاوی تسک هاییست که پکیج SSIS اجرا و از تسکی به تسک دیگر flow می کند.
تب Data Flow (که در عکس پایین نشان داه شده)، designer دیگریست که حاوی جزییات data flow task معینی است؛ مثلاً بازیابی داده ها از منبع داده ها، و در مواردی بعضی transformationها را روی داده ها اجرا می کند، سپس آن را روی منبع داده های دیگر می نویسد.
تب Event Handlers، نیز designer دیگریست که در آن می توانید هنگامی که event میعنی raise می شود، تسک هایی را برای اجرا شدن معین کنید.
تب Package Explorer، کل پکیج را بصورت tree-view نمایش می دهد.
شکل زیر، دیزاینر Data Flow برای تسک Data Flow در تب Control Flow است:
پنجره Connection Managers، حاوی منابع داده های مختلف و destinationیی است که از پکیج استفاده می کند:
Connection Managers یکبار تعریف می شوند، و سپس در تسک های مختلفی مانند Execute SQL Task، OLEDB Data، یا OLEDB Destination، ریفرنس می شوند.
Toolbox حاوی تسک هایست که در Control Flow،Data Flow، یا دیزاینرهایEvent Handlers دردسترس هستند. برای ساختن یک پکیج، کافیست تسک هایی را از Toolbox به designer، درگ و آنها را به ترتیبی که می خواهید اجرا کنید، بهم متصل کنید. تسک های Toolbox زیر در تب دیزاینر Control Flow قابل دسترس هستند:
تسک های Toolbox زیر در تب دیزاینر Data Flow قابل دسترس هستند:
Properties Window برای ویرایش و آپدیت کردن پراپرتی های یک تسک در دیزاینر یا یک کانکشن در ناحیه Connection Managers دردسترس است. روی Preparation SQL Task در Control Flow، که در بالا نشان داده شده، کلیک کنید؛ و شکل زیر را در Properties Window خواهید دید:
Preparation SQL Task، یک Execute SQL Task است و برای اجرای عبارات SQL بکار می رود. در اینجا، ما عبارت CREATE TABLE یا stored procedure اجرا می کنیم. وقتی روی یک شی کلیک راست می کنید، یک پنجره popup ظاهر می شود که می توانید برای تنظیم پراپرتی های مختلف استفاده کنید؛ مثلاً روی Preparation SQL Task کلیک راست کنید و Edit را از منو انتخاب کنید و پنجره زیر ظاهر خواهد شد:
SQL SERVER آموزش
