Tuesday, December 23, 2008

Architecture of Integration Services-SQL Server

SSIS Designer :
SSIS Designer is a graphical tool that you can use to create and maintain Integration Services packages. SSIS Designer is available in Business Intelligence Development Studio as part of an Integration Services project. Runtime engine : The Integration Services runtime saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions.



















For more information, see Integration Services Packages.
Tasks and other executables :
The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes. Run-time executables also include custom tasks that you develop.
For more information, see Integration Services Tasks, Integration Services Containers, and Integration Services Event Handlers.

Data Flow engine (also known as the pipeline) and Data Flow components :
The Data Flow task encapsulates the data flow engine. The data flow engine provides the in-memory buffers that move data from source to destination, and calls the sources that extract data from files and relational databases. The data flow engine also manages the transformations that modify data, and the destinations that load data or make data available to other processes. Integration Services data flow components are the sources, transformations, and destinations that Integration Services includes. You can also include custom components in a data flow.
For more information, see Data Flow Task and Data Flow Elements.
API or object model :
The Integration Services object model includes managed application programming interfaces (API) for creating custom components for use in packages, or custom applications that create, load, run, and manage packages. Developer can write custom applications or custom tasks or transformations by using any common language runtime (CLR) compliant language.
For more information, see Developer's Guide (Integration Services).
Integration Services Service :
The Integration Services service lets you use SQL Server Management Studio to monitor running Integration Services packages and to manage the storage of packages.
For more information, see Integration Services Service and Introducing SQL Server Management Studio.
SQL Server Import and Export Wizard :
The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. This wizard also offers the simplest method to create an Integration Services package that copies data from a source to a destination.
For more information, see Importing and Exporting Data by Using the SQL Server Import and Export Wizard.
Other tools, wizards, and command prompt utilities :
Integration Services includes additional tools, wizards, and command prompt utilities for running and managing Integration Services packages.
For more information, see Integration Services Wizards and Command Prompt Utilities (Integration Services).

Via : http://msdn.microsoft.com/en-us/library/bb522498.aspx

Sql Server Integration Services-SSIS

What is SSIS: the data ware housing platform from microsoft
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

  • Typical Uses of Integration Services:
    Integration Services provides a rich set of built-in tasks, containers, transformations, and data adapters that support the development of business applications. Without writing a single line of code, you can create SSIS solutions that solve complex business problems using ETL and business intelligence, manage SQL Server databases, and copy SQL Server objects between instances of SQL Server.
    The following scenarios describe typical uses of SSIS packages.
    1)Merging Data from Heterogeneous Data Stores :
  • Data is typically stored in many different data storage systems, and extracting data from all sources and merging the data into a single, consistent dataset is challenging. This situation can occur for a number of reasons.
  • For example:
  • Many organizations archive information that is stored in legacy data storage systems. This data may not be important to daily operations, but it may be valuable for trend analysis that requires data collected over a long period of time.
  • Branches of an organization may use different data storage technologies to store the operational data. The package may need to extract data from spreadsheets as well as relational databases before it can merge the data.
  • Data may be stored in databases that use different schemas for the same data. The package may need to change the data type of a column or combine data from multiple columns into one column before it can merge the data.
  • Integration Services can connect to a wide variety of data sources, including multiple sources in a single package.
  • A package can connect to relational databases by using .NET and OLE DB providers, and to many legacy databases by using ODBC drivers. It can also connect to flat files, Excel files, and Analysis Services projects.
  • Integration Services includes source components that perform the work of extracting data from flat files, Excel spreadsheets, XML documents, and tables and views in relational databases from the data source to which the package connects.
  • Next, the data is typically transformed by using the transformations that Integration Services includes. After the data is transformed to compatible formats, it can be merged physically into one dataset.
  • After the data is merged successfully and transformations are applied to data, the data is usually loaded into one or more destinations.
  • Integration Services includes destination for loading data into flat files, raw files, and relational databases. The data can also be loaded into an in-memory recordset and accessed by other package elements.
    2)Populating Data Warehouses and Data Marts :
  • The data in data warehouses and data marts is usually updated frequently, and the data loads are typically very large.
  • Integration Services includes a task that bulk loads data directly from a flat file into SQL Server tables and views, and a destination component that bulk loads data into a SQL Server database as the last step in a data transformation process.
  • An SSIS package can be configured to be restartable. This means you can rerun the package from a predetermined checkpoint, either a task or container in the package. The ability to restart a package can save a lot of time, especially if the package processes data from a large number of sources.
  • You can use SSIS packages to load the dimension and fact tables in the database. If the source data for a dimension table is stored in multiple data sources, the package can merge the data into one dataset and load the dimension table in a single process, instead of using a separate process for each data source.
  • Updating data in data warehouses and data marts can be complex, because both types of data stores typically include slowly changing dimensions that can be difficult to manage through a data transformation process.
  • The Slowly Changing Dimension Wizard automates support for slowly changing dimensions by dynamically creating the SQL statements that insert and update records, update related records, and add new columns to tables.
  • Additionally, tasks and transformations in Integration Services packages can process Analysis Services cubes and dimensions. When the package updates tables in the database that a cube is built on, you can use Integration Services tasks and transformations to automatically process the cube and to process dimensions as well.
  • Processing the cubes and dimensions automatically helps keep the data current for users in both environments; users who access information in the cubes and dimensions, and users who access data in a relational database.
  • Integration Services can also compute functions before the data is loaded into its destination.
  • If your data warehouses and data marts store aggregated information, the SSIS package can compute functions such as SUM, AVERAGE, and COUNT. An SSIS transformation can also pivot relational data and transform it into a less-normalized format that is more compatible with the table structure in the data warehouse.
    3)Cleaning and Standardizing Data :
  • Whether data is loaded into an online transaction processing (OLTP) or online analytic processing (OLAP) database, an Excel spreadsheet, or a file, it needs to be cleaned and standardized before it is loaded.
  • Data may need to be updated for the following reasons:
  • Data is contributed from multiple branches of an organization, each using different conventions and standards. Before the data can be used, it may need to be formatted differently. For example, you may need to combine the first name and the last name into one column.
  • Data is rented or purchased. Before it can be used, the data may need to be standardized and cleaned to meet business standards. For example: an organization wants to verify that all the records use the same set of state abbreviations or the same set of product names.
  • Data is locale-specific. For example: the data may use varied date/time and numeric formats. If data from different locales is merged, it must be converted to one locale before it is loaded to avoid corruption of data.
  • Integration Services includes built-in transformations that you can add to packages to clean and standardize data, change the case of data, convert data to a different type or format, or create new column values based on expressions. For example, the package could concatenate first and last name columns into a single full name column, and then change the characters to uppercase.
  • An Integration Services package can also clean data by replacing the values in columns with values from a reference table, using either an exact lookup or fuzzy lookup to locate values in a reference table.
  • Frequently, a package applies the exact lookup first, and if the lookup fails, it applies the fuzzy lookup. For example, the package first attempts to look up a product name in the reference table by using the primary key value of the product. When this search fails to return the product name, the package attempts the search again, this time using fuzzy matching on the product name.
  • Another transformation cleans data by grouping values in a dataset that are similar. This is useful for identifying records that may be duplicates and therefore should not be inserted into your database without further evaluation. For example, by comparing addresses in customer records you may identify a number of duplicate customers.
    4)Building Business Intelligence into a Data Transformation Process : A data transformation process requires built-in logic to respond dynamically to the data it accesses and processes.
  • The data may need to be summarized, converted, and distributed based on data values.
  • The process may even need to reject data, based on an assessment of column values.
  • To address this requirement, the logic in the SSIS package may need to perform the following types of tasks:
  • 1)Merging data from multiple data sources.
    2)Evaluating data and applying data conversions.
    3)Splitting a dataset into multiple datasets based on data values.
    4)Applying different aggregations to different subsets of a dataset.
    5)Loading subsets of the data into different or multiple destinations.
    Integration Services provides containers, tasks, and transformations for building business intelligence into SSIS packages.
  • Containers support the repetition of workflows by enumerating across files or objects and by evaluating expressions.
  • A package can evaluate data and repeat workflows based on results. For example, if the date is in the current month, the package performs one set of tasks; if not, the package performs an alternative set of tasks.
  • Tasks that use input parameters can also build business intelligence into packages. For example, the value of an input parameter can filter the data that a task retrieves.
  • Transformations can evaluate expressions and then, based on the results, send rows in a dataset to different destinations. After the data is divided, the package can apply different transformations to each subset of the dataset. For example, an expression can evaluate a date column, add the sales data for the appropriate period, and then store only the summary information.
  • It is also possible to send a data set to multiple destinations, and then apply different sets of transformation to the same data. For example, one set of transformations can summarize the data, while another set of transformations expands the data by looking up values in reference tables and adding data from other sources.
    5)Automating Administrative Functions and Data Loading :
  • Administrators frequently want to automate administrative functions such as backing up and restoring databases, copying SQL Server databases and the objects they contain, copying SQL Server objects, and loading data. Integration Services packages can perform these functions.
  • Integration Services includes tasks that are specifically designed to copy SQL Server database objects such as tables, views, and stored procedures; copy SQL Server objects such as databases, logins, and statistics; and add, change, and delete SQL Server objects and data by using Transact-SQL statements.
  • Administration of an OLTP or OLAP database environment frequently includes the loading of data. Integration Services includes several tasks that facilitate the bulk loading of data.
  • You can use a task to load data from text files directly into SQL Server tables and views, or you can use a destination component to load data into SQL Server tables and views after applying transformations to the column data.
  • An Integration Services package can run other packages. A data transformation solution that includes many administrative functions can be separated into multiple packages so that managing and reusing the packages is easier.
  • If you need to perform the same administrative functions on different servers, you can use packages. A package can use looping to enumerate across the servers and perform the same functions on multiple computers.
  • To support administration of SQL Server, Integration Services provides an enumerator that iterates across SQL Server Management Objects (SMO) objects. For example, a package can use the SMO enumerator to perform the same administrative functions on every job in the Jobs collection of a SQL Server installation.

Source : http://msdn.microsoft.com/en-us/library

Monday, December 22, 2008

SQl server 2008 Features

T-SQL Data Types
SQL Server 2008's new date and time types.
The new DATE type is just a date, in the range of 0001-01-01 through 9999-12-31, which equates to Jan. 1, A.D. 1 through Dec. 31, A.D. 9999.Iit provides a simple date type usable for most business applications. SQL Server stores this information in three bytes, a vast improvement over the DATETime’s eight bytes, which is a nice savings when you don't need the overhead of the time data.
The equivalent TIME type has a range of 00:00:00.0000000 through 23:59:59.9999999, stored in five bytes at the default 100 nanosecond precision. The new DATETIME2 type doesn't show much imagination in how it was named, but it's useful, providing a larger date range, a larger default fractional precision, and optional user-specified precision stored in six to eight bytes. A new DATETIMEOFFSET type includes time zone information embedded in the type. The date and time enhancements in SQL Server 2008 make it much easier to work with temporal data, and the database stores the data much more efficiently.
SQL Server 2008 spatial data types.
SQL Server 2008 has built-in support for two kinds of spatial-data systems. The geometry types support planar or "flat-earth" coordinate data. The geography types store ellipsoidal data that stores locations on the earth's surface, a flattened sphere. Whether you're storing GPS data scattered around the globe or need to store the coordinates that define complex shapes on a rectangular surface, you'll find a lot of features in these data types, along with dozens of useful methods.
T-SQL Improvements:

The new version includes many features that make code simpler and more efficient. There are a few syntax enhancements that developers will like, including a couple that make T-SQL seem more like a "real" programming language. You can now declare and initialize variables in a single statement. Now this statement works: DECLARE @id int = 5
Another nice new programming feature is compound operators. There's no rocket science here; this code prints "6": DECLARE @id int = 5SET @id += 1PRINT @id
T-SQL supports compound operators for addition, subtraction, multiplication, division, and modulus, as well as the bitwise operators.
Sparse columns provide an efficient storage mechanism for data that consists of many nulls. When defined using the SPARSE keyword on a column definition, any nulls in the column require no storage space at all. There are a few restrictions; for example, you can't use sparse columns with a few complex data types such as the spatial types, image and text fields, or user-defined types. But using sparse types requires substantially less disk storage. You can also use sparse columns with column sets, which is untyped XML data that combines all the sparse columns in the table. Column sets are useful when the table contains many sparse columns and when working with these columns individually could prove tedious.
table-valued parameters (TVPs).
This one feature single-handedly will save you from a lot of ugly T-SQL code. Have you ever had to pass several pieces of data as a parameter to a stored procedure? Maybe it was a comma-delimited list or some other array-like structure. You'd have to write some nasty parsing code to split up the values, then probably use a loop to process the data. SQL Server 2005 introduced a table data type, but you couldn't pass it to a procedure.
TVPs solve these kinds of problems elegantly by letting you pass -- as the name suggests -- a table-valued parameter to the procedure or function. Then, in the body of the procedure, you can use the set-based features of SQL to process the data, such as by inserting it into a persistent table.


Here's a simple example of using a TVP. There are four simple steps. First, create a persistent TABLE type with the schema to hold the data: CREATE TYPE MyTbl AS TABLE (ID INT, String NVARCHAR(100))GO
Next, create a stored procedure that accepts a parameter of the TABLE type you just created. As with any stored procedures, you can have as many parameters as you like, and any or all of them can be TVPs: CREATE PROCEDURE dkSelectFromTVP(@TVParam MyTbl READONLY)AS SET NOCOUNT ON SELECT * FROM @TVParamGO
Now declare a variable of your TABLE type and insert some data: DECLARE @TVP AS MyTblINSERT INTO @TVP(ID, String) VALUES (1, ‘Fairbanks')INSERT INTO @TVP(ID, String) VALUES (2, ‘Juneau')INSERT INTO @TVP(ID, String) VALUES (3, ‘Anchorage')INSERT INTO @TVP(ID, String) VALUES (4, ‘Denali')
Finally, run the stored procedure and pass in the TVP: EXEC dkSelectFromTVP @TVP
TVPs have a few restrictions; the worst of these is that the parameter is read-only. If you leave off the READONLY keyword in the stored-procedure definition, you'll get a syntax error. The SQL community is exerting some pressure on Microsoft to ease this restriction, so perhaps a future version will make TVPs even more flexible. But now, whenever you need to pass multiple pieces of data in a single parameter, you won't have to write messy parsing code to break it up.
A database scenario that developers often need to write code for involves taking a set of source data, updating a table based on that source data, and then applying changes as necessary. You might have to update some existing rows, insert new rows, or delete rows that are no longer needed. This can be tortuous T-SQL code to write, often requiring a dreaded cursor, which is a guaranteed drain on performance.
MERGE statement:
MERGE lets you define the source and target data, as well as the operations performed on the target data based on whether the source data matches the target data (using match definitions you specify). Assume you have a table with data about a set of working dogs, with fields such as Name, BirthDate, and HarnessSize. You gather a set of changes from a user and need to apply the changes to the Dogs table. You might do this by creating a TABLE type named typDogUpdates to store the source update data that the code will apply to the dbo.Dogs table (see Listing 1). You can then use the MERGE statement to create a TABLE variable of that type and populate it with data. In this case, the primary key dogID will be the basis for matching source and target data, but you can use any field or combination of fields you want. If SQL Server finds a matching dogID, it updates the row; otherwise, it inserts the record.
The MERGE statement consists of five main clauses. The MERGE clause specifies the target for the changes -- in this case, the persistent table Dogs. The USING clause specifies the source data that contains the changes you want to apply to the target. The ON clause specifies the join condition, how the source data is matched to the target data. One or more WHEN clauses let you specify what to do when the database finds matches and what to do when source data doesn't match anything in the target. In Listing 1, the code executes an UPDATE statement when there is a match. When the source data doesn't match a row in the target, it executes an INSERT statement. You can also delete data and apply various conditions to each action. Finally, the OUTPUT clause can return a result set that contains records for each row changed in the target data.

Other great enhancements to T-SQL in SQL Server 2008: Grouping Sets are an extension to the GROUP BY clause and provide functionality similar to ROLLUP and CUBE to define multiple groupings in a single query. You now have far more options for working with unstructured data, including enhancements to the XML data type and FILESTREAM, which lets you store blobs in the file system while letting SQL Server manage the files for you.
Full-text search:
SQL Server 2008, full-text search is completely integrated into the database instead of being stored externally. Portions of full-text indexing and querying are now integrated into the query optimizer, so performance is much better, and there are more tools to extract useful data from the database. You might want to consider dumping all that gnarly T-SQL code you wrote over the last decade to give users flexible searches into their data and implement full-text searches instead.
SQL Server 2008 supports Windows PowerShell,:
an enhanced, extensible scripting shell interface for developers and administrators who love the command line. SQL Server includes two PowerShell snap-ins that expose the hierarchy of database and server objects as paths (similar to file-system paths). On the surface, this sounds a bit like an abomination, but it can simplify getting around the database object model. Another snap-in implements a set of PowerShell cmdlets for performing a variety of actions such as running sqlcmd scripts. PowerShell is a powerful tool, but if you love your mice and GUIs, you can opt not to use it.

SQL Server 2008 Editions

SQL Server 2008 Enterprise: SQL Server 2008 Enterprise is a comprehensive data management and business intelligence platform that provides enterprise-class scalability, data warehousing, security, advanced analytics and reporting support for running business-critical applications. With this edition, it is possible to consolidate servers and perform large-scale online transactional processing.
SQL Server 2008 Standard: SQL Server 2008 Standard is a complete data management and business intelligence platform that provides best-in-class ease of use and manageability for running departmental applications.
SQL Server 2008 Workgroup: SQL Server 2008 Workgroup is a reliable data management and reporting platform that delivers secure, remote synchronization and management capabilities for running branch applications. This edition includes core database features and is easy to upgrade to the Standard or Enterprise edition.
SQL Server 2008 Web. SQL Server 2008 Web is designed for highly available, Internet-facing Web-serving environments running on Windows Server. SQL Server 2008 Web provides the tools necessary to support low-cost, large-scale, highly available Web applications or hosting solutions for customers.
SQL Server 2008 Developer: SQL Server 2008 Developer allows developers to build and test any type of application with SQL Server. This edition features all of the functionality of SQL Server Enterprise but is licensed only for development, test and demo use. Applications and databases developed on this edition can easily be upgraded to SQL Server 2008 Enterprise.
SQL Server 2008 Express. SQL Server 2008 Express is a free edition of SQL Server that features core database functionality including all of the new SQL Server 2008 data types, in a small footprint. This edition is ideal for learning and building desktop and small server applications, and for redistribution by ISVs.
SQL Server Compact 3.5: SQL Server Compact is a free embedded database designed for developers and is ideal for building stand-alone and occasionally connected applications for mobile devices, desktops and Web clients. SQL Server Compact runs on all Microsoft Windows platforms, including the Windows XP and Windows Vista operating systems, and on Pocket PC and smartphone devices.

IIS, Weblogic and Websphere

Similarities:
IIS, Weblogic and Websphere are all fully capable web servers. With the right tools behind them they will all provide identical look and feel to the visiting web user.
Differences :
The main difference between IIS and Weblogic/Websphere is what architecture is at the heart of the web server. IIS is based on MTS, COM and ASP. Weblogic/Websphere is based on the Java platform and includes support for EJB and JSP.

Microsoft grants Windows XP a reprieve

Some PC makers now have an extra four months to sell Windows XP.
The BBC reported Monday that Microsoft has extended the deadline for smaller PC builders and resellers to obtain licenses for the discontinued operating system from the previous deadline of January 31, 2009 to May 30, 2009.
"Microsoft is making accommodation through a flexible inventory program that will allow distributors to place their final orders by January 31, 2009; and take delivery against those orders through May 30, 2009," a Microsoft representative said in an e-mailed statement. "This is not an extension of sales."
Even after May 30, however, it's still not the end of XP. The operating system will be available on ultra-low-cost PCs until June 30, 2010, and the low-end Windows XP Starter Edition will continue to be available in emerging markets until the same date.
Plus, big PC makers plan to offer PCs with Vista Ultimate and Vista Business that have been factory downgraded at customers' request until July 30 next year.
Source : http://news.cnet.com/8301-10805_3-10128132-75.html?tag=newsEditorsPicksArea.0