Posts Tagged ‘SSIS’

Execute SQL Task

April 27th, 2010 by aabhaanjan

SSIS Tasks :-

Execute SQL Task:

  1. 1. Execute SQL Task: – Execute sql task execute a stored procedure or sql query.

Process to create a package:-

a)      Open Business intelligence Development studio (BIDS) à select Fileà New Project àchoose Integration service Project

BIDS

a)      Put the project name and press ok button .The Project will be created and screen will be look like this and you will find default package.dtsx package file in solution explorer.Rename the package name whatever you would like to change .

Execute_SQL_task

a)      Drag and drop Execute Sql Task control to perform some operation into database and double click on Execute SQL Task  .The following screen will be appear :-

Execute_SQL_task.1

d)Select Connection Type , Click connection to establish connection with database.

Execute_SQL_task.4

e)      Enter the sql Query or procedure in SQLStatement Properties and click OK button.

Execute_SQL_task.5

e)      You have done all process ,Now your package is ready to execute . Press F5 to run the package .When you will press the F5 , the Package will be execute and data will be inserted into database and screen will be look like this .

Execute_SQL_task.6

Tags: , ,

The SSIS Package Designer

April 27th, 2010 by aabhaanjan

The SSIS Package designer contain design pane where we create or design our DTS package .The SSIS package designer tool provide a way to create a workflow which represent how the data flow .The Designer include four  tabs

1. Control folw

2.  Data Flow

3. Event Handlers

4. Package Explorer

1

Control Flow :- It contain workflow parts of the package .Control flow give a pictorial representation how the data flow from heterogeneous source. Control flow also include Task and constraints (Success , failure)

Control Flow include :-

  1. Tasks: – Task basically unit of work.
  2. Constraints :-Evaluate success , failure and completion

Data Flow :- It contain the data flow task ,it define how the data flow from source to destination .Data Flow task includes Data Flow Source, Data Flow Transformation, Data Flow Destination .

  1. Data Flow Source :-The Data Flow Source is the source from where you want to transfer the data .such as DataReader source,Excel Source,OLEDB Source etc .
  2. Data Flow Transformation :-Data Flow transformation provide a way to cleaning the data .Transformation can perform task such as updating ,cleaning , mearging and distributing data.Such as Fuzzy Lookup,Data conversion,Conditional split etc .
  3. Data Flow Destination :- A destination is component that store or move the data to a specific data stores .such as OLE DB Destination etc

3. Event Handlers :- The Event handlers allow you to handle errors or changes in events .

4. Package Explorer: – The Package explorer tab all the package designiner view to a single view . Package Explorer show the connection manager which you have already configured during in data flow or control flow .

Executing Package :- The final step of SSIS package is executing a package by clicking F5.

Tags: , , , ,

SSIS

April 9th, 2010 by aabhaanjan

SSIS: – SSIS is most important part of Sql Server 2005 . SSIS is the way to transfer the data from one source to another source .SSIS is robust way to load data or transfer the data from different source and perform tasks in a workflow.

Architecture of SSIS

The SSIS Architecture consists of four main components

  1. The SSIS Services
  2. The SSIS runtime engine and the runtime executables
  3. The SSIS data Flow engine and the data flow components
  4. The SSIS clients

SSIS Package:

When we are creating SSIS package .You will get following object in Solution Explorer. The solution explorer window consist SSIS package, connection or Data Sources and data source views. The Screen is given below

SSIS1

  1. Data Source :- Data source basically is connection that can be used for source or destination to transfer the data . Process to create Data Source
  2. Data Source Views (DSV):- Data Source views allow you to create a logical views of your data. Basically Data source views are collection of tables , views , stored procedure and queries that are shared across our projects .DSVs are deployed as a connection manager.
  3. SSIS Packages :-A Package is a collection of tasks that execute in orderly. SSIS Packages basically provide the user interface to transfer the data from one source to another source They can be used to merge data from heterogeneous source or transfer data from heterogeneous data source .The primary use for SSIS package  is flexible tool for data extraction , transformation and loading (ETL).This is an important part of data warehousing .

The tool also used to update multidimensional cube data

Tags: , , , , ,