Containers and data streams in SSIs-Example of data conversion tasks

In the previous article, we were familiar with data flow tasks. Now we will give an example to learn about these content through practice. In the hosts file.

2. Create a new package and rename it as adventureworksextract. dtsx. Drag and Drop a Data Flow task in control flow and double-click it to enter the data flow interface.
4. Drag and Drop an ole db data source on the data flow interface, right-click to rename it as transactionhistory, and double-click to open the editing interface. If the adventureworks database connection already exists, select it directly. If it does not exist, click Create a connection.
6. After the connection is created, return to the ole db source Editor interface. The default data access mode option is table or view. Select the table [production]. [transactionhistoryarchive] In the database. Figure 4-32
8. Click column Page4-33 select columns productid, quantity, and actualcost, click OK to exit Figure 4-33
10. Drag and Drop a derived Column task from the toolbar Data Flow transformations to generate a new derived column, rename it as Calculate total cost, and drag the transactionhistory data source to connect to this task
12. Double-click the derived column to open the editing page 4-34. In the expression column, enter the following expression: [quantity] * [actualcost]. You can drag and drop the column name from columns on the left, enter totalcost In the derived column name column, select <Add as a new column> In the derived column, and click OK to exit the editing page. Figure 4-34
14. Drag and Drop an aggregate task in data flow, rename it as aggregate data, and connect it with the derived column. Double-click the aggregate task to open the editing page. On the 4-35 page, double-click productid. A new row is added in the following table. In operation, select group by, quantity and totalcost, and sum in operation. Click OK to exit the editing page. Figure 4-35
16. Drag a sort task from data flow transformations and rename it to sort by productid to connect it to the aggregate task. Double-click the sort task to open the editing page. Select the quantity field in descending order, which means to sort by sales volume from high to low. Click OK to exit the editing page.
18. Drag and Drop a flat file destination task from data flow destinations to rename it as vendor extract and connect it to the sort task.
20. Double-click flat file destination to open the editing interface and create a new file connection. In the pop-up file format selection dialog box, the first delimited is selected by default, and the connection is named vendor extract, enter c: \ ssisdemos \ vendorextract.csv in the file name text box. If this folder is not found in drive C, create a new one. The last page is 4-36. Click OK to exit the editing page. Figure 4-36
    Click the mapping tab to view the data input from the sort task and the data columns in the file one-to-one correspondence, 4-37
    
    Figure 4-37

Now the entire package is complete. This is a typical etlpackage. Right-click to run the package. After the package is executed, you can see that the Green Line contains the number of converted data lines, 4-38.

Figure 4-38