Containers and data streams in SSIS-data conversion (1)

For data streams, converting data to the desired format is a key step in data operations. For example, if you want to obtain the operation result after aggregation and sorting, you can perform the conversion operation. Unlike SQL Server 2000 DTS, these operations do not require script writing, and all operations are performed in the memory. After adding a data conversion, the data is directly converted to the desired format and then passed to the next step. Because the operation is completed in the memory, you do not need to create objects in the database to store these operation functions. However, to process a large volume of data, you still need to create a storage table or other objects for processing.

Drag and Drop a conversion on the data flow panel, click the mouse, and drag and drop a green line to a Destination. Then, you can double-click and edit the conversion. This part explains the conversion from the most basic functions. The next part will perform some experiment operations and use it in the actual uploading environment. There are also some more advanced transformations that will not be explained here, and they will be explained in the next essay. These advanced transformations include:

Some of the conversions are not very complex, but they are not supported by the UI in SSIS. to use them, you need to use the advanced editor in Visual Studio.

Aggregate

Aggregate conversions can aggregate data like the group by, Average, Minimum, Maximum, and Count functions in a T-SQL. As shown in Figure 4-13, the data is grouped by OrderDate and ProductLine to perform the sum operation on LineTotal. In this way, three new columns of data are generated for other operations.

 

Figure 4-16

In the Aggregate operation Editor Aggregate Transformation Editor, select the columns to be aggregated, then, the selected column will appear in the following table and the Alias of the input and Output columns in the Output Alias column. For example, if you want to output the total amount of each order, change it to SubTotal, which makes it easier to identify the purpose of this column. The most important setting is to select an aggregation Operation in the Operation column. It has some options.

Click the Advanced tab to edit the conversion output. On the Advanced page, you can enter the name of the aggregate calculation to generate a new column. On the Advanced Tab page, the most important option is to set the Cache during the conversion so that it can be at an appropriate level. For example, set the lower level to 500000, the middle level to 5000000, and the higher level to 25000000. You can also use the Number of Keys attribute to set a specific value. The Auto Extend Factor attribute sets the amount of memory that can be used for conversion. The default value is 25%. You can also set other options to ensure RAM usage. Warn On Division by Zero single partition is used to process the average value when the divisor is 0. If no selection is made, no prompt will be given if the conversion fails.

Review Conversion

Review conversion allows you to add review data to the data stream. In the past, when HIPPA and Sarbanes-Oxley (SOX) were used, you must track who inserted the data at what time. Review conversion can implement this function. For example, to track the task to insert data into the table, you can add the corresponding columns in the audit conversion. In the Output Column Name Column, enter the Column you want to review. In Audit Type, select Audit Type 4-17. You can select the following types:

 

Figure 4-17