SSIS and development

SSIS is short for SQL Server intergration Service and a new member of SQL Server 2005. It can be seen from the name that it is used to integrate data, microsoft has to classify Nb into bi feature (Business Intelligence. Like the last vsto, SSIS is not a new thing. in SQL Server 2000 or earlier versions, we have seen its prototype-DTS. In those years, DTS was used to read and write data from various data sources that support ole db. In addition, it was simply used to copy data between different databases. The same is true for basic SSIS content, but SSIS has become much more powerful. For developers, DTS is mostly a tool, and SSIS has evolved into a framework that provides data-related infrastructure such as control flow, data conversion, logs, variables, events, and connection management. Microsoft provides a visual vs-based designer that combines these infrastructures to complete data conversion and integration tasks. The architecture of SSIS can be seen here.

The content above may not be specific enough. Aside from logs, events, or anything, you need to know at least three types of information about data conversion tasks from one data source to another, three types of information can be combined to complete complex tasks, such as source --> conversion 1 --> conversion 2 --> conversion 3 --> target. At this time, the output of the previous conversion is the input of the next conversion. In SSIs, each conversion corresponds to a data flow object, and the data flow object is connected through a logic string to complete the preceding conversion task. Note that the source and target are special data flows. The source only has output, while the target only has input.

Simple conversions can be done with data flow. If the conversion involves branches, loops, and data-independent operations, such as sending emails, data Flow cannot be completed (in terms of design, data flow should not carry all of these miscellaneous functions ). At this time, another group of objects-control flow. It completes the above "Miscellaneous" task. control flow has a special object that can contain some data flow objects, so that control flow can do the right thing. That is to say, data flow has become a part of control flow, and is the most important part-imagine that your entire task is a complex task, but there is no data conversion. Isn't it a serious task? All these flows are combined into a task called a package in SSIs ).

The above is a little trivial. Let's summarize it. A data integration task is called a package in SSIs. When you need to complete a data integration task, create a package and then create the components/objects of control flow, and tell the specific process of the SSIS task by connecting them. One or more steps in this process are about data conversion. In this case, you need to establish a data flow object and connect these objects to tell SSIS how to perform this data conversion. In this case, we can make an analogy. Do you know the workflow? Control flow is equivalent to a workflow tailored to data integration. Each step corresponds to a control flow object. The steps related to data conversion are a bit special. Although it is also a control flow object, it contains some data flow objects to describe/provide specific algorithms for data conversion.

Through the above mechanism, SSIS provides a powerful data integration framework. The problem is that it is powerful and flexible, but easy to use? You cannot expect every user to write a program to assemble the data flow and control flow and then throw it to SSIs for running. What if someone else doesn't know how to write a program? The solution provided by Microsoft is a wizard + visual designer. The wizard encapsulates some simple and common data integration tasks. You only need to answer some questions and the wizard will automatically generate the corresponding packages. For advanced users, the visual designer of SSIS allows users to drag and drop to generate and connect data flow and control flow.

The following is a little bit about custom development. SSIS does provide a lot of ready-made and comprehensive data flow and control flow operations, but what if customers want to embed their own logic into the entire integration process? SSIS also provides a solution: Create a class that inherits from a specific interface/class, and encapsulate it into. Net assembly or COM components into a package for SSIs runtime to call.

To customize your data flow component, you must inherit from Microsoft. sqlserver. DTS. pipeline. pipelinecomponent. Here you can find detailed help. For control flow, you need to inherit from Microsoft. sqlserver. DTS. runtime. task, which is helpful here. Of course, more than just these things that SSIS can customize, including connection managers, logs, and for loops.

You must pay attention to the customization of data flow and control flow. First, you must note that the functions inherited from the base class are called in design-time (design time, those are called at runtime. You need to consider that your objects can be used normally in the visual designer. In addition, pay attention to the trigger mechanism of events, messages, progress, etc.-not throw exception or delegate, but by calling the corresponding method of passing in the componentevents parameter or events. In addition, because data flow involves specific data conversion, the structure of the associated classes and pipelinecomponent itself is a bit complicated. Control flow is much simpler.

I have written so much, but I hope you can get to know about SSIS through this article. Do not get confused by official and formal language of msdn. If you are confused by this article ~~~ ~~~ I cannot -_-