SSIS are 2 types
ETL Operations (Extracting Transform Load)
E- Extracting –> Getting data
T- Transform –> performing intermediate operations
L- Load –> Load to destination
DB <-> DB
DB <-> File
File <-> File
Databases: Oracle, SQL server, Tara data etc..
Files: XML, Excel, Flat file. Raw file etc…
There are 4 important components in SSIS ARCHITECTURE
SSIS Designer: It’s a native tool to create ‘IS’ packages and it components.
Object model: It is an application programming interface which connects and understands custom tools and components.
SSIS runtime: This is ‘CLR’ which saves the layout of the packages (.dtx) runs the packages and manages the package components.
Integration services service: This component helps us to store the packages in SQL server_database (MSDB), managing the packages and running the packages.
To move the data between source to destination and perform different operations, data flow task is required.
It uses various “Inline buffers” while processing the data.
It uses a “data pipe line engine” to move the data from source to destination and to manager buffers.
Package is on important component in SSIS Architecture.
ENGINES IN SSIS
–> SQL engine (generates plan to execute package)
–> Data pipe line engine (only inside data flow task)
Different between DTS and SSIS
|SQL server 7.0 introduced available 2000 on words.||SQL server 2005 on words available.|
|Designed for ETS [Extract Transform Sources].||Designed for ETL [Extract Transform Load].|
|It consists of single pane (i.e., pane means screen or frame) for all operation. It has data transformations work flow etc.||It consists of multiple pane for multiple operations. It has
|Data transformations available.||Data flow task introduced and transformations embedded.|
|No DSV [Data Source View] No connection manage, No event handling, No looping through folders, files.||Available [introduced].|
|Message boxes displayed in active –X script.||Message boxes displayed in script task.|
|Less transformation.||More transformation.|
|Partial BI support (less)||Full support to BI.|
|No deployment wizard||Deployment wizards are there.|
|Saved in||Saved in local file system. Deployed to SQL server.|
|a) Enterprise manager (SQL server)|
|b) File system (structured storage file )
–> Max 25 instances in CMS.
–> Max 256 logical processors in CMS.
–> Multi server administrator.
–> MDS [Master Data Services].
–> “Data-tier” applications
–> POWPIVOT for virtualization
–> Full support to
–> Data compression with UCS-2 code support.
–> Available edition
i) Data centre edition
ii) Parallel DWH edition
2008R2 to 2011:
èIts code name “DENAIL”
èMulti sun-net failover clustering introduced.
Creating sequence introduced.
Syntax: create sequence START WITH increment by . Eg: create sequence x start with 1 increment by 1. Insert into test values (Next value for x, ”vinay” –> emp ID or username)
Paging implement in 2011:
Get Updates on Tech posts, Interview & Certification questions and training schedules