Writing a custom data flow component (dataflow component) for SSIS: Data Source component

In the previous article, we talked about a simple step, including creating a project, deploying and testing. In this section, we will first discuss the design of data source components.

1. Add several references. Make sure that the four references shown in are added and the corresponding using statements are added.

[Note] I have modified the component name to better express my purpose. It is now a "folder data source" to read the file information of a folder. Do you have such a demand?

 

 

2. Add output column Definitions

As a data source, it is clear that it needs to define which columns are output. So where is this defined? What we need to do is override the providecomponentproperties method.

Public override void providecomponentproperties () {componentmetadata. runtimeconnectioncollection. removeall (); removeallinputsoutputsandcustomproperties (); idtscustomproperty90 folder = componentmetadata. custompropertycollection. new (); folder. name = "folder"; // how to set this attribute so that it can bind expressions? Idtsoutput90 output = componentmetadata. outputcollection. new (); output. name = "myoutput"; // when initializing, prepare several output columns: idtsoutputcolumn90 filenamecolumn = output. outputcolumncollection. new (); filenamecolumn. name = "FILENAME"; filenamecolumn. setdatatypeproperties (datatype. dt_wstr, 256, 0, 0, 0); // codePage is required if it is not Unicode. Other types can be set to 0 idtsoutputcolumn90 filesizecolumn = output. outputcolumncollection. new (); Files Izecolumn. name = "filesize"; filesizecolumn. setdatatypeproperties (datatype. dt_i8, 0, 0, 0, 0); idtsoutputcolumn90 fileextensioncolumn = output. outputcolumncollection. new (); fileextensioncolumn. name = "fileextension"; fileextensioncolumn. setdatatypeproperties (datatype. dt_wstr, 50, 0, 0, 0); // how can I prevent users from editing the output and column information?}

In this method, we added a custom attribute to increase the flexibility of the component. At the same time, we have added a specific output with three columns.

3. Add verification rules for components

Because Custom Attributes exist, we should verify them before execution to ensure that they are correctly set.

Public override dtsvalidationstatus validate () {idtscustomproperty90 folder = componentmetadata. custompropertycollection ["folder"]; If (Folder. value! = NULL & directory. exists (folder. value. tostring () {return dtsvalidationstatus. vs_isvalid;} else {componentmetadata. firewarning (0, "folder data source", "the target path cannot be blank and must exist", String. empty, 0); Return dtsvalidationstatus. vs_isbroken ;}}

In this method, make sure that the folder attribute is set and must be a valid path.

4. After the above preparations, we can enter the actual problem, that is: how can we read all the file information based on the folder settings and output it to the subsequent pipeline components?

        public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)        {            IDTSCustomProperty90 folder = ComponentMetaData.CustomPropertyCollection["Folder"];            string path = folder.Value.ToString();            IDTSOutput90 output = ComponentMetaData.OutputCollection["MyOutput"];            PipelineBuffer buffer = buffers[0];            try            {                foreach (var item in Directory.GetFiles(path))                {                    buffer.AddRow();                    FileInfo info = new FileInfo(item);                    buffer[0] = info.Name;                    buffer[1] = info.Length;                    buffer[2] = info.Extension;                }            }            finally            {                buffer.SetEndOfRowset();            }                    }

Great. We have completed the data source design. Of course, so far its functions are very simple.

5. Generate and deploy the project.

6. Test in Bi Studio

When we drag it from the toolbox to the data source design view, there is a warning icon on it, because it has not been configured

Right-click the component and choose edit.

Our folder attributes are automatically displayed in "Custom Attributes ".

We can also see the defined columns on the "Input and Output attributes" page.

All right, we should have read it all. Now, enter a path in the folder attribute, for example, C: \ WINDOWS

Click "OK" to complete the configuration. Our data source can provide data.

Note: If the entered path does not exist, the verification fails.

To make this example more interesting, we add a "sort" conversion (sort by file size in descending order), and add an Excel Data target as the recipient. These two components are standard and I will not go into details here

 

Next, we can execute the task.

Great. We successfully executed the task. Now let's take a look at the Excel file output.

 

Summary: This article explains how to design the simplest data source component. It can read the file information of a folder. The steps for designing a data source are as follows:

• Prepare the attributes of the component (generally custom attributes, custom outputs, and columns)
• Write verification rules
• Override Output Method

Author: Chen xizhang at 9:50:10
Released in: http://www.cnblogs.com/chenxizhang/
This article is copyrighted by the author and can be reproduced. However, this statement must be retained without the author's consent, and the original article is clearly connected on the article page. Otherwise, the legal liability will be retained.
For more blog articles, as well as the author's complete comments on Blog references and the policy of cooperation, please refer to the following site: Chen xizhang's blog Center