Understanding Custom Functions and Processes, version 4.6.6.6.


Let's delve deeper into the Functions and Processes dialog box. Above, we have the toolbar with most of the actions already seen in other components. We highlight the "Process Box" action, which transforms a process into an action to be executed by a flow.


These two terms, "Process Box" and "Flow," will be further discussed later in this course. Another possibility is the functionality that allows listing the objects created within the Function and Process, grouped by creation date or modification date. On the main screen, we have the properties of name, description, provider, and whether the component is active or not.


In the center, we can choose the programming language to be used for creating a Function or Process. We can use VB.NET or C Sharp. In the "Source" tab, we have the location where we will create our Functions or Processes.


Although we've been mainly referring to Functions and Processes, in reality, there are seven different types of programs that can be created within the PIS. These program types are mentioned above. The first is the Function, which we used in the previous video.


A Function has parameters as input data, i.e., a list of parameters, and has a single value as output. The Function is used within the transformation to create a new column. But we have others; the Process is a program where we input a list of parameters, and our output is only the value, true or false.


It is used within the Process Box to execute more complex processes, such as data updates in a directory or a very complicated account that will generate an output in a table, and then we will simply communicate whether that process was successful or not. Another type is the Table; it is a program where we also input a list of parameters as input data and the output is a database. It is used as if it were a container, like a data source.


It can be used, for example, as a data source within the Automatic Box. We use it in case we want to create a table that will be part of the ETL or integration process, whose data formation law is complex and can only be constructed using programming. The Target type is the inverse; we input a table and output true or false.


It is also used in the Automatic Box, but it is used as a destination to be written. The Transfer is also used within a transformation. We input one table, and the output is another table, and it is used within transformations.


We will even use the Transfer more later when we are going to load the time dimension. The other type is JSON. I enter not a table but a Dataset.


A Dataset, for those who don't know, is a set of interrelated tables, and our output is a JSON in String format. The JSON type transformation is used when working with Web Services and need to transform the program's output as a JSON. As mentioned, I will then transform a database with several tables into JSONs.


Finally, the last type is Libraries. Libraries are used so that we can create functions or procedures that will be used in other programs. We use them if we have a procedure that I want to reuse in several other components.


Below the screen, we have a set of buttons representing the model of each of the types mentioned above, as well as an example of how we use these programs and a test button, where we can test our program. The model is very important to use when you are going to build a new program. Why? Because in the model, you will see what is mandatory to keep in the program.


This is because if the program is not built correctly, using these mandatory items, the PID will not understand and will not be able to incorporate that program into the integration flow. For example, in the function we developed in the previous video, all these initial declarations, such as the namespace and the class name, must be maintained. If I change these names, the PID will not understand this program.


You can identify these mandatory lines by looking at the models of the other types of programs. At the end, on the right side, we have an "edit" button, which when clicked, we can for example, visualize our program in a larger way. It's easier for you to develop.


And down here, there is a space where I can view the libraries I want to use. To do this, I just click the "add library" button to add the libraries. During programming, my code becomes different colors, highlighting the syntax of the programming I am using.


And for example, in some cases, we can by clicking the button, view the functionalities that we can use, for example, here in the case, for this variable that is of type string. That is, the IDE helps you understand the syntax and already shows you, for example, when you have a certain error, it already shows with a red dash below the error, showing that that command you typed is not compatible with the programming language you are using. By clicking on the "External" tab, I can add references to external DLLs, so that I can use them in our programs.


These DLLs must be compatible with .NET. This allows us to use the full potential of the .NET Framework and its internal and external components in our program. Some native components of the .NET Framework, such as Systems, point, they are already added. To use them, we need to include the component reference in the program code, here at this point, for example, using, when you are going to reference an external component in C Sharp, and, for example, import, when we reference using VB.NET. Note that if I click here, using System, point, it will already show me, for example, System, point, now yes, it will already show me a series of external .NET components that are already available to me.


However, other components that do not appear in this list, you may want to use in your program. In this case, you add the DLL that contains these components in the "External" tab. In the "Libraries" tab, it is where I reference the libraries that I will be using in my program.


These libraries are other Functions and Process components that we create of the Library type and that we will use here in my current program. Prompt Libraries are libraries for accessing four types of databases, SQL Server, MySQL, Oracle, PostgreSQL, and, in fact, the fifth is not a database, but it is ODBC Connection. For each of these four databases and ODBC Connection, we already have a series of methods.


On the left side, the methods in VB.NET and on the right side, the methods in C Sharp, which can be used within your program. These five libraries, four of databases and one of ODBC Connection, they must be associated with a driver of the same type. If we come here in Drivers, we have these five types here.


So I will be able to refer to these Drivers and connect to these Drivers within my program. For example, say I want, in my program, to access a table from a SQL Server database. I then add the SQL Server Prompt Library, and then refer to a driver also of type SQL Server, which is created in Python.


The way the driver connection is passed to the program is an internal way, where you will not need to write the connection string of the program, allowing the access data to this bank to not be exposed. The Parameters tab is used when we are going to test Table, Transfer, and JSON type processes. And of course, to test, we add these parameters using the Param tag, in the same way we already used in other Python components.


The Preview tab has the same actions as a Container Preview and is used so that we can visualize the return, the output of the programs that generate tables. In the case, the Table and Transfer type programs. As the output of these processes also requires the configuration of a Metadata, such as saying the primary keys, foreign keys, fields, types, and sizes, in the Metadata tab, we can visualize the metadata that is valid.


Finally, in the JSON tab, CLEX is used to visualize the output of JSON type programs. I would like to emphasize that Functions and Processes are the most powerful tools in Python. With them, you can implement integration processes using what native and normal Python processes cannot.


Therefore, a deeper knowledge of .NET, especially of the VB.NET and C Sharp languages, will greatly enhance your knowledge in these functionalities. As mentioned, but it's also never bad to repeat, using Visual Studio to create the programs and then transport them here, within Python, is a very important tip, as this will allow you to develop these programs faster and more efficiently because, through Visual Studio's IDE, you will be able to debug and break down your program before transporting it to Python. Thank you, and until the next video.