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

Advance Editor

·         Click on “Input and Output Properties” tab

Input and Output Properties

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

Thanks for visiting!!

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!!

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.

 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

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

CROSS JOIN

Here is an example of Cross Join in LINQ

var query = from s in _dbContext.Students

a 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!!