Shulou(Shulou.com)06/01 Report--

The Execute Sql Task component is a very useful Control Flow Task that can execute SQL statements directly, for example, you can execute a data update command (update,delete,insert) or an select query statement that returns a result set, which can be one row or multiple rows.

One, General tab

1, return the result set (Result Set)

None: indicates that no results are returned, use this option when executing Update,delete or insert commands

Single row: returns a single row of results. You can return the result set to one or more variables in the Result Set tab.

Full result set: returns multiple rows of results, and the result set is stored in the object object

2, the SQL command (SQL Statement) executed by the component

ConnectionType: the type of link connected to the data source. If it is an OLEDB link, check OLEDB.

Connection: link string

The type of SQLSourceType:SQL data source, there are three options: Direct Input,File Connection and Variable. Direct Input: enter the SQL command directly; FileConnection: save the SQL command in the .sql file; Variable: the SQL command is saved in the variable of SSIS. If the SQL command executed by Execute SQL Task is dynamic, it is better to use the Variable option.

SQLStatement: the SQL statement to be executed, depending on the SQLSourceType, has three different values: SQL statement, .sql file path, or SSIS variable

3, example: SQLSourceType=Variable

Second, Expressions tab

The properties of SSIS Package can be configured either manually in General Tab or in Expressions Tab. The values of configuration properties are saved to variables, and the execution of the package can be controlled dynamically.

For example, if you store the value of SqlStatementSource in a variable, the result is the same as SqlSourceType=Variable, but in a different way, and SSIS overrides the property value configured in General with the property of Expressions during execution.

Three, Result Set tab

If you set Result Set to Singel Row in the General tab, you can store the result value in a variable

Four, Parameter Mapping tab

If the sql command needs to be passed during execution, it can be configured in the Parameter Mapping tab. If you use OLE DB links, do you need to use? Represents the first parameter, and sets parameter name to the serial number of the parameter in Parameter Mapping, the first? The serial number is 0, that is, the serial number is incremented from 0

Fifth, return the number of rows of affected data

The ExecValueVariable attribute is a standard property of Task. Some Task will return the output result after the execution is completed. In order to get the output result of Task, we can define a variable to store the output result. The ExecValueVariable property of Task is used to specify the name of the variable in which the Task output is stored. The default attribute value is none, which means that the output of task will not be stored.

Execute SQL Task returns the number of rows of data being updated, and we can specify a variable for the ExecValueVariable property to receive the output value (Execution value) of Task, which can be referenced in the downstream component to get the number of rows updated by Execute SQL Task.

Returns the number of rows affected by the SQL statement (s). The ExecValue is using the @ @ ROWCOUNT to assign the value of the variable and absent a @ @ ROWCOUNT the value returned is-1.

ExecValueVariable usage of sample Execute Sql Task

1. Design the Control Flow of Package

Attribute of Execute Sql Task: the value of ExecValueVariable is the variable varCount, and the SQL statement executed by this Task is as follows:

Insert into dbo.delay_testVALUES (1), (2), (3) insert into dbo.delay_testVALUES (2)

The SQL statement executed by Task:insert Data is that the parameter passed in is User::varCount

Insert into dbo.dt_testvalues (?)

2. Look at the result. The result returned by the first Task is varCount is 2. This result is actually @ @ RowCount,SSIS assigns @ @ RowCount to the variable specified by the Execute SQL Task attribute ExecValueVariable after executing the statement.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.