Friday 12 September 2014

Execute SQL Task

Introduction:

  • Execute SQL task is a very common task used inside SSIS packages.
  • As the name says,it can execute a SQL query inside the control flow.

How to configure:

  • Execute SQL task is present inside the toolbox in the control flow tab of a package.
  • Drag the task and place it inside the control flow
  • Double click on the task.

  • The Execute SQL task editor will appear where you can see all different configurable properties of the task.
  • Both DML and DDL SQL scripts can be executed by using the Execute SQL task.
  • In this blog we will see how to execute a SQL script which doesn't return any result set.

Executing a SQL Statement which doesn't return any result set:

In this demo a create statement will be executed by Execute SQL task which will not return any result set.
  • Let's say there is a DB named test.
  • We are going to create a table named EMP with the following attributes.

  • To execute the above statement inside the Execute SQL task we need to create a connection to the TEST Database. Please refer the following post to get the details regarding creating a connection:Creating connection manager in ssis
  • Once the connection manager is created for the Database where the table need to be created go the Execute SQL Task editor.
  • Chose Connection manager name from the Connection drop down
  • Then place the SQL Statement inside the SQL Statement textbox.
  • After that click OK and execute the package
  • Once the package executed successfully you can see a EMP table is created inside the server mentioned in the OLEDB connection manager.
  • If you try to re-execute the package it will throw error as the table is already created.

Creating connection manager in SSIS

Introduction:

  • Creating a connection manager is the first step of creating a Package and is a very important part.
  • After creating connections only we can do operations inside databases and files.

OLEDB Connection:

  • OLEDB connection is required to do any operations inside a Database.
  • Here we will be creating a OLEDB connection to a SQL Server.
  • To create a connection manager go to the connection manager pan in a package.
  • Right click on it and choose 'New OLEDB connection' option.
  • Click on 'New'.
  • Fill the Server information like Server Name, Authentication type, choose the Database and then click test connection.
  • If all the information is correct you will get the following pup-up.
  • Click OK and a new connection manager will be created inside the Connection manager Pan.

Thursday 11 September 2014

Getting started with SSIS

What is ‘SSIS’:


  • ·         SQL Server integration services (SSIS) is a tool used to Extract, Transform and Load (ETL) data in different databases.
  • ·         SSIS is a Microsoft product lunch with SQL Server 2005. Prior to this it was known as DTS services.

Pre-requisite:


  • ·         Install SQL Server 2005 or any other version later to it. This will automatically install SSIS Engine to your system.
  • ·         Open SQL Server Business Intelligence Development studio (BIDS) to create SSIS package. 

Getting Started:


  • ·         To create a SSIS package first a Solution and a SSIS Project need to be created.
  • ·         A solution is a collection of different types of project like: SSIS project, SSMS project, SSAS project and different C# projects.
  • ·         A SSIS Project is a collection of SSIS packages and other files related to the project.
  • ·         To create a solution go to File->New->Project
  • ·         Click on Integration Services Project.



  • ·         Fill the Name and location. By default the solution name will be the Name of the project. If you want you can customize the name of the solution. Then press OK. 

  • ·         Once you click OK a solution and a project will be created along with a default package.

Getting familiar with BIDS:


  • ·         The following screen will appear once you click OK.
  • ·         The following panes are present inside the BIDS:
o   Solution Explorer
o   Properties
o   Toolbox
o   Variables
o   Package
§  Control Flow
§  Data Flow
§  Event Handlers
§  Package Explorer
§  Connection Manager

Solution Explorer:

·         Shows all the Data Sources, Data views, Packages and files preset inside the solution.

Properties:

·         Shows the property of the selected component.

Toolbox:

·         Contains different tasks when the Control flow tab is open.
·         Contains different transformations when the Data flow tab is open.

Variables:

·         Contains all the user defied and system defined variables present inside the package.

Package:

·         Package is a collection of task that is to be performed to do the ETL.
·         It’s a XML file containing all the ETL instructions inside the XML tags and saved with .dtsx extension.
There are four tabs present inside the package area:

Control Flow:

·         Contains all tasks present inside a package.
·         Task is the granular level of work that a package performs. For example: Execute SQL Task, Data Flow task, Send mail task

Data Flow:

·         Data flow contains source where the data need to be extracted, transformations which modifies data according to the business requirement and target where the transformed data going to reside.

Event Handler:

·         Event handler also contains task like the control flow. The only difference is the tasks will trigger only when the associated event happens.
·         The events can be associated on the package level, container level or on the task level.

Package Explorer


·         Contains the hierarchical structure of all the components present inside a package.