انتقال داده ها از Sharepoint به SQL Server و چالش های آن

چگونه داده ها را از sharepoint به SQL Server بارگذاری کنیم؟ 

انتقال داده ها از یک مکان به مکان دیگر همیشه چالش های خاص خود را دارد. هنگامی که داده ها در سرور شیرپوینت قرار دارند ، پیچیدگی های بیشتری در این زمینه وجود خواهد داشت. این چالش ها عمدتاً موضوعات مرتبط با امنیت است.

شروع:
سناریو نسبتاً ساده ای است. کلاینت دارای تعداد زیادی از sheetهای اکسل در یک اینستنس یا سرور SharePoint است. داده های موجود در یک (یا بیشتر) از این شیت ها به صورت روزانه به روز می شود و کلاینت می خواهد داده های یک شیت خاص ، با یک فرآیند شبانه بارگیری شود. نمای سرور شیرپوینت را می توان در زیر مشاهده کرد:

SSIS و داده های Sharepoint ما

کارآمدترین و موثرترین روش برای ورود داده های ما به SQL Server از طریق SQL Server Integration Services است. از اینرو در Visual studio یک پروژه جدید SSIS ایجاد میکنیم. بعد  یک  “Data Flow Task” را به سطح اضافه می کنیم . این به ما این امکان را می دهد که علاوه بر تعیین مقصد برای داده های خام ، منبع داده های خود را نیز تعریف کنیم. سپس روی این کامپوننت دبل کلیک کرده تا به صفحه طراحی Data Flow منتقل شویم. در اینجا ما باید داده های منبع خود را تعریف کنیم و همچنین مقصد پایان داده های خود را نیز تعریف کنیم.از آنجا که منبع داده ما یک شیت اکسل است ، ما باید “Excel Data source” را ایجاد و پیکربندی کنیم.پس آن کامپوننت را از جعبه ابزار سمت چپ به صفحه کار می آوریم.

از آنجا که منبع داده ما تحت وب است ، باید منبع داده خود را به روشی متفاوت از یک فایل اکسل مبتنی بر دیسک معمولی پیکربندی کنیم.علاوه بر این ، لینک منبع داده باید در قالب UNC باشد.

مثلا : 

//sharepoint.xxx.internal\Community\4dx\SalamiSQL\Template1.xlsx

ابتدا روی دکمه Browse (شکل زیر) کلیک می کنیم . Explorer Window باز می شود و ما پیوند SharePoint با قالب UNC خود را در کادر نام پرونده جایگذاری می کنیم.

اکنون بر روی “open” کلیک کرده و بلافاصله توسط اطلاعات SharePoint ما برای اعتبارنامه های امنیتی به چالش کشیده می شویم.

ما به SharePoint اطلاع می دهیم که چه کسی هستیم (بالا را ببینید) و ok را کلیک میکنیم. ما به صفحه Excel Connection Managed خود بازگشتیم . حال با دوبار کلیک بر روی کنترل excel data source خود به ما امکان می دهد شیت منبع حاوی داده های خود را برای وارد کردن انتخاب کنیم. زمانی که پنجره اش باز شد باید شیت موردنظر خود را همانند شکل زیر انتخاب کنیم:

پس از آنتخاب میتوانیم در صفحه پدیدار شده به تب columns در سمت چپ پنجره برویم تا از آنجا ستونهای شیت ما را به ما نشان دهد:

ok را زده و به صفحه طراحی خود برمی گردیم. و بعد به تنظیم کامپوننت برای داده های مقصد که یک دیتابیس در SQL Server است،  مبادرت می ورزیم. از اینرو ابتدا یک OLEDB Connection ایجاد میکنیم و پس از آن یک کامپوننت OLE DB Destination به صفحه خود اضافه میکنیم و سورس را به آن متصل میکنیم و به تنظیمات آن می پردازیم.  (سرور و دیتابیس و جدول خود را انتخاب میکنیم و بین عناصر ستونهای شیت اکسل و فیلدهای متناظر آن در جدول دیتابیس Mapping انجام میدهیم). و درنهایت ok را می زنیم. برای هر تعداد شیت دیگر که داشته باشیم باید همین رویه را که شرح دادیم انجام دهیم. نکته: تا اینجای پروژه را خودتان میتوانید جریان داده را از منبع به مقصد و بارگذاری آنرا با افزودن taskهایی مانند Execute SQL و دیگر کنترل ها آزمایش کرده و آنرا Debug کنید.

اگر ما process ID را disconnect کنیم و بگذاریم در پس زمینه آن اجرا شود، در می یابیم که رمز و توکن امنیتی دست نخورده باقی می ماند.

اکنون کار “واقعی” آغاز می شود !! کار با شیرپوینت به دلیل مسائل امنیتی اغلب چالش برانگیز است. از آنجا که ما می خواهیم هر روز عصر از طریق پکیج خود داده ها را از سایت شیرپوینت بگیریم ، مهم است که شیرپوینت  job زمانبندی شده ما را در زمان اجرا  به چالش نکشد ،  در حالی که اجرای شبانه با شناسه فرآیند و با رمز عبور (که منقضی نمیشود) پردازش میشود، احراز هویت SharePoint نیز وجود دارد که باید مورد توجه قرار گیرد. تعداد کمی از سایتهای سرویس گیرنده امنیت، هر دایرکتوری شیرپوینت را حذف خواهند کرد. در دنیای واقعی این یک چالش ایجاد میکند. به محض آنکه شناسه فرآیند log on شد و پکیج اجرا شد ، برای شناسه کاربری و رمز عبور چالش شروع خواهد شد. 

یکی از مواردی که پس از تأیید اعتبار صحیح متوجه آن میشویم اینست که می توانیم پکیج را اجرا کنیم. می توانیم پکیج را بارها و بارها و بدون ایراد گرفتن ، اجرا کنیم. بعداً ، هنگام بارگیری پکیج در SQL Server از طریق گزینه Integration Services از Management Studio ، ما می توانیم پکیج را بدون مشکل اجرا کنیم تا زمانی که از سیستم خارج نشویم. اما این مساله برای SQL Server Agent که با همان process ID اجرا میشود، متفاوت خواهد بود.SQL Server Agent از طریق شناسه فرآیند SAME که در بالا بحث شد ، اجرا می شود. پکیج توسط process ID توسعه یافته است. تست و دیباگ نیزبا همان شناسه فرآیند انجام میشود.

برای شیرپوینت نیز مشخص کردیم که شناسه فرآیند می تواند تمام حقوق لازم را در شیت داشته باشد.
با این اوصاف ، انتظار می رود که با نقض دسترسی  کمی روبرو شوید.

همانطور که پیشتر گفتیم هنگامی که process ID در حالت قطع ارتباط باقی ماند ، در حالت agent خوب  عمل نکرد پس ما بجای سرویس ایجنت به سراغ windows task scheduler می رویم که بتوانیم process ID را غیرفعال کنیم تا کار جلو برود. برای انجام این کار به یک رشته اجرایی احتیاج دارید تا پکیج را فراخوانی کرده و آن را اجرا کنید. یکی از ابزارهای عالی موجود در  SQL Server، ابزار DTEXECUI است. فراخوانی آن از طریق خط فرمان ویندوز است. سپس اولین صفحه ضبط داده ظاهر می شود.

ابتدا تب General را تنظیم میکنیم. توجه داشته باشید که دو ویژگی حیاتی Package Source و محل پکیج هستند. ما به تب “connection manager” میرویم تا  نگاهی به کانکشن های خود بیندازیم. ما  excel connection را انتخاب می کنیم (تیک آن را می زنیم). بعد به تب command line می رویم (توجه کنید که یک خط دستور رشته ای ساخته شده است). این دستور را کات کرده و آنرا در یک flat file با پسوند .bat کپی مکینیم  کلمه DTEXEC.exe را ابتدای آن دستور اضافه میکنیم و فایل را ذخیره می کنیم. رشته همانند رشته زیر است:

DTEXEC.exe /FILE “\”C:\SharePointTest\SharePointTest\GoalLoad.dtsx\”” /CONNECTION “\”Excel Connection Manager\””;”\”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sharepoint.xxx.internal\Community\4dx\SalamiSQL\Template1.xlsx;Extended Properties=\”\”EXCEL 12.0 XML;HDR=YES\”\”;\”” /CHECKPOINTING OFF /REPORTING EW

برنامه ریزی کار شبانه ما در Task Scheduler !!!

برای شروع Task Scheduler ، فقط باید کلمه “Task” را در صفحه جستجو تایپ کنید تا این برنامه باز شود. سپس در پنجره باز شده ما “create task” را از منوی گوشه سمت راست بالای صفحه نمایش در بالا انتخاب می کنیم. تب “General” در “create task” ظاهر می شود.اکنون بر روی تب “Trigger” کلیک می کنیم که تب دوم در پنجره ورودی “Create Task” است. اینجاست که باید زمان اجرای job خود را برنامه ریزی کنیم. نام jobای که باید اجرا شود در بخش تب بعدی “action” آمده است. 

تنظیم فایل بچ ای که باید اجرا شود
اکنون باید به سیستم بگوییم کدام پرونده را اجرا کند. همانطور که ما این کار را از طریق Windows Server Task Scheduler انجام می دهیم ، ما فایل SQLShack.bat (که شامل رشته خط فرمان است) را انتخاب می کنیم.

ابتدا تب «action» را از صفحه «create task» انتخاب کرده و «new action» را انتخاب می کنیم. اکنون منوی «new action» ظاهر می شود. به طور پیش فرض “start a program” ظاهر  خواهد شد.

ما به سادگی نام بچ فایل خود را وارد می کنیم (به بالا مراجعه کنید) و همه ما پس از کلیک مجدد بر روی ok و ok مجدد کار ما تمام می شود. job زمانبندی شده شما در لیست jobهای موجود در قسمت میانی بالای صفحه نمایش داده شده در بالا نشان داده می شود. با “کلیک راست” روی job خود ، می توانید آن را اجرا کنید تا صحت همه را تأیید کنید.