Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. SQL Server addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by SQL Server connectivity.

SQL Server Basics

SQL Server provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a SQL Server solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations.

SQL Server supplies a number of tasks that can be accessed graphically or programmatically. These tasks cover a wide variety of data copying, data transformation, and notification situations. For example:

• Importing and exporting data
• Transforming data
• Copying database objects
• Sending and receiving messages to and from other users and packages
• Executing a set of Transact-SQL statements or Microsoft ActiveX® scripts against a data source

Using SQL Server

The SQL Server interface consists of a work area for building packages, toolbars containing package elements that you can drag onto the design sheet, and menus containing workflows and package management commands.

Connections: Accessing and Moving Data

To successfully execute SQL Server tasks that copy and transform data, a package must establish valid connections to its source and destination data and to any additional data sources. When creating a package, you can configure connections by selecting a connection type from a list of available OLE DB providers and ODBC drivers.

Tasks: Defining Steps in a Package

A SQL Server package usually includes one or more tasks. Each task defines a work item that may be performed during package execution. Some of the tasks generally used in SQL Server are:

• Transform Data task
• ActiveX Script task
• Send Mail task

Workflows: Setting Task Precedence

When you define a group of tasks, there is usually an order in which the tasks should be performed. In SQL Server, you manipulate tasks on the design sheet and use precedence constraints to control the sequence in which the tasks execute.

Scenario

Assume that you want to transfer data from one table to another table using some parameters. One can pass the date values to the query by declaring “Global Variables”. Before transferring the data, you can check the date values using ActiveX Script. After the data transfer is complete, you can send a mail to convey the status of the task as “Success” or “Failure”.

Creating a Connection object

To establish a connection, you can mention the connection details of the database server.

Creating Global Variables

To pass the variable(s) to a query, you need to create global variable(s). These variable(s) can also be used in ActiveX Script.

Creating ActiveX Script

You can write your own ActiveX Script in VB language. Here the script is used to validate the date stored in global variables.

Creating a Transform Data Task

You use the Transform Data task to copy data between a source and destination and to optionally apply column-level transformations to the data.

Managing Transactions between two or more tasks

Transactions play a very important role in databases. You can set transactions between two tasks to ensure data integrity.

Send Mail Task

The Send Mail task lets you send an e-mail as a task. For example, you can notify a database administrator about the success or failure of a particular task.

Workflow Properties

Using workflow tasks, you can prioritize which task needs to be executed after another task. You can set the precedence of the various tasks.

Error Logging

You can log errors and track the execution of a SQL Server package using various logging options.

Executing the Package

To execute the package, you can click “Execute” from the Package Menu.

Saving the Package

To save a SQL Server package, you have multiple options available such as Meta Data Services, SQL Server, Structured Storage File, and Visual Basic File.

Loading a Package

You can open an existing package from a .dts file and save it to your server.

SQL Server Package Passwords

You can use SQL Server package passwords to secure your packages and control access to them.

By following these steps, you can effectively use SQL Server to extract, transform, and consolidate data from disparate sources into single or multiple destinations.

Author: Rama Nageshwara Rao

Email: rama.nageshwara@wipro.com

Profile: Senior Project Engineer at WIPRO TECHNOLOGIES, INDIA