官术网_书友最值得收藏!

How to do it...

  1. 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.
  1. Drag and drop an Execute SQL task on it:
    1. Rename it sql_Truncate_dbo_CustomLogging.
    2. 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].
    3. As shown in the following screenshot, right-click in the Connection Managers area and choose New OLE DB Connection... from the menu that appears:
  1. 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.
  1. 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:

  1. Save and close the Chapter2Part.dtsxp package part.
  2. 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.
  1. 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:

  1. Connect it to the dft_dbo_CustomLogging data flow task.
  2. Select all Control Flow components
  3. In the Layout toolbar, click Make Same Size
  4. From the Format menu, select Auto Layout Diagram to properly align the tasks
  5. Execute the package to make sure that the package part is properly working and stop the package execution when done.
  6. 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.
主站蜘蛛池模板: 会东县| 泸州市| 东乌| 兴业县| 伽师县| 汪清县| 界首市| 大名县| 尉犁县| 凤凰县| 太和县| 新绛县| 长葛市| 文登市| 寻乌县| 辽宁省| 时尚| 两当县| 洛宁县| 东宁县| 呈贡县| 大邑县| 金平| 青州市| 富蕴县| 万安县| 绿春县| 宜昌市| 康马县| 兰溪市| 祁东县| 宁明县| 广元市| 本溪| 壶关县| 建瓯市| 灵宝市| 南宁市| 开阳县| 乐业县| 寻乌县|