Wednesday, April 27, 2016

SSIS - Reading Custom Objects Variables

In our last section SSIS - Object Types Variable in Execute SQL Task, we have covered up to store the data into the object variables. In this section, we are going to use these data into another process. We are in favor to store your data in Dataset object because by doing this, we can always access our data in other script task / components.

To understand the object outputs, we are using another most important component of SSIS called Script Task. As we know that custom classes are unknown in the other SSIS components and we can get them through the Script Task because they have all the properties and correct values to handle the custom object variables.
In this window, we are using two type of variables. One is for to pass the value into the database and pull the output result into the Object variable by the help of the Execute SQL Task component.
Now, we need to declare a variable in SSIS development window and set the default value as given below:

In the variable window, we are using two variables. One variable will be used as the input parameter which has integer data type. Another one will be used as the output parameter and must have Object Typed data type. 
After declaring the variables, we need to add Execute SQL Task in Control Flow Window from the SSIS Toolbox control as given below:

Now, we need to set the data connection and SQL Statement in the Execute SQL Task. For this, right click on the Execute SQL Task and click on Edit. This action will open the Execute SQL Task Editor window. We need to set the following things as given below in green colors- 
  1. Set the Result Set as Full result set 
  2. Connection  need to set the data source from connection manager 
  3. SQL Statement, need to set the SQL statement or stored procedure with question mark (?) where ? is represented as parameter value to the data source. Enter SQL Query window, we can write the SQL query or stored procedure- 

  4. After adding the SQL statement, it will be look like as given below-

After setting the above setting, we need to set the Parameter Mapping in the Execute SQL Task Editor window. For this, click on the add button and choose the parameter which needs to be passed as given below:

Now, we have set the input parameter in the parameter mapping. To pull the value from the output, we need to set the Result Set as given below: 
We need to do following things-
  1. Set the default Result Name as 0 (Zero) - (In Green background)
  2. Set the Variable Name as the defined Object Type variable. In our case, we have declared it as outResult- (In Green background)
Now, click on the Okay Button and execute the Execute SQL Task. It will execute without any error as given below-
After setting the returned values in the Object variable called outResult, we need to read the value from the Object variable. 
For this, we need to add Script Task component from the SSIS Toolbox window and link it with Execute SQL Task component with the help of Precedence Constraint as given below:
Now right click on the Script Task to open Script Task Editor window as given below:
In the Script Task Editor Window, we need to set the following things-
  1. In Script Window, set the Object variable with ReadWriteVariables properties as given below: 

In the Script Task Editor Window, you can see the Edit Script Button. Click on the Edit Script button to launch the code window as given below:
Remember, we are using the object variable which are storing the records from database and these records may be more than one row also. So, we need to add the following namespaces in the code window: 

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Xml;

After adding the above namespaces, we need to add the following code as given below:
 In the above code, we are doing the following things-
  1. OleDbDataAdapter object to fill the DataTable,
  2. DataTable object to store the value from the Object Variable,
  3. String Object to show the result output from the DataTable object,
  4. Foreach loop on the DataTable object to get the values row by row as given below:

/// <summary>
/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.
/// </summary>
public void Main()
// TODO: Add your code here

Dts.TaskResult = (int)ScriptResults.Success;
// declare object for OleDbDataAdapter
OleDbDataAdapter objDA = new OleDbDataAdapter();
// declare object for DataTable
DataTable objDT = new DataTable();
// declare variable for string
string strMsg = string.Empty;
// fill DataSet object with the help of object by OleDbDataAdapter
objDA.Fill(objDT,Dts.Variables["User::outResult"].Value );

// Call For Each to pull the value row by row
foreach (DataRow dr in objDT.Rows)
 strMsg=dr[0].ToString() + " "+ dr[1].ToString();

 After build the code, close the script code window and execute the packages as given below:

After executing the package, Message Box is appearing on the screen and showing the results row by row by pulling from Object variable. By the help of Execute SQL Task, we have learnt that -
  1. How to pass the input parameters to the SQL Statements/Stored Procedures.
  2. How to set the result output into the Object Typed Variables.
  3. SSIS - Reading Custom Objects Variables
Now, we will learn another feathers of the Execute SQL Task and Script Task.

No comments:

Post a Comment