SSIS Execute SQL Task usage

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 you can execute a SELECT query statement to return a result set. The result set can be a row or multiple rows.

One, General tab

1, returns the result set (results set)

1. None: Indicates that no results are returned, and this option is used when executing the update,delete or insert command;

2. Single row: Returns one-line result, which can be returned to 1 or more variables in the result set tab

3. Full result set: Returns multiple rows of result set stored in object

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141101223545001.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

2, the SQL command that the component executes (SQL Statement)  

• connectiontype: the type of link connected to the data source, If it is an OLE DB link, check the

3, Example: sqlsourcetype=variable

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141141571511291.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141142075737740.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

Two, Expressions tab

The properties of the SSIS package can be configured either manually in the General tab or in Expressions tab, saving the value of the configuration property to a variable, and dynamically controlling the execution of the packet.

example, store the value of Sqlstatementsource in a variable, and the result is the same as sqlsourcetype=variable, except that the implementation is different, and SSIS is in the process of executing it. Use the properties of expressions to override the property values configured in general.

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141143185739516.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

Third, Result set tab

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

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141148205105305.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

Quad, Parameter Mapping tab

If the SQL command needs to pass parameters during execution, it can be configured in the Parameter Mapping tab. What if I use an OLE DB link that needs to be used? Represents the first parameter, and in parameter Mapping, the parameter name is set to the ordinal of the parameter, the first one? The sequence number is 0, that is, the sequence number increments from 0

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141306123704660.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

650) this.width=650; "src=" http://images.cnitblog.com/blog2015/628084/201504/141305057924367.jpg "style=" margin:0 px;padding:0px;border:0px; "/>

Five, returns the number of rows for the affected data

The ExecValueVariable property is the standard property of a task. Some tasks will return the output after execution, and in order to get the output of the task, we can define a variable to store the output result. The ExecValueVariable property of a task is used to specify the name of the variable that stores the result of the task output. The default property value is None, which indicates that the output of the task is not stored.

The Execute SQL Task returns the number of rows of the data being updated, and we can specify a variable for the ExecValueVariable property to receive the output value of the Task (execution value), which can be referenced in the downstream component to get the Execute The number of data rows updated by 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 the package

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201610/628084-20161023140311295-1883773331. PNG "style=" margin:0px;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201610/628084-20161023140316185-654677094. PNG "style=" margin:0px;padding:0px;border:0px; "/>

The properties of the Execute SQL task: The value of execvaluevariable is the variable varcount, and the task executes the following SQL statement:

INSERT into Dbo.delay_testvalues (1), (2), (3) insert into Dbo.delay_testvalues (2), (3)

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201610/628084-20161023140339029-185898184. PNG "style=" margin:0px;padding:0px;border:0px; "/>

The SQL statement that Task:insert data executes is that the parameter passed in is User::varcount

INSERT into Dbo.dt_testvalues (?)

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/628084/201610/628084-20161023140403232-1414428968. PNG "style=" margin:0px;padding:0px;border:0px; "/>

2, viewing the result, the first Task returns the result is Varcount is 2, the result is actually @ @RowCount, SSIS after executing the statement, the @ @RowCount assigned to the Execute SQL Task The ExecValueVariable property is specified in the variable.

SSIS Execute SQL Task usage