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

Advertisements

What is LineageID in SSIS?

Let’s understand about LineageID property in SSIS

  1. LineageID is a very important property in SSIS packages.
  2. Every column used in dataflow has atleast one LineageID.
  3. It’s an integer value which identifies the column in the buffer.
  4. A column can have more than one LineageID and this is based on the types of transformation outputs  the column has (or passes through) in the data flow.

Below is the image of column properties in ADO.NET Source – Advanced Editor (right click on the ADO.NET Source in Data  Flow task and select Show Advanced Editor)

Image : 1

image

 

Image 2:  How to open Advanced Editor for ADO.NET Source (in Data Flow Task)

image

 

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.

SSIS Webcast Watch : Tuning Your SSIS Package Data Flow in the Enterprise

Tuning Your SSIS Package Data Flow in the Enterprise video gives you a very good understanding on the SSIS tuning part.

Download Video from here:

http://msdn.microsoft.com/en-us/library/dd795224.aspx

There are couple of other video links are available for download on SSIS. So don’t forget to check that. Those are really good videos and gives lot of knowledge on the performance tuning of SSIS packages.

Happy Learning !!!

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