- SQL Server 2017 Integration Services Cookbook
- Christian Cote Matija Lah Dejan Sarka
- 444字
- 2021-07-02 20:41:35
How to do it...
- In SSDT, with your project open, right-click on the Control Flow folder in the Package Parts section of the solution explorer. As shown in the following screenshot, select New Control Flow Package Part from the contextual menu. Rename it Chapter2Part.dtsxp.

- Drag and drop an Execute SQL task on it:
- Rename it sql_Truncate_dbo_CustomLogging.
- Click anywhere in the Control Flow. Set its description property to This is a simple template that is meant to truncate the table [dbo].[CustomLogging].
- As shown in the following screenshot, right-click in the Connection Managers area and choose New OLE DB Connection... from the menu that appears:

- As shown in the following screenshot, select the existing connection (SSISCOOKBOOK\TestCustomLogging in this case) in the top left or create a new one by clicking on NEW.... Click on OK when done to close the Configure OLE DB Connection Manager window.

- Double-click on the sql_Truncate_dbo_CustomLogging task to open the Execute SQL Task Editor. Set the properties in the SQL Statement section as follow:
- ConnectionType: OLE DB, the default value
- Connection: Set it to the connection manager created in the previous step.
- SQLSourceType: Leave the default value, Direct input type the following SQL DML statement in the SQL Statement property:
TRUNCATE TABLE [dbo].[CustomLogging];
It is also shown in the following screenshot:

- Save and close the Chapter2Part.dtsxp package part.
- Now, open the CustomLogging package from the solution explorer. As shown in the following screenshot, there is now a new component in the Package Parts section of the SSIS Toolbox: the Chapter2Part created in the previous steps. Also, notice the description below the Package Parts section. This is the one that we assigned at the Control Flow level of the package part when it was created.

- Drag and drop a Chapter2Part onto the Control Flow of the CustomLogging package. Now to get the same as in the following screenshot:

Execute the following steps:
- Connect it to the dft_dbo_CustomLogging data flow task.
- Select all Control Flow components
- In the Layout toolbar, click Make Same Size
- From the Format menu, select Auto Layout Diagram to properly align the tasks
- Execute the package to make sure that the package part is properly working and stop the package execution when done.
- Double-click on the Chapter2Part to view its properties as shown in the following screenshot. Go to the Connection Managers tab. Locate the ConnectionString property as highlighted in the screenshot. Notice that it has a fixed value. This is the biggest limitation of these parts; there is no way for now to alter any of the property at runtime. We cannot use package configurations or parameters to alter these values dynamically as we can with regular tasks.

推薦閱讀
- 復雜軟件設計之道:領域驅動設計全面解析與實戰
- Java Web基礎與實例教程(第2版·微課版)
- Git高手之路
- 薛定宇教授大講堂(卷Ⅳ):MATLAB最優化計算
- Mastering Scientific Computing with R
- 基于Swift語言的iOS App 商業實戰教程
- 正則表達式經典實例(第2版)
- Web程序設計(第二版)
- Oracle JDeveloper 11gR2 Cookbook
- UVM實戰
- Unity 3D/2D移動開發實戰教程
- 玩轉.NET Micro Framework移植:基于STM32F10x處理器
- 深入實踐DDD:以DSL驅動復雜軟件開發
- 計算機應用基礎(第二版)
- Learning Redux