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