Monday, August 28, 2017

SSIS - Error Code and Error Column

SSIS automatically creates two columns on the Error output from data flow components
1.       ErrorCoulmn
2.       ErrorCode 

Error Code – it contains the error number that caused the row to be rejected. You can find the list of error code at http://msdn.microsoft.com/en-us/library/ms345164%28v=sql.100%29.aspx

Error Column – it contain the column ID of the flawed column and that Column ID is unique to a dataflow within package.

You can easily find actual associated Column Name for Column ID.
·         Open the Advance Editor of error associated component

SSIS - Advance Editor
Advance Editor


·         Click on “Input and Output Properties” tab

SSIS - Input and Output Properties
Input and Output Properties


·         View all Input Columns, you can find the matching column ID.





Thanks for visiting!!


Wednesday, August 23, 2017

SQL - The Difference between ROW_NUMBER (), RANK () and DENSE_RANK ()

In this blog, we will discuss about ROW_NUMBER (), RANK () and DENSE_RANK () sql function and these function are used to generate RowID for given result set but these calculate rowID in different way.

Here is EmployeeCourse SQL relational table  and this table stores the empoyee and assigned course mapping information.

Table:  EmployeeCourse
             { 
                  EmployeeID
                  CourseID
              }

Select * From EmployeeCourse

Output:  

EmployeeID
CourseID
1
1
1
3
2
2
2
1
3
3
3
1




Row_NUMBER () assign unique ID for each row of result with help of Order by Column.

SQL Script :

Select *, Row_Number() Over (Order by EmployeeID)  as [Row Number] From EmployeeCourse

Output:

Employee ID
Course ID
Row Number
1
1
1
1
3
2
2
2
3
2
1
4
3
3
5
3
1
6

RANK()  assigns a unique number for each row and for those rows, which have duplicate value, assign the same ranking. There would be gap in the sequence in case of duplicate value.

SQL Script :

Select *, Rank() Over (Order by EmployeeID)  as [Rank] From EmployeeCourse

Output: 
Employee ID
Course ID
Rank
1
1
1
1
3
1
2
2
3
2
1
3
3
3
5
3
1
5



DENSE_RANK()  assigns a unique number for each row and in case of duplicate value, it assigns the same rank but don’t leave any GAP in sequence.

SQL Script :

Select *, DENSE_RANK() Over (Order by EmployeeID)  as [Dense Rank]  From EmployeeCourse

Output:

Employee ID
Course ID
Dense Rank
1
1
1
1
3
1
2
2
2
2
1
2
3
3
3
3
1
3


Thanks for visiting!!

Friday, August 18, 2017

C# LINQ : Joins (Inner Join, Left Outer Join, CROSS JOIN)

this blog will demonstrate that how Join (Left outer join, inner join) work in LINQ . LINQ has a join query operator that work like SQL JOIN. LINQ use join query operator to fetch data from more than one tables on common columns/properties.
  1. INNER JOIN
  2. LEFT OUTER JOIN
  3. CROSS JOIN
There are few tables Student, StudentCourse and StudentAddress :

Student Table:

StudentID
StudentNumber
 FirstName
 LastName
DateofBirth
1
911
John
Lewis
1/23/1985
2
912
King
George
4/3/1989
3
913
Jolly
Singh
6/7/1986

StudentCourse Table:

StudentID
CourseID
TeacherID
1
M1
12
1
C1
13
1
E1
15
2
M1
12
2
E1
15

StudentAddress Table:

Student ID
City
State
Zip Code
1
Dublin
OH
43017
2
Rockville
TN
32012


Inner Join:

LINQ uses Inner Join query operator to get the data from two or more tables based on a common columns, and It will ignore those record, which are not present into both table similar Like SQL Inner Join.

C# LINQ INNER JOIN
 INNER JOIN

Here is an example of  Inner Join in LINQ :

 var query = from s in _dbContext.Students
              join c in _ dbContext.StudentCourses on s.StudentID equals c.StudentID
              select new { s, c };

Lambda Expression :

var query = _dbContext.Students.Join(_dbContext.StudentCourses , s=> s.StudentID , c =>  c.StudentID,(s,c) => new { s, c})
.Select(m => new { m.s, m.c });

Output:

Student ID
Student Number
 First Name
 Last Name
Date of Birth
Course ID
Teacher ID
1
911
John
Lewis
1/23/1985
M1
12
1
911
John
Lewis
1/23/1985
C1
13
1
911
John
Lewis
1/23/1985
E1
15
2
912
King
George
4/3/1989
M1
12
2
912
King
George
4/3/1989
E1
15

In above joining query, we are fetching student and its associated course details on given student Number (911). In this query, it will returns only matching and common records on both tables and you can see, there is not any record for Jolly (StudentID : 3) . StudentCourse table does not have course details for student Jolly.

If you want to get the specific given student number records (added where clause for Student Number)

var query = from s in _dbContext.Students
              join c in _dbContext.StudentCourses on s.StudentID equals c.StudentID
              where s.StudentNumber = 911
              select new { s, c };

Lambda Expression :

var query = _dbContext.Students.Join(_dbContext.StudentCourses , s=> s.StudentID , c => c.StudentID,(s,c) =>; new { s, c})
.Where(w => w.s.StudentNumber 911)
.Select(m => new { m.s, m.c });

Output:

Student ID
Student Number
 First Name
 Last Name
Date of Birth
Course ID
Teacher ID
1
911
John
Lewis
1/23/1985
M1
12
1
911
John
Lewis
1/23/1985
C1
13
1
911
John
Lewis
1/23/1985
E1
15


Left Join or Left Outer Join:
Left Outer join is used to get the completely data from one table and only matching data from second table



LINQ LEFT OUTER JOIN
LEFT OUTER JOIN
Here is an example of Left Outer Join in LINQ :

var query = from s in _dbContext.Students
join a in _db.StudentAddress on s.StudentID equals a.StudentID into StudentAddressDetails
            from address in StudentAddressDetails.DefaultIfEmpty()                     
            select new { s, address.City };

Lambda Expression :

 var query = dbContext.Students.GroupJoin(_db.StudentAddress, s => s.StudentID , a => a.StudentID , (s, a) => new { s, a}).SelectMany(m => m.a.DefaultIfEmpty(),(m,s) => new { m.s, m.a.City});

Output:

Student ID
Student Number
 First Name
 Last Name
Date of Birth
City
1
911
John
Lewis
1/23/1985
Dublin
2
912
King
George
4/3/1989
Rockville
3
913
Jolly
Singh
6/7/1986
NULL

The above query returns the complete list of student record from students table and its city address, if there is not address record for any student, it will return student record along with City Default value (NULL).

Cross Join :

Cross join is a Cartesian join and it means Cartesian product of both the tables. This join does not use any condition to join two table and it returns the multiplication of record number of both tables

LINQ CROSS JOIN
CROSS JOIN

Here is an example of Cross Join in LINQ

var query = from s in _dbContext.Students
in _db.StudentAddress 
            select new { s.StudentID ,s.StudentNumber, s.FirstName, a.City, a.Zipcode };

Output:


Student ID
Student Number
 First Name
City
ZipCode
1
911
John
Dublin
43017
2
912
King
Dublin
43017
3
913
Jolly
Dublin
43017
1
911
John
Rockville
32012
2
912
King
Rockville
32012
3
913
Jolly
Rockville
32012

The above query returns all records from both tables.

Thanks for visiting!! 

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...