What is Package in SSIS?

Let’s understand the basics of Package in SSIS. This could also be a good interview question for SSIS.

  • A package is a unit of work that is retrieved, executed and saved.image
  • It’s a collection of various objects i.e. Control Flow elements, data flow elements,event handlers, variables, logging which together builds a package.
  • Various functionalities can be added into package using Control Flow elements, Data Flow elements.
  • We can set the security settings on the package.
  • We can also incorporate transactions in packages to better control the package execution.
  • Using connection managers in package helps us to connect with different data sources to extract and load data.
  • Logging in packages allows us to better control the package execution and also enables efficient error handling.
  • Event handlers handles the events raised during the package runtime.

 

Happy Learning !!!

Delete duplicate records from a SQL Server table

Today one of my colleague asked me how to delete duplicate records from a SQL Server Table. So to answer his question, let’s check the following example.

Note: The below example works on SQL Server 2005 and later versions.

–Create Table

CREATE TABLE dbo.TestDuplicate (EmpNo INT, EmpName VARCHAR(10));

–Insert Test Data

INSERT INTO dbo.TestDuplicate VALUES (1,‘Emp 1’);

INSERT INTO dbo.TestDuplicate VALUES (1,‘Emp 1’);

INSERT INTO dbo.TestDuplicate VALUES (2,‘Emp 2’);

INSERT INTO dbo.TestDuplicate VALUES (2,‘Emp 2’);

INSERT INTO dbo.TestDuplicate VALUES (3,‘Emp 3’);

–Remove Duplicates

WITH CTE
AS (SELECT ROW_NUMBER () OVER (ORDER BY EmpNo) SrNo , EmpNo FROM dbo.TestDuplicate)
DELETE FROM CTE WHERE SrNo NOT IN (SELECT MAX(SrNo) FROM CTE GROUP BY EmpNo);

–Check the table

SELECT * FROM dbo.TestDuplicate;

— Data before deleting the duplicate records.

image

— Data after deleting the duplicate records.

image

Points to Note:

  1. This was possible because of the ROW_NUMBER() function available in SQL 2005 onwards.
  2. ROW_NUMBER() function Returns the sequential number of a row within a partition of a result set. This means for each row in a result set it will return one unique sequential number for that row.
  3. The OVER clause determines the partitioning and ordering of the row set.
  4. As we have used CTE (Common Table Expression) it allows us to reuse the result set like a temporary table.

Hope this article may help you in deleting the duplicate records.

Download SQL Server 2008 R2 Developers Training Kit

Hello Readers,

SQL Server 2008 R2 Developers Training Kit is available now for the download. You can download it from the  below MSDN link.

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

The training kit is brought to you by Microsoft Developer and Platform Evangelism.

Following are the contents of the Developer Training Kit. For more details please visit the link mentioned above.

Contents
The training kit includes the following content:

  • Videos (8)
    • SQL Server 2008 R2 Update for Developers Overview Part I – SQL Server 2008 Review
    • SQL Server 2008 R2 Update for Developers Overview Part II – Introducing SQL Server 2008 R2
    • Introducing SQL Server 2008 R2 StreamInsight
    • Demo: Real Time Analytics with SQL Server 2008 R2 StreamInsight
    • Introducing SQL Server 2008 R2 Application and Multi-Server Management
    • Introducing SQL Server 2008 R2 Reporting Services
    • Introduction To SQL Server 2008 R2 StreamInsight and Complex Event Processing
    • Introducing PowerPivot for Excel 2010 and SharePoint 2010
  • Presentations (8)
    • SQL Server 2008 R2 Update for Developers Overview Part I – SQL Server 2008 Review
    • SQL Server 2008 R2 Update for Developers Overview Part II – Introducing SQL Server 2008 R2
    • SQL Server 2008 Filestream
    • SQL Server 2008 Spatial
    • SQL Server 2008 T-SQL
    • SQL Server 2008 Date and Time Types
    • SQL SErver 2008 SQLCLR
    • SQL Server 2008 Reporting Services
  • Demos (13)
    • AdventureWorks Racing All-Up SQL Server 2008 Demo
    • SQL Server 2008 All-Up Spatial Demo
    • SQL Server 2008 Spatial Types Demo
    • Intro to SQL Server 2008 Filestream Demo
    • SQL Server 2008 SQL CLR Nullable Types Demo
    • Programming with SQL Server 2008 Filestream Demo
    • SQL Server 2008 Reporting Services Web Application Integration Demo
    • Date and Time Support in SQL Server 2008 Demo
    • SQL Server 2008 T-SQL Table-Valued Parameters Demo
    • SQL Server 2008 T-SQL Row Constructors Demo
    • SQL Server 2008 T-SQL Grouping Sets Demo
    • SQL Server 2008 T-SQL Merge Demo
  • Hands-on Labs (8)
    • How to build your first Web Application with SQL Server and ASP.NET
    • Using SQL Server 2008 Spatial Data in TSQL
    • Using SQL Server 2008 Spatial Data in Managed Code
    • Using SQL CLR in SQL Server 2008
    • PowerPivot in SQL Server 2008 R2
    • Using the New Features of Reporting Services 2008 R2
    • Introduction To SQL Server 2008 R2 StreamInsight and Complex Event Processing
    • Data-tier Applications in SQL Server 2008 R2 and Visual Studio 2010

Happy Learning !!!