Microsoft BI's SSIS series-handling techniques for single Row and full Result Set in Execute SQL Task

Introduction to the outsetExecute SQL Task This control is very often used in Microsoft BI ETL projects, and it is also the first few control flow controls that are accessible to most beginners in SSIS. we typically use the Execute SQL Task scenario to include but not only the following categories:

1. Use Execute SQL Task to perform some Truncate operations before loading data from the source to the Staging table.
2. Perform some Log inserts and update operations.
3. The Merge statement operation in the ETL process.
4. Output processing of XML.

about how to use Execute SQL Task is not explained in this article, including parameter passing Input, stored procedure return value Output, including return values, and SQL Source Type, as well as the old Db,ado. NET differences and other basic concepts such as text will not be involved.  the content of this article is for several types of ResultSet: None, single row, full result set, XML. None of which does not need to be explained, the contents of the XML can be referred to my other article two ways to output SQL Server database data into an XML file, so here only the single Row and full result Set processing methods. Single Rowfirst of all, single row refers to a single row of records returned in the Execute SQL Task, allowing multiple columns. The use of single Row in the Execute SQL Task is usually done in order to control the flow of flow in the control stream, meet certain conditions to walk a branch process, not satisfied then go another process. For example, at the beginning of the package execution query the database to check some of the status of the package, or some of the state of the data, only to meet certain conditions to carry out the remaining process, or do not execute the package or error. This type of design may exist in a variety of BI projects, and the prototype is to control the process using the value returned by the single row in the Execute SQL Task. here are a few steps:

1. The statement in SQL Statement returns a single-line record, which can be multiple columns.
2. You need to implement the value on a column in a package that defines a variable to receive a single row.
3. Use precedence Constraint and variables to control the flow.

Single Row caseA simple query statement returns a single-row, two-column record. set up the connection and put the SQL statement, select Single Row. Add three variables

1. Existing_count-the number of count returned in the query to receive.
2. Max_date-Used to receive the maximum time returned in a query.
3. Test_date-Test time, for example, 2014-01-01.

0 represents the first column in a single row, and 1 represents the second column in single row. The following two empty Data Flow tasks are associated to the Execute SQL task, and click on the connector to set the condition control. the connection line condition on the Dft_test_after_max is determined as: DATEDIFF ("DD", (dt_dbdate) (@[user::test_date]), (dt_dbdate) (@[user::max_date])) >=0the judgement expression on Dft_test_before_max is: DATEDIFF ("DD", (dt_dbdate) (@[user::test_date]), (dt_dbdate) (@[user::max_date])) <0left and right two data streams add a Script Task to display the values for these variables, as shown in the following code:

 Public voidMain () {//Todo:add Your code here//user::existing_count,user::max_date,user::test_date            stringExisting_count = dts.variables["User::existing_count"].            Value.tostring (); stringMax_date = dts.variables["user::max_date"].            Value.tostring (); stringTest_date = dts.variables["user::test_date"].            Value.tostring (); MessageBox.Show ("Existing Count-"+ Existing_count +", test date-"+ Test_date +", max date-"+max_date); Dts.taskresult= (int) scriptresults.success; }

Save and execute the package, the process goes to the right because MAX date is larger than TEST date.

Modify Test_date, then TEST date is larger than MAX date. This is how the single Row in Execute SQL Task is used. Full Result SetThe use of full Result Set in an Execute SQL Task is typically used in conjunction with a Foreach loop, where the scenario is to iterate through each row of data in the result set of the query, take out a few columns of each row of data into the variable, and then Other control flow controls in the Foreach Loop use these variables to do some things. QuestionsWouldn't it be better to traverse through a database like this? The problem is that sometimes the object that you manipulate is not just a Table object in the database, it involves different file handling, and this is the way to do it. For example, I have hundreds of files in my target data source, and the name of the file includes the names of the products, and now there is a record of the product names in the table. You need to iterate through the products in the table and compare which files are named with these product names, files that contain the product name are extracted, and files that do not contain the product name are moved to another directory. Use this query as an example result set below- using full RESULT SETThe following variables are defined-order_set is used to hold the result set in the Execute SQL Task query, and other variables are used to save the values of each column while looping through each row. Note: The returned result set is to be saved with a variable of type object, which exists internally as a collection and holds the entire query result set. the Mapping of the EXECUTE SQL TASK. Settings under Foreachwe can iterate through the returned collection in a Foreach way. Add a foreach control and set the Traverse mode-Foreach ADO enumerator and the collection object to traverse. As you traverse this collection, each time you return a row, the line is also a collection that assigns the elements of the collection to the individual variables by setting the index 0,1,2. Add a Script Task to the Foreach control to display the contents of each column in each row. the scripting code in script is as follows:

 Public voidMain () {//Todo:add Your code here//User::P roduct_id,user::sales_order_detail_id,user::unit_price            stringDetailid = dts.variables["user::sales_order_detail_id"].            Value.tostring (); stringProductID = dts.variables["User::P roduct_id"].            Value.tostring (); stringUnitPrice = dts.variables["User::unit_price"].            Value.tostring (); MessageBox.Show ("Detail ID-"+ detailid+", Product ID-"+productid +", Unite Price-"+UnitPrice); Dts.taskresult= (int) scriptresults.success; }

Saving and executing the package, the result of the first loop is a failure, but it can be seen that the return of our result set is no problem.

the cause of the error is as follows:error:the type of the value (String) being assigned to variable "user::sales_order_detail_id" differs from the Curren T variable type (INT32). Variables is not a change type during execution. Variable types is strict, except for variables of type Object.error:the type of the value (String) being assigned to variable "User::P roduct_id" differs from the current variable Type (INT32). Variables is not a change type during execution. Variable types is strict, except for variables of type Object.error:the type of the value (String) being assigned to variable "User::unit_price" differs from the current variable Type (Decimal). Variables is not a change type during execution. Variable types is strict, except for variables of type Object.This is because the data in the collection is of type String by default and cannot be converted directly to the type specified by the variable we define , so we need to modify our variable type. execute again to see the effect of the traversal, the first time--

Last time-

loop traversal in a Script Taskthe same object object can iterate through not just in Foreach, but also directly in a Script Task. Add a Script Task to put the result set object in the variable list. The script code is as follows-

usingSystem.Data.OleDb; Public voidMain () {//Todo:add Your code hereOleDbDataAdapter adapter =NewOleDbDataAdapter (); DataTable DataTable=NewDataTable (); Adapter. Fill (dataTable, dts.variables["User::order_set"].            Value); foreach(DataRow rowinchdatatable.rows) {MessageBox.Show (row[0] +","+ row[1] +","+ row[0]); } Dts.taskresult= (int) scriptresults.success; }

can achieve the same effect of traversal-

Last Record-Summarya single row and full result set are returned by explaining the two query results of the Execute SQL Task, which in effect gives the scenario of when to use single row and full result set. The familiarity and mastery of these scenarios can help us to identify different solutions in complex ETL projects and to solve some practical problems with great flexibility. again, in particular through the Foreach Loop Container and Script Task learned two ways to parse the full Result Set, which involves the knowledge points and details are more, deliberately summed up the hope can help everyone.

For more bi articles, see the Bi-series essay list (SSIS, SSRS, SSAS, MDX, SQL Server) If you feel this article has helped you, please help recommend it to make it easy for others to see these articles quickly in the Biwork blog recommendations Bar.

Microsoft BI's SSIS series-handling techniques for single Row and full Result Set in Execute SQL Task