SSIS data stream component development (2) reprint

To develop a data stream component, you must implement the runtime (idtsruntimecomponent90) and design-hour (idtsruntimecomponent90) interfaces for the component. The runtime Interface contains the metadata of the component and the execution method of the component. The design interface contains the method used when you edit the component in bids.

In fact, as long as we inherit the pipelinecomponent class, the override class method can have the components we want to develop.

Simple Example

Create a new class library project in. Then, reference four sets in the project:

1. Microsoft. sqlserver. pipelinehost

2. Microsoft. sqlserver. dtspipelinewrap

3. Microsoft. sqlserver. manageddts

4. Microsoft. sqlserver. dtsruntimewrap

Create a class that inherits pipelinecomponent:

The dtspipelinecomponent statement is followed by an attribute in the dtspipelinecomponentattribute class, which defines the component name, type, icon, whether an advanced editor is available, and the editing interface. The custom editing interface will be discussed later.

Then, create a signature for the Assembly.

Install Components

After debugging by pressing F5, find the compiled library file in the bin \ debug \ folder of the project. The library file compiled here is dataflowcomponent_1.dll, copy the library file to <SQL server instance installation path> \ 90 \ DTS \ pipelinecomponents. Then install the Assembly in GAC. You can drag the library file to the <system drive letter> \ windows \ assemble, alternatively, enter gacutil/I "<project folder> \ bin \ debug \ dataflowcomponent_1.dll" in the command line"

Create an SSIS package in bids. In this case, you cannot see the newly developed components in the data flow design toolbox. Right-click the toolbox and select "select item ".

Add development components

Then, you can find this component in the data flow design toolbox, add the component to the data flow, and double-click it. Because the editing interface is not designed for the component, the advanced editor is opened by default.

Although this component can be executed in the data stream, it does not work at all, because we have not defined the work during its execution.

During development, you often need to test the new version in bids. Every time you copy the library file to the SQL folder, It is very troublesome to install it in GAC.

The component can be automatically installed after compilation is successful in the project generation event. Select "compile" -- "generate events..." -- "events generated after compilation"

Assume that both your system and SQL are installed in the default path under C:, and enter:
"C: \ Program Files \ Microsoft Visual Studio 8 \ SDK \ V2.0 \ bin \ gacutil.exe"-u dataflowcomponent_1.dll
Copy dataflowcomponent_1.dll "C: \ Program Files \ Microsoft SQL Server \ 90 \ DTS \ pipelinecomponents \ dataflowcomponent_1.dll"
"C: \ Program Files \ Microsoft Visual Studio 8 \ SDK \ V2.0 \ bin \ gacutil.exe"-I "C: \ Program Files \ Microsoft SQL Server \ 90 \ DTS \ pipelinecomponents \ dataflowcomponent_1.dll"

In this way, the program assembly is automatically installed after each compilation.
However, if you enable two VS 2005 components, one is writing the data stream component code and the other is used to design the data stream. You want to try it out in the data stream designer immediately after the code is compiled, first, a later version of the Assembly is required for each compilation.

For example, if the previous version is 1.0.0.0, 1.0.0.1 will be used for code re-compilation after the code is modified. After compilation, the component will be deleted from the toolbox of the data flow design interface and then added.

Test "design time"

Generally, two vs instances are enabled. One is used to compile the component code, and the other is called vs_a. The other is called vs_ B in the data flow design interface.

On the menu bar of vs_a, choose "debug"> "attach to process ..."

 

Add vs_ B

Then add the component to vs_ B. You can set breakpoints in vs_a. All events and variables of the component can be captured in vs_a.

Test "RunTime"

Suppose we want to test the "ADO source VB" component in the data stream (this component is the sample provided by SQL Server 2005), and add a script component to the vs_ B data stream, the script component code is as follows:

Public class scriptmain
Inherits usercomponent

Public overrides sub input 0_processinputrow (byval row as input 0 buffer)
'
'Add your code here
'
End sub

Public overrides sub preexecute ()
Msgbox ("begin debug ")
Mybase. preexecute ()
End sub

End Class

This script component aims to enable an msgbox during Data Execution to interrupt the data stream.

After msgboxappears, add vs_ato dtsdebughost.exe

Set the breakpoint when the component is executed, and then continue the data stream ......