金屬零件圖像數據集
Whilst discussing the evolution of SQL Server Data Tools in my previous article, I touched on a new feature, Package Parts, which is available in SSDT 2015. This article takes an in-depth analysis of the Package Parts feature, particularly, as it relates to Data Warehouse development.
在上一篇文章中討論SQL Server數據工具的演變時，我談到了SSDT 2015中可用的新功能Package Parts。本文對Package Parts功能進行了深入分析，尤其是與之相關的到數據倉庫開發。
There are various approaches to designing a reusable SSIS package, for instance, you could create your own package template, implementing parent-child package execution using Execute Package Task, or simply copy and paste an existing package. However, just because your SSIS solution is reusable doesn’t necessarily mean that it’s easily maintainable too. Sometimes as an ETL developer, you need to make changes to your SSIS solution because of structural changes from source, change of connection string etc. and depending on how you have designed your solution, this process of refactoring your ETL could range from bearable to agonising. In the following sections of this article, we discuss and illustrate how Package Parts can be used to create reusable and maintainable SSIS solutions that could later make your ETL refactoring experience, very pleasant.
數據倉庫模型， 設計可重復使用的SSIS包的方法有多種，例如，您可以創建自己的包模板，使用“執行包任務”實現父子包執行，或者簡單地復制并粘貼現有包。 但是，僅僅因為您的SSIS解決方案是可重用的，并不一定意味著它也易于維護。 有時，作為ETL開發人員，由于源代碼的結構更改，連接字符串的更改等，您需要對SSIS解決方案進行更改，并且根據您設計解決方案的方式，重構ETL的過程可能從可承受到痛苦。 。 在本文的以下各節中，我們討論并說明如何使用“打包部件”來創建可重用和可維護的SSIS解決方案，這些解決方案以后可以使您的ETL重構體驗非常愉快。
In order to understand and appreciate the Package Part feature in SSIS 2016, I will demonstrate some of the limitations we encountered in the traditional package template. A package template allowed you to create a package with shared controls that can later be used as a template for creating other packages. Say for instance you have a requirement to setup a data mart that stores fruit-sales data. Such a data mart might contain the following fact and dimensions:
為了理解和欣賞SSIS 2016中的Package Part功能，我將演示我們在傳統包模板中遇到的一些限制。 包模板允許您創建帶有共享控件的包，這些控件以后可以用作創建其他包的模板。 假設您需要設置一個存儲水果銷售數據的數據集市。 這樣的數據集市可能包含以下事實和維度：
Populating the abovementioned dimensions may involve similar processes (i.e. use Execute SQL Task to clear staging table, generate surrogate keys, send out error emails) in which case creating a package template would be useful. A typical package template that can be used to load the fruit-sales data mart is shown in Figure 1.
填充上述維度可能涉及類似的過程（即，使用Execute SQL Task清除登臺表，生成代理密鑰，發送錯誤電子郵件），在這種情況下，創建程序包模板將很有用。 圖1顯示了可用于加載水果銷售數據集市的典型包裝模板。
外協零件包裝要求，
The next part involves saving and copying the package template developed into a drive location where Visual Studio is installed. The folder that you need to copy the template to is called DataTransformationItems and depending on your version of Visual Studio installed (in my case, I am using Visual Studio 2013), the path of the drive location should be as follows:
下一部分涉及將開發的包模板保存并復制到安裝Visual Studio的驅動器位置。 您需要將模板復制到的文件夾稱為DataTransformationItems，并且取決于安裝的Visual Studio版本（在我的情況下，我使用的是Visual Studio 2013），驅動器位置的路徑應如下所示：
{Your Drive Letter}:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\PrivateAssemblies\ProjectItems \DataTransformationProject\DataTransformationItems
{您的驅動器號}：\ Program Files（x86）\ Microsoft Visual Studio 14.0 \ Common7 \ IDE \ PrivateAssemblies \ ProjectItems \ DataTransformationProject \ DataTransformationItems
Pascal數據集圖像類別有多少、 Once you have located the correct drive location, then copy and paste the package template into the folder as shown in Figure 2.
找到正確的驅動器位置后，然后將包模板復制并粘貼到該文件夾??中， 如圖2所示。
After you have closed and re-launched SSDT, the next time you add a new item into an SSIS project, the package template you added in Figure 2 will come up as one of the template options for your new item as shown in Figure 3.
關閉并重新啟動SSDT之后，下次將新項目添加到SSIS項目中時，在圖2中添加的包模板將作為新項目的模板選項之一出現， 如圖3所示。
圖像數據集構建手動。
Now that I have a template, we can use it to create the rest of the packages that will load our fact and dimensions for the fruit-sales data mart. The complete ETL for loading our data mart is shown in Figure 4.
現在我有了一個模板，我們可以使用它來創建其余的程序包，這些程序包將為水果銷售數據集市加載我們的事實和維度。 圖4中顯示了用于加載數據集市的完整ETL。
The greatest limitation with the traditional package template comes in when we need to refactor our SSIS solution. Say for instance we later realise that for some reasons our staging database has been changed from AdvetureWorks2014 to ReportServer. Well, to ensure that any packages created using the template has the correct database reference, we will update our package template such that the OLE DB connection shows the correct database as shown in Figure 5.
數據包裝、 當我們需要重構我們的SSIS解決方案時，傳統軟件包模板的最大局限就出現了。 例如，我們稍后意識到由于某些原因，我們的登臺數據庫已從AdvetureWorks2014更改為ReportServer 。 好了，為了確保使用該模板創建的所有軟件包都具有正確的數據庫引用，我們將更新我們的軟件包模板，以使OLE DB連接顯示正確的數據庫， 如圖5所示。
To check that the ensure is effective, I created a new dimension called SomeNewDimension.dtsx and as it can be seen in Figure 6, SomeNewDimension.dtsx has ReportServer connection name instead of AdvetureWorks2014.
為了檢查確保有效，我創建了一個名為SomeNewDimension.dtsx的新維度，如圖6所示， SomeNewDimension.dtsx具有ReportServer連接名稱而不是AdvetureWorks2014 。
圖像數據庫， But, what happens to packages created with an older version of the template? Will they still have references to the old connection? The answer is, unfortunately, yes – as shown in Figure 7.
但是，使用較舊版本的模板創建的包會怎樣？ 他們還會參考舊的連接嗎？ 不幸的是，答案是肯定的- 如圖7所示。
This means that in order to ensure that the old packages have the correct connection, we will have to open each individual package and update the connection (that’s where the pain comes in, as in a case of a solution with 100 packages would mean opening and editing each package). Another alternative would be to delete all old packages and recreate them using the new template – either way, you will have to manually work on those 100 packages!
這意味著，為了確保舊程序包具有正確的連接，我們將不得不打開每個單獨的程序包并更新連接（這就是麻煩所在，例如，對于具有100個程序包的解決方案而言，這意味著打開和編輯每個程序包）。 另一種選擇是刪除所有舊軟件包，然后使用新模板重新創建它們-任一種方式，您都必須手動處理這100個軟件包！
圖像分類數據集？ Package Parts enable us to produce package templates that are easier to manage and reuse. As from SSDT 2015, every SSIS project has a new Package Parts node, which in turn, has a Control Flow sub-node, as shown in Figure 8.
套件零件使我們能夠生產易于管理和重復使用的套件模板。 從SSDT 2015開始，每個SSIS項目都有一個新的Package Parts節點，該節點又具有一個Control Flow子節點， 如圖8所示。
It is in this Control Flow sub-node that we get to create Package Parts by either adding an existing file-based Package Part or simply create one from scratch, as per options shown in Figure 9.
通過在這個Control Flow子節點中，我們可以通過添加現有的基于文件的Package Part或僅從頭開始創建一個Package Part來創建Package Parts， 如圖9所示。
醫學圖像數據集。
Working on Package Parts is similar to the way you work on SSIS Packages except that Package Parts have their own file extension, dtsxp. In Figure 10, we have created a Package Part that mirrors the traditional package template that was created earlier on.
處理軟件包部件的方法與處理SSIS軟件包的方法類似，不同之處在于軟件包部件具有自己的文件擴展名dtsxp 。 在圖10中 ，我們創建了一個Package Part，它反映了之前創建的傳統Package模板。
Now that we have created out Package Part, we have to add it in an SSIS Package. Unfortunately, you cannot just click and drag the Package Part from Solution Explorer into the SSIS Package’s Control Flow area, instead as soon as you have created and saved the Package Part, it will come up under Package Parts node in SSIS Toolbox, as shown in Figure 11 – wherein you can click and drag it onto the SSIS Package’s Control Flow area.
HRRP圖像數據集。 現在我們已經創建了Package Part，我們必須將其添加到SSIS Package中。 不幸的是，您不能僅從“解決方案資源管理器”中單擊并拖動“包裝零件”到SSIS包裝的“控制流”區域，而是一旦創建并保存了包裝零件，它就會出現在SSIS工具箱中的“包裝零件”節點下，如下圖所示。 圖11 –您可以單擊并將其拖動到SSIS包的“控制流”區域。
Figure 12 shows an SSIS Package that uses a Package Part. As it can be seen, Package Part’s component have a letter P on the right hand corner of that component’s name.
圖12顯示了使用Package Part的SSIS Package。 可以看出，“包裝部件”的組件在該組件名稱的右上角有一個字母P。
圖像分割數據集？ Now to demonstrate as to how changes to the Package Parts gets applied across packages referencing it, we will attempt to make changes to the Package Parts’ connection name. The existing version of the Package Part has the AdvetureWorks2014 connection name, as can be seen in Figure 13 from one of the SSIS Packages that are referencing our Package Part, PackageTemplate.dtsxp.
現在，為了演示如何在引用它的包中應用對包部件的更改，我們將嘗試對包部件的連接名稱進行更改。 軟件包部件的現有版本具有AdvetureWorks2014連接名稱， 如圖13所示 ，其中一個SSIS軟件包引用了我們的軟件包部件PackageTemplate.dtsxp 。
Once the connection name of PackageTemplate.dtsxp is changed to ReportServer, we can see that such a change has been applied on SSIS Packages referencing the Package Part as shown in Figure 14.
將PackageTemplate.dtsxp的連接名稱更改為ReportServer后 ，我們可以看到這種更改已應用到引用Package Part的SSIS Packages上， 如圖14所示。
數據倉庫中常見的模型，
One of the things you would have noticed when we replicated the traditional package template into Package Parts was that in the Package Part we had grouped all the executables into a Sequence Container. This is because, although you can get away with adding multiple executables into a package Part without using a Sequence Container, when you attempt to add them from SSIS Toolbox you run into an error message displayed in Figure 15 as apparently Package Parts with multiple executables cannot be added into an SSIS Package.
當我們將傳統的程序包模板復制到“程序包部件”中時，您會注意到的一件事是，在“程序包部件”中，我們將所有可執行文件分組到一個“序列容器”中。 這是因為，盡管無需使用序列容器即可將多個可執行文件添加到程序包部件中，但是當您嘗試從SSIS工具箱中添加它們時，卻遇到了如圖15所示的錯誤消息，因為帶有多個可執行程序的程序包部件顯然無法被添加到SSIS包中。
I can almost hear what you are thinking, “So Microsoft, why allow me to create such a Package Part with multiple executables outside the Sequence Container, if you are going to prevent me from using it?” Personally, I think this is a bug!
我幾乎可以聽到您的想法：“那么，Microsoft，為什么要阻止我使用這樣的程序包部件，使其在Seq??uence Container之外具有多個可執行文件？” 我個人認為這是一個錯誤！
圖像識別數據集，
Remember how a change to the Package Part gets applied across all packages who references that Package Part, well, if one of the SSIS Packages that references the Package Part is opened at the time of editing the Package Part, then when you switch context back to that SSIS Package, you will run into a message displayed in Figure 16. It basically means that you will have to manually refresh all SSIS Packages that were opened during updating of the Package Part. So make sure that you close all packages prior to making changes to Package Parts.
請記住，如果在編輯“包裝件”時打開了一個引用“包裝件”的SSIS包，那么對“包裝件”所做的更改將如何應用到所有引用該“包裝件”的包中。該SSIS包，您將遇到圖16中顯示的消息。 基本上，這意味著您必須手動刷新在更新軟件包部件期間打開的所有SSIS軟件包。 因此，請確保在更改“包裝零件”之前關閉所有包裝。
The rest of the limitations of Package Parts include the following:
小零件包裝方式， 封裝件的其他限制包括：
Package Parts introduces another alternative to creating reusable and maintainable SSIS solutions. Unfortunately, it has its own sets of shortcomings which I hope will be resolved in subsequent releases of SQL Server.
套件零件介紹了創建可重用和可維護的SSIS解決方案的另一種方法。 不幸的是，它有其自身的缺點，我希望可以在后續版本SQL Server中解決。
翻譯自: https://www.sqlshack.com/how-to-use-package-parts-to-develop-easily-maintainable-data-warehouse-solutions/
金屬零件圖像數據集
版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间：8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态
![[译]编写高性能对垃圾收集友好的代码](/upload/rand_pic/2-481.jpg)
