Dealing with joins

I had worked a lot with Data warehousing and database projects. So many people working told me join is really easy. Left join, right join, Full outer join, cross join and inner join.

I had my opinion no its not easy if you understand it correctly. There is lots of combination that you can do with join and where close play a big role in sql server.

1. Inner join: In definition it says matching records from both table. We can also define Self join which means joining with same table. One more thing to mention if you have only inner joins in your query than no need to use Where clause in most cases. On condition you can use which makes performance of query really faster.

2. Left or Right Outer Join: Left join in terms of definition non matching and matching output from left table and matching output from right table. For the right outer join it is other way around.

Left join and Right join both can serve same purpose. It has been created for better visualization of data. You can have restricted Left join and Restricted Right join if you use where condition with very right purpose.When you have combination of multiple left joins and inner joins in the query than its crucial to identify right output. So when you have this kind of situation work with one join at a time. IF you are using all together than you will not see right output.

3. Full outer join: Full outer join is for all non matching and matching record. If you have multiple full outer join and multiple left join than its a mess.You can also have restricted full outer join.

I have seen lots of issues while joining self join with left outer join or full outer join in single query. 

Error handling in SSIS

There are five way to do Error handling.

1. Event handler
2. Error redirection
3. Event Log or Error Log
4. Checkpoint usage
5. Transactions

1. Event Handler: Event handler is about when event occurs it may be OnError, OnExecStatusChanged, OnInformation, OnPostExecute, OnPostVaildate, OnPreExecute, OnPreVaildate, OnProgress, OnQueryCancel, OnTaskFailed, OnvariableValueChanged or OnWarning.
2. Error Redirection: Error Redirection is like when ever error occurs you can create precedence constraint connection which may be at control flow or data flow level. 
3. Event Log or Error Log: Event log may occur at any level pre execute, post execute, on error, on validation. There are 5 different way to do event log. Text file, SQL Server, SQL Server Profiler, Windows event log and XML files.
4. Check point: Check point is like a failure point. While running the package if execution process got failed than on next run it will start from failure point. It uses checkpoint file to keep track for last run.
5. Transactions: Transactions are created using same concept as we have for T-SQL. But purpose is quite different. There are basic three types of transactions. Required, Supported and Not Supported. By default it is supported. 

We will have more discussion but this one is overview of how we can do error handling using SSIS.