Variables in SSIS:

Variables in SSIS:

System Variables	User Defined Variables
1 To hold system info	1 To hold User Given data    & created by user only
2 Pre Defined	2 User Defined
3 READ Only	3 READ & WRITE
4 Stored under System :: <Name Space>	4 Stored under USER ::  <Name Space>
Usage: used for Monitoring the packages	Usage : to implement Dynamic behavior for executable we use User Defined Variables

Variable Scopes: 1. Package Level     2. Task Level

How to debug an SSIS Package after its Execution

Q:: How can we see Package Execution Process wether it is success/failure , errors, Bottle necks.?

Ans: We can observe the execution information in

2-ways            

1.      Progress Tab                2. Log Providers

1. Progress Tab:

It contains
     --How package is validated
     --Step by step execution from starting to ending
In general it displays

     --The no of rows operated

     --Sourc & Destination Connections

     --The amount of time take bw one stmt to other stmt…etc

2.Log Providers:

                           we will discuss soon...............

SSIS Task Colors while Execution of a Package

Colors:

1.      White              – for Ready to Execute

2.      Yellow            – for Running

3.      Red                 – for Fail

4.      Green              – for Success

5.      Gray                – for Disable

How many ways we can execute the packages?

In How many ways we can execute the packages?

1)      By Pressing F5

2)      Debug menà start Debugging

3)      SolutionExploreàPackagesàR.CàExecute Pacakage

4)      SSMS à Integration Services à MS_DB à Package à R.C à Run Package

5)      By using

a.       DT   UTIL

b.      DT   UTIL Exec

SSIS Architecture:

SSIS _ Practical Architecture:

Solution (collection of Projects)

            -Project (collection of packages)

                        -Package (Collection of 1/more tasks)

Package:

1.      Contlow Flow Tasks

2.      Data Flow Tasks

3.      Package Explorer

4.      Event Handling

5.      Parameters ( Newly added in 2012 onwards)

Sollution Explorer Contains :

1.      Projects

2.      Packages

3.      DSVs

4.      Data Source Info

Data Source Info: is used to connect to the DB

            Data Source can be Reusable across the “packages”               

DSV : is a Logical Object for the Physical collection of tables/views in Data Sources

Conn_Mgrs: For every connection we can take a name that name can be ReUsable across the Program/pkg.

1.      In case of FLAT File : (File Conn_Mgr)

            Folders & the FileName taken as “Connection String” for the Conn_mgr

2.      In case of DB Source:  ( DB Conn_Mgr)

ServerName & DB_Name is taken as Part of “Connection String” in Conn_Mgr.

MSBI 2005 & 2008 Differences:

MSBI 2005 & 2008 Differences:

2005	2008
1 No DateTime Data Types  in 2005	1 DateTime data types added in 2008
2 No Spatial Data types	2 Spatial Data types added
3 No GEOSPATIAL applications in 2005	3 GEO SPATIAL Applications added in 2008
4 No CMS	4 CMS added
5 No PBM Server	5 PBM added
6 SSNS is there	7 SSNS is removed

CMS means “Control Mgt Server” : it manages all the instances under singer Manager Server Interface.

PBM means (Policy Based Mgt Server) : There are Common Policies implemeneted between various instances in CMS

Differences B/w Sql Server 2005 & Sql Server 2000

Differences B/w Sql Server 2005 & Sql Server 2000 (According to MSBI)

2000	2005
1. DTS File  to  File DB  to  DB DB  to  File ( ,dts  extension )	1 SSIS (SQL Server Integration Services) (extension is .dtsx)
2 M AS	2 SSAS
3 No Reporting	3 SSRS
4 No Notifications	4 SSNS ( Notification Services) But in 2008 removed
5 No XML Data Services	5 XML Data Services added
6 No MDX & XMLA service	6 MDX & XMLA introduced

Notification Services means: like Sending an Email When SSIS execution is successful…etc

MSBI Features:

MSBI Features:

1. SSIS  : it acts like ETL to perform Load & Unload Operations
2. SSAS : It creates Multidimensional Objects & Provides Multidimensional Analysis
3. SSRS : Generate Reports From IS Loaded Table & AS created Data Base
4. SSNS : sends/Receives notifications according to the Events.                                                                    Eg: Sending an Email when SSIS execution is Successful/ Over.
5. Full Support to MS-Office
6. Full Support to Data Mining
7. Full Support to Report Builder
8. Full Support to Proactive Caching
9. Hosted CLR : However .NET Languages running under a single Run Time(CLR)                     Similarly MSBI also runs under CLR
10. Advanced TSQL : They are many keywords added in 2005 to satisfy the demands of BI 

Why MSBI , Why not Others & its Compitative Advantages

Why MSBI? Or   Compitative Advantages?

1.       Msbi is a GUI based complete Ene-to-End Solution

2.       complete Ene-to-End Solution

3.       Full Support to .Net, WebService

4.       Msbi can easily integrate with All Front End Applications like

a.       .Net

b.      Share Point

5.       Low Cost Tool /  Low Cost TCO ( Low Cost Owner Ship)

1.      Easy to Install

2.      Easy to Use

3.      Easy to Maintain

4.      Cheaper Price

5.      Full Support to GUI

6.       MSBI is Highly Scalable

Means MSBI Supports Multiple Instances to connect to the server without sacrificing its performances.

Instances means : Users/Servers/Work

i.e.., MSBI is a LENEAR SCALABLE Application.

(upto 2008 MSBI Is Not Linearly Scalable)

7.       However different languages runs @ CLR

In the same way Our MSBI also runs uner CLR

8.       Msbi is lenear Scalable Application due to having

1.      CMS  (Central Mgt Server)

2.      PBM  (Policy Based Mgt) Server ..etc.

9.       MSBI also Supports SEMI WareHousing Application.

Differences B/W DTS and SSIS

DTS	SSIS
1.DTS is available in 2000	1. SSIS is available from 2005 onwards
2 DTS designed for DTS(Data Transform Sources)	2 SSIS designed for ETL
3 DTS has Single pane for all operations	3 SSIS is having multiple panes/Screens for multiple operations
4 Data Transformations available	4 Data Transformations are embedded in Data Flow Task
5 No Conn_Mgr,    No Event_Handling    No Looping through files & Foleder	5 Conn_Mgr, Event_Handling, Looping_Through Files & Folders are available
6 Message Boxes displayed in “ActiveX Script Task”	6 Message Boxes displayed in Script Task
7 DTS has Less Transformations	7 SSIS has More Transformations
8 DTS is Partial BI	8 SSIS is Full Support to BI
9 No Deployment Wizard	9 Deployment Wizard is there
10 Saved in         SQL Server         File System	10 Saved in Local File System & Deployed in SQL Server