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;
#endregion

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();
 MessageBox.Show(strMsg);
               
}
}


 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.

SQL - CONCAT function

CONCAT function is also known as T-SQL function which was introduced in SQL Server 2012. This function allows us to concatenate two or more parameters values together and these parameters values should be separated by comma. Before release of Concat function, we used to use the “+” operator to combine or concatenate two or more string values. The most important feature of Concat function is that it also takes care of data type conversion and beautifully handles NULL on its own. In case of Concat function, we don't need to care about the null values in the parameters which are going to be used.
Syntax
CONCAT ( string_value1, string_value2 [, string_valueN ] )
Arguments
string_value: A string value to concatenate to the other values.
Return Types: String, the length and type of which depend on the input.

Interesting features
  1. CONCAT takes a variable number of string arguments and concatenates them into a single string.
  2. It requires at least two input values; otherwise, an error is raised.
  3. All arguments are implicitly converted to string types and then concatenated.
  4. Best part of this; Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned.
  5. The implicit conversion to strings follows the existing rules for data type conversions. 
  6. If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.
Examples:
Concatenate Space Characters with input variables

To understand the features of Concat function, we are showing the examples with Concat function and without the concat functions as given below:
---- Declare local variables
Declare @inpFirstName Varchar(20) ='Ryan'
Declare @inpLastName Varchar(20) ='Arjun'

---- Concatenating variables without Concat Function
Select @inpFirstName+' '+@inpLastName as FullName
FullName
Ryan Arjun
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName
FullName
Ryan Arjun

In the above example, we are using the two variables to concatenating together. You can see that, we are getting the same outputs. 

Concatenate Space Characters with with NULL values
Now, we are going to make some interesting changes in the above example by setting the null value in the second variable as given below:
---- Declare local variables
Declare @inpFirstName Varchar(20) ='Ryan'
---- Set Null Value here
Declare @inpLastName Varchar(20) =NULL

---- Concatenating variables without Concat Function
Select @inpFirstName+' '+@inpLastName as FullName
FullName
NULL
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,' ',@inpLastName) as FullName
FullName
Ryan
You can easily see that full name is showing NULL values in case of without Concat function but by using Concat function, full name is showing because of Null values are implicitly converted to an empty string.
Concatenate number and string together
If you want to concatenate string with numeric value then you should need to convert the numeric value into the string as given below:
---- Declare local variables
Declare @inpFirstName Varchar(20) ='Ryan'
Declare @inpContactNumber BIGINT =1234567890

---- Concating variables without Concat Function
Select @inpFirstName+':'+CAST(@inpContactNumber as Varchar) as NameWithContact
NameWithContact
Ryan:1234567890
---- Concatenating variables with Concat Function
Select CONCAT(@inpFirstName,':',CAST(@inpContactNumber as Varchar)) as NameWithContact
NameWithContact
Ryan:1234567890

If you want to concatenate two numeric values then there is no need to change their data types because of implicit conversion to strings follows the existing rules for data type conversions.
Conclusion
The CONCAT function appends one string to the end of another string and does not require ISNULL for converting NULL into an empty string. All arguments are implicitly converted to string types and then concatenated.

Sunday, April 17, 2016

SSIS - Object Types Variable in Execute SQL Task

What are Object Types Variable in SSIS?
In SSIS, Object Types variables are very generic object which are used to store the dataset result for the further processing. We can use them in the various SSIS components to achieve the requirements. They are used to store the output values from a relational query/stored procedure within Execute SQL Task component and can be used as an ADO recordset within a For Each loop container to perform iterative logic. They can be used in Script Task component to implement the business logic.
Object Types Variable in Execute SQL Task
To pull the values from database, we can use the SQL Statement or stored procedure with the parameters. To understand this functionality, we are using the below dataset to pass the input parameter and set the output in the Object variable –

SELECT 101 AS DEPTID, 'SALES & MARKETTING' AS DEPTNAME
UNION
SELECT 102 AS DEPTID, 'ACCOUNT & FINANCE' AS DEPTNAME
UNION
SELECT 103 AS DEPTID, 'INVENTORY' AS DEPTNAME
UNION
SELECT 104 AS DEPTID, 'PRODUCTION' AS DEPTNAME

DEPTID
DEPTNAME
101
SALES & MARKETTING
102
ACCOUNT & FINANCE
103
INVENTORY
104
PRODUCTION

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.  
  5. 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-
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.

Friday, April 15, 2016

SSIS - Use Variable in Execute SQL Task

As we know that Execute SQL Task is an extra ordinary component in Integration Services. We can use one or more variables in Execute SQL Task to pull the information from the database.  
To pull the values from database, we can use the SQL Statement or stored procedure with the parameters. To understand this functionality, we are using the below dataset –

SELECT 101 AS DEPTID, 'SALES & MARKETTING' AS DEPTNAME
UNION
SELECT 102 AS DEPTID, 'ACCOUNT & FINANCE' AS DEPTNAME
UNION
SELECT 103 AS DEPTID, 'INVENTORY' AS DEPTNAME
UNION
SELECT 104 AS DEPTID, 'PRODUCTION' AS DEPTNAME

DEPTID
DEPTNAME
101
SALES & MARKETTING
102
ACCOUNT & FINANCE
103
INVENTORY
104
PRODUCTION
Now, we need to declare a variable in SSIS development window and set the default value as given below:

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.  
  5. 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:

After doing all the stuffs, click on the OK button. Now, we have done all the steps to pass the variable value to the back end. Now, execute the Execute SQL Task to pull the requested values.


Now, we are able to pass the input values into the data source to pull the expected result with the help of Execute SQL Task. Now, we will learn, how to capture output result from the Execute SQL Task.

Popular Posts