SSIS advanced conversion task-ole db command

The ole db commond task accepts row data from the input stream and executes an SQL statement or custom stored procedure. This conversion is easy to confuse with the Execute SQL task, but it is very similar to receiving data in a result set cyclically in the ADO connection, creating and executing an ADO command. Parameter Data in the input stream can be provided to an SQL command or stored procedure. Here we can see that "loop" may think of another word "performance ". This may involve updating, inserting, and deleting statements. Each time there is a disconnect or connection operation. But it does not mean that this conversion is useless. Here we will focus on how to configure and use this task. Pay attention to the amount of input data and weigh performance and scalability when you need to cache data to a temporary or actual table to achieve the optimal effect.

When configuring this task, the basic point is to set the connection to obtain SQL statements, provide executable SQL statements, and set the ing between data in the input stream and SQL statement parameters. Open the editing page. The editing page of this task has four tabs. Use advanced editing:

1. Connection Management: sets an ole db connection to obtain the SQL statement to be executed through this connection. Note that the parameter data in the input stream does not come from this source.
2. Component attributes: In this label, the sqlcommand attribute sets the SQL statement and the commandtimeout attribute sets the connection expiration time. This is the same as the working principle of the ADO connection object, if it is set to 0, there is no expiration time. You can also set the Task Name and description in this tag.
3. Column ing: this label displays the columns and target columns in the input stream, that is, the parameter names in the SQL statement. Use the mouse drag method to connect them. They correspond one-to-one. If the statement has more parameters than the input stream, you must use the derived column conversion to generate redundant columns.
4. Input and Output attributes: most of the time, you can set the output columns in the column ing label. However, if the ole db data source does not support derived parameters, you must use the parametersnames and dbparaminfoflags attributes to set the output columns.

The best way to learn about this conversion task is to operate on an example. This example assumes that you want to verify the daily deposit data and create a deposit in the database. You must also create payment transactions for each customer so that accounting personnel can review the transactions through software. In this example, we don't need to know how to create deposit items and pay transactions. We just need to create two empty stored procedures to complete this work.

1. Create a package named ole db command, add a Data Flow Tesk, and double-click it to enter Control Flow
2. Create a text file and save it to c: \ ole db eft data.txt. The file content is as follows:

Customerid, depositamt, depositdate, invoice

XY-111-222, $100.00, 07/13/2005, 222-063105

XX-Z11-232, $1000.00, 07/13/2005, 232-063105

XX-Y88-233, $555.00, 07/13/2005, 233-053105

3. Run the following SQL statement to create two empty stored procedures in the database adventureworks.

Use adventureworks

Go

Create proc usp_deposittrans_add (

@ Customerid varchar (10 ),

@ Depositamt money,

@ Depositdate smalldatetime,

@ Invoice varchar (15 ))

As

--- An empty stored procedure only serves as an instance

Go

Create proc usp_paymenttrans_add (

@ Customerid varchar (10 ),

@ Depositamt money,

@ Depositdate smalldatetime,

@ Invoice varchar (15 ))

As

-- An empty stored procedure only serves as an instance

4. Add a flat file source to connect to c: \ ole db eft data.txt
5. Drag and Drop an ole db command task on the data flow design interface to connect the flat file source with it.
6. Open the edit interface of ole db command and connect to the database adventureworks in Connection Manager settings. On the component Properties tab page, set the sqlcommand attribute to usp_deposittrans_add ?, ?, ?, ?. Here? Indicates an input parameter. Last interface 1
   
   Figure 1
7. In the column mapping label, connect the input column with the corresponding parameters.
   
   Figure 2
8. Add an ole db command to connect the first and second databases. The configuration is similar to the first database, but here the SQL commond attribute is set to usp_paymenttrans_add ?, ?, ?, ?
   Run the package. The three lines of data in the text file are input and executed as parameters of the two stored procedures. If the stored procedure exists, three deposit items and three transactions are created. Here we can also see that the three stored procedures can be reused here, as long as appropriate parameters are provided. In this example, you can repeat the existing logic to input a batch of data to the package once for execution.