SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Shrink database when log is full

Leave a comment »

Interview question – Quick Links


Running value function:

SSRS Formatting

SSRS Functions:

SSRS Tips and Tricks

Dataware housing

SSIS — –V  Good

SSIS Event Handlers and Error Logging:

Fuzzy Grouping:

Fuzzy Look Up:

SSIS Performance Tuning:

SSIS Check Points:

Multicast Transformation vs Condition Split:


Sql Server Performance tuning: <–do Check the comments to the post



User Defined Datatypes:

Performance Related:

Rank, Dense_Rank, row_number:




Single tier – MS access, MS excel- Single file – direct acess —usually single user application

2 tier – Client – Server application  –multi user apps.

Multi Tier applications?

–      3 tier or N tier

–      Presentation Layer , Business Logic and Database Layer

  • Presentation Layer – UI or Front end
    • Handles User interactions – Screen validations
    • Should not contain business logic
  • Business Logic Layer – Middle ware or Back end
    • Set of rules to process the information
    • Should not contain presentation or data access code
  • Data Layer
    • Database , Datasets , RDBMS\DBMS
    • Provide access to back-end i.e. database

3 Tier Architecture Advantages:

–      Easy to maintain

–      Components are resusable

–      Faster development by division of work

  • Web designer can do presentation
  • Software engineer can do the logic part
  • DB devloper and admin can handle the backend and data modeling

Design Patterns?

Solution to common problems defined by gang of four programmers.

  • Factory  –  Creates an instance of several derived classes
  • Abstract Factory –  Creates an instance of several families of classes
  • Singlton – single instance of a class can exists
  • Builder – Separates object construction from its representation

data modeling?  

  • Process of defining and analysing data requirements to support business  processes.
  • ER diagrams are designed in VISIO to model the data requirements

SDLC Models?

Software development life cycle :

  • Waterfall model
  • Rapid application development Model
  • Incremental\Iterative Model
  • Proto-type Model
  • Spiral Model

Waterfall model – requirement garthering, designing, implementation, verification\testing, maintainace

  • Each phase has to be completed before the next starts
  • A review takes place after a particular phase completes and the next starts. – review is with the aim to check if the project is on right path.
  • No overlapping of phases
  • Easy to use and implement
  • Not good for projects where requirements changes frequently.
  • Poor model for complex projects

Incremental\Iterative Model This method doesn’t require to start with full requirement.

  • Also called Multi – waterfall model
  • Development and delivery is broken down into increments

Proto-type Model In this model , a proto-type (an early approximation of a final system or product) is build for user acceptance and the improvements are done untill the users are convinced.

Spiral Model  Combined features of proto-type and waterfall model.

  • Similar to incremental model
  • Planing, Risk analysis, engineering and evaluation

Rapid application development Model Based on component integration techniques

  • Short development cycle and linear squential model
  • Phases: business modeling , data modeling , process modeling, application modeling, testing.

White box testing(tranasparent testing) – internal – logical\data flow testing

Black box testing – input and desired output testing, not concerened with the inner code.

Grey box à combination of white and black box

SSIS – Tasks?

  • Data Flow task –extracts data from source, allow transformations, and the load data into target data destination
  • File System task – copy/move/delete files and folder over a file system
  • FTP task  – copy/move/delete files and folder over a FTP
  • Execute package
  • Execute SQL
  • Execute Script
  • Send Mail
  • Execute sql server agent job
  • Notify operator
  • Back up database
  • Bulk insert
  • For Loop:  Repeat a task a fixed number of times
  •  Foreach Loop: Repeat a task by enumerating over a group of objects

SSIS Auditing and Error Handling?

SSIS deploying packages:

  1. Database – schedule using Jobs
  2. File system – schedule using control M
  3. Can be called via command prompt or execute the .dtsx package

Sql Server

Normalisation – process of organising data in form of table and define relationship and hence form a realtional database.

RDBMS – ACID – atomicity , consistency, isolation, durability

1 NF – eliminating redundant groups

2 NF – eliminating redundant data

3NF – Eliminate Columns Not Dependent On Key

Trigers – DDL (drop table, create table, alter table or login events)

DML (insert update delete)

Instead off – fired instead off insert update delete

SSRS – variables – overview?

Delete duplicate rows

WITH CTE (COl1,Col2, DuplicateCount) AS
 (SELECT COl1,Col2,
 FROM DuplicateRcordTable)
 DELETE FROM CTE WHERE DuplicateCount >1

Master – contain information for all databases

Model – template for database —

MSDB –dts packages, jobs

Resource: read only – contain all system objects – sys.objects

Split Function: write a split function to get output of (1,2,3)  in a table in different rows

Scope Identity and @@identity


It returns the last identity value generated for any table in the current session, across all scopes.

Let me explain this… suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then @@IDENTITY returns that identity record which is created by trigger.


It returns the last identity value generated for any table in the current session and the current scope.

Let me explain this… suppose we create an insert trigger on table which inserts a row in another table with generate an identity column, then SCOPE_IDENTITY result is not affected but if a trigger or a user defined function is affected on the same table that produced the value returns that identity record then SCOPE_IDENTITY returns that identity record which is created by trigger or a user defined function.


It returns the last identity value generated for a specific table in any session and any scope.

In other words, we can say it is not affected by scope and session, it only depends on a particular table and returns that table related identity value which is generated in any session or scope.

Date , date part functions

Case and if statement: difference , syntax

SELECT   ProductNumber, Category =
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'Other sale items'
ELSE 'Not for sale'
FROM Production.Product
ORDER BY ProductNumber;

‘If statement’ cannot be used inside select statement <— v important

Table variable and #table difference – advantages and disadvantages:

major disadvantage of table variable

  • Table variables are Transaction neutral. They are variables and thus aren’t bound to a transaction.
  • Temp tables behave same as normal tables and are bound by transactions

Power of CTE

Can two different SPs create same #table or ##table (temp tables)

Rank dense rank row number

Pivot and unpivot

Find the top third row from a dataset

Find the day(Monday, Tuesday etc) on the first day of the current month (lets say today is 25th feb)

Steps for optimisation

OOPS Concepts? Inheritance, polimorphism (operation overloding and overridding) , encapsulation,abstraction

Boxing – converting a value type to reference type

unboxing — reverese i.e. converting a reference type to value type

Delegates – function pointers

Cloud computing

Cloud computing is the use of computing resources (hardware and software) that are delivered as a service over a network (typically the Internet). The name comes from the use of a cloud-shaped symbol as an abstraction for the complex infrastructure it contains in system diagrams. Cloud computing entrusts remote services with a user’s data, software and computation. There are many types of public cloud computing:[1]

  • Infrastructure as a service (IaaS)
  • Platform as a service (PaaS)
  • Software as a service (SaaS)
  • Network as a service (NaaS)
  • Storage as a service (STaaS)
  • Security as a service (SECaaS)

In the business model using software as a service, users are provided access to application software and databases. The cloud providers manage the infrastructure and platforms on which the applications run. SaaS is sometimes referred to as “on-demand software” and is usually priced on a pay-per-use basis. SaaS providers generally price applications using a subscription fee.


  • SaaS allows a business the potential to reduce IT operational costs by outsourcing hardware and software maintenance and support to the cloud provider.
  • This enables the business to reallocate IT operations costs away from hardware/software spending and personnel expenses, towards meeting other IT goals.
  • In addition, with applications hosted centrally, updates can be released without the need for users to install new software.


  • One drawback of SaaS is that the users’ data are stored on the cloud provider’s server. As a result, there could be unauthorized access to the data.
  • Basically from the security point of view it is not the safest as compared to others.

Software as a Service (SaaS)

Software as a service (SaaS) sometimes referred to as “on-demand software”, is a software delivery model in which software and associated data are centrally hosted on the cloud. SaaS is typically accessed by users using a thin client via a web browser.e.g. Facebook.

Leave a comment »