Microsoft is about to release Project Gemini free of charge as an add-in on top of Microsoft Excel , Gemini is a tool that can connect on most of the data sources + reports and data feeds to allow you to analyze numerous amount of data in a matter of seconds, check more about this nice product on the Gemini Team's Official Blog here. There is another extension to Project Gemini which is the Self Data Management which is installed on top Share point 2010 as another add-in free of charge too, check more about it here Donald Farmer's Collection of Links regarding Project Gemini here

• Table Analysis Tools for Excel: This add-in provides easy-to-use tasks that leverage SQL Server 2005 Data Mining to perform powerful analytics on your spreadsheet data.
• Data Mining Client for Excel: This add-in allows you to go through the full data mining model development lifecycle within Excel 2007 using either your spreadsheet data or external data accessible through your SQL Server 2005 Analysis Services instance.
• Data Mining Templates for Visio: This add-in allows you to render and share your mining models as annotatable Visio 2007 drawings.

Download from here

SQL Server 2008, the latest release of Microsoft SQL Server, provides a comprehensive data platform. Books Online is the primary documentation for SQL Server 2008. 

Books Online includes the following types of information: 

• Setup and upgrade instructions.
  
• Information about new features and backward compatibility.
  
• Conceptual descriptions of the technologies and features in SQL Server 2008.
  
• Procedural topics describing how to use the various features in SQL Server 2008.
  
• Tutorials that guide you through common tasks.
  
• Reference documentation for the graphical tools, command prompt utilities, programming languages, and application programming interfaces (APIs) that are supported by SQL Server 2008.
  
• Descriptions of the sample databases and applications that are available with SQL Server 2008. You can download the sample databases from the SQL Server Community Projects and Samples page on CodePlex.

• Easily manage and administer SQL Server 2008 Express with a easy-to-use graphical management tool - SQL Server 2008 Management Studio Basic.
  
  
• Issue full-text queries against plain character-based data in SQL Server tables. Full-text queries can include words and phrases, or multiple forms of a word or phrase.
  
  
• Run SQL Server Reporting Services reports on local relational data.

• A Report Viewer Web Part that provides report viewing capability, export to other rendering formats, page navigation, search, print, and zoom.
• Web application pages so that you can create subscriptions and schedules, and manage reports, models, and data sources.
• Support for using standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types.

• Synchronization from the SharePoint content database to the report server database.
• A custom security extension that uses SharePoint permissions to control access to report server operations.
• A delivery extension that you can use in subscriptions to deliver reports to SharePoint libraries.
• A revised Reporting Services Configuration tool that you can use to configure a report server for SharePoint integrated operations.
• A SOAP endpoint for managing report server content in SharePoint integrated mode.
• Support for new data-driven subscriptions and URL parameters.
• In-place upgrade of the Reporting Services Add-in.

Download SQL Server 2008 Feature Pack which contains:

• Microsoft SQL Server 2008 Native Client
• Microsoft SQL Server 2005 Backward Compatibility Components
• Microsoft SQL Server 2008 Reporting Services Add-in for Microsoft SharePoint Technologies
• Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007

Download it from here

today I'll demonstrate a very nice feature that has been added recently in Microsoft SQL Server 2005 which is SQL CLR Integration.

This feature It’s the possibility to deploy C# or VB.NET code that is used within the SQL Server process. this means that if you need complex procedural code, you can write it as managed code and of course it has plenty of benefits :

¨T-SQL is interpreted and hence slower.

¨Error handling in T-SQL was frankly abysmal (although it has improved in SQL Server 2005)

¨String handling in T-SQL is un-optimized and slow

¨SQL-CLR is compiled so faster

¨SQL-CLR lets you take advantage of the huge base class library

and now I'll show you a small demonstration on how you create a SQL CLR project:

first of all you should have Microsoft SQL Server 2005 Installed and Microsoft Visual Studio 2005.

you should enable the SQL CLR feature by clicking Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Surface Area Configuration --> click on Surface Area Configuration for features -->you will find in the left pane MSSQLSERVER underneath it Database Engine underneath it you will find CLR Integration --> select it and Check it from the right pane.

my Example is so simple imagine that we have a table that we need to capture what is inserted in it on insertion and pass it to a web service to through it into another database, I chose web service particularly because it will give the opportunity to do anything you with the captured data so let's open the Visual Studio 2005 from Start --> All Programs --> Visual Studio 2005 --> click on File--> New Project

Choose SQL Server project from the Database Template under the Visual C# template and write in the Name "CLRDemo" , save it where ever you like and click OK.

The Add database Reference window will open to create the database reference you want to connect to ...

Click on Add New Reference to add Reference to "Northwind" Database ...

A new Database Reference Window will open type the SQL Server name in the Server Name textbox and Select "Northwind" database from the Select or enter a database Name drop down , click on Test Connection button to make sure that the connection is successful , click Ok, Click OK.

Right click on the Solution Icon "CLR Demo" on the right pane  and click Add Trigger , Add New Item Window will appear with the trigger class selected , type in the Name "CustomersTrigger.cs"

and click Add  the Trigger class will be added to the project as shown in the figure below.

 

let's create our web service that we will pass to it the captured inserted values to insert them into another database.

click on File --> Add --> New Web Site  --> choose ASP.NET Web Service Template make sure that the selected Language is C# and change the name of the Project to "PassValues" and Click OK

The Web Service Project will be added to our solution as intended now , let's add our connection string value in the "Web.config" file in the Web service Project so right click on the Web Service Project and click on add New Item and choose Web Configuration File from the Add New Item Window and Click Add

double click on the "Web.Config" file and replace the following code

<connectionStrings/> Tag

    <connectionStrings>
        <add name="SQLcon" connectionString ="uid=your_username;pwd=database Password;data source=.;Initial Catalog=database_name"/>
    </connectionStrings>

 

this connection string that I've just wrote down is the one that the web service will connect on to throw the captured inserted data by our trigger.

to make the web Service work successfully you will have to run the following script in your SQL Server

USE [Northwind]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 09/06/2007 12:43:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
    [UserName] [varchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

USE [Northwind]
GO
/****** Object:  Table [dbo].[test1]    Script Date: 09/09/2007 12:08:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
    [CustomerName] [nchar](100) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]

Press F5 to execute the SQL Code

to create a Table named "Users" and another one named "Test" in the "Northwind" database.

 

let's write some code in our Web Service , double click on the "service.cs" file , copy and paste the following code in it :

[WebMethod]
 public void InsertIntoTemp(string CustomerName)
 {
     SqlConnection con = new SqlConnection();
     con.ConnectionString = ConfigurationManager.ConnectionStrings["sqlcon"].ToString();
     con.Open();
     SqlCommand com = new SqlCommand();
     com.Connection = con;
     com.CommandText = "Insert users values('" + CustomerName + "')";
     com.ExecuteNonQuery();
 }

Compile the code to make sure that it has no errors...

now you've created a Web service that has one web Method that takes one Parameter which is "CustomerName" and inserts it in the "users" Table in the "Northwind" database.

to attach our web service to the CLR project you will have to right click on the "CLRDemo" Project Icon and click on add Web Reference button.

click on Web services in this solution the following window will appear to you

click on the Service Link the following window will appear to you...

Type in the Web reference Name "PassValues" and click Add Reference button

 

let's get back to the our "CustomerTrigger.cs" class , double click on the "CustomerTrigger.cs" class and copy and paste the following code :

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer;

using CLRDemo.PassValues;
public partial class Triggers
{
    // Enter existing table or view for the target and uncomment the attribute line 

//Target Means = the name of the Table we need to monitor which is in our case Table "test" , Event Means = Monitors which Action

    [Microsoft.SqlServer.Server.SqlTrigger(Name = "CustomerTrigger", Target = "test", Event = "FOR INSERT")]
    public static void CustomerTrigger()
    {
        CLRDemo.PassValues.Service service = new CLRDemo.PassValues.Service();
        // Replace with your own code
        SqlTriggerContext triggerContext = SqlContext.TriggerContext;
        SqlPipe sqlPipe = SqlContext.Pipe;
        if (triggerContext.TriggerAction ==
            TriggerAction.Insert)
        {
            SqlDataReader sdr;

       // here I'll set the context of the sqlconnection I use as the same context of my Trigger
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {

      //open SQL Connection
                conn.Open();

      // Setting my SQL command
                SqlCommand cmd = new SqlCommand("SELECT * FROM INSERTED", conn);

      //Executing my command in the Data reader               

                sdr = cmd.ExecuteReader();

       //Checking if the data reader has Rows or not        

                  if (sdr.HasRows)
                {

         // read the data reader
                    while (sdr.Read())
                    {
         // passing the captured data to the created web Service

                        service.InsertIntoTemp(sdr[0].ToString().Trim());
                    }
                }

            }
        }

        SqlContext.Pipe.Send("Trigger FIRED");
    }
}

you will find a folder named Test Scripts Folder under the root of the"CLRDemo" project and under it "Test.sql" file; this file is used for testing , you can write in it the SQL test script you want to test your CLR code so just add in it the following code:

insert test values ('Karim')

where test is our table name in the "Northwind" database

and comment anything else...by putting "//" at the beginning of the line.

try to compile the project right now, the following error will appear in the output window

That's because SQL Server need another assembly to access the web services, a "Serialization Assembly". How can I do that? Simple, go to the project's properties, and set the feature "generates serialization assembly" to "on".

then go to the Database tab in the project properties to set the permission level to External because you are calling web service so you have to set the database permission level to External.

After you try to compile the code after making the Generate Serialization assembly to ON the Code will generate the following error ...

Calling web service methods requires serializing all the types being used to XML. Outside SQL Server, this serialization code is generated at runtime, compiled and loaded in the application. However, for security and reliability reasons, SQL Server does not allow you to dynamically load assemblies. Hence, to make this scenario work, you need to pre-generate the serialization assembly and register it in the database. Visual Studio 2005 beta2 allows you to generate the serialization assembly from your project. You need to turn the Generate serialization assembly option on in project properties a Build. You can also generate the serialization assembly using a tool called ‘sgen’ that is shipped with the .NET Framework SDK as follows:

>sgen.exe myAsm.dll

Where myAsm.dll is the assembly that you want to use inside SQL Server and contains code that is calling webservices. If you have installed Visual Studio 2005, you would usually find sgen at C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin. When you run sgen, it would generate an assembly with the name myAsm.XmlSerializers.dll.

Once you have these two assemblies available. You need to register them in SQL Server as follows:

CREATE ASSEMBLY myAsm from ‘<path>\myAsm.dll’

with permission_set = EXTERNAL ACCESS

CREATE ASSEMBLY myAsmXml from ‘<path>\myAsm.XmlSerializers.dll’

with permission_set = SAFE

Visual Studio 2005 allows you to register the first assembly in SQL Server using the deploy feature in SQL Server projects. However, it does not support deploying the serialization assembly and you would need to manually register it. If you are using Visual Studio on the same machine as your SQL Server, you can use the pre / post deploy script feature in Visual Studio to automate this in your project. This can be done as follows:

1. Add the following to a file called predeployscript.sql:

IF EXISTS (SELECT [name] FROM sys.assemblies WHERE [name] = N'myAsmXML')

DROP ASSEMBLY myAsmXML with NO DEPENDENTS;

2. Add the following to a file called postdeployscript.sql:

CREATE ASSEMBLY myAsmXML from '<path>\myAsm.XmlSerializers.dll'

3. Add these two files to your project by right clicking your project in Solution Explorer and Add an Existing Item. 

Make sure you add this in the root of the project and not in the Test Scripts. When you click deploy, Visual Studio will automatically run these scripts before and after the deployment of your project. However since the scripts are run on the SQL Server, the path must be relative to the machine on which you have SQL Server. If SQL Server and VS are on separate machines then you must make sure the path is a network path and accessible from SQL Server machine.

after this the try to compile the code and it will work just fine.

References:

http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

 

Consider the following support issues when you run the SQL Server "Katmai" June CTP:

• This release is not supported in production environments.
• This release is available in English only.
• This release is available in Developer Edition only.
• Installation must be run from the Servers folder. Tools-only installation is not supported.
• This release is available by download only.
• Upgrade to the SQL Server "Katmai" June CTP from earlier versions of SQL Server is not supported.
• This release has a 180-day trial period. Installations will not function after 180 days.
• Hotfix support is not available for this release.
• Technical support is not available for this release.
• Failover cluster instances are not supported for this release.
• There is no native IA64 support in this release. WOW64 support is for the x64 platform only.

2.3.1 Supportability Known Issues

• Side-by-side tools are not supported. The presence of SQL Server 2005 Management Tools or BI Development Studio blocks installation of SQL Server "Katmai" Management Tools and BI Development Studio. To install the June CTP of SQL Server "Katmai" on a computer where SQL Server 2005 Management Tools or SQL Server 2005 Business Intelligence Development Studio is already installed, use the following syntax at a command prompt:

  Start /wait <CD or DVD Drive>\servers\setup.exe /qb 
  INSTANCENAME=<InstanceName> 
  ADDLOCAL=All REMOVE=Client_Components,SQL_Documentation 
  SAPWD=<StrongPassword> SQLACCOUNT=<domainName\userName> 
  SQLPASSWORD=<domainUserPassword> 
  AGTACCOUNT=<domainName\userName> 
  AGTPASSWORD=<domainUserPassword> 
  ASACCOUNT=<domainName\userName> 
  ASPASSWORD=<domainUserPassword> 
  RSACCOUNT=<domainName\userName> 
  RSPASSWORD=<domainUserPassword> 
  SQLBROWSERACCOUNT=<domainName\userName> 
  SQLBROWSERPASSWORD=<domainUserPassword>

• Side-by-side installation with SQL Server 2000 is not supported. An existing instance of SQL Server 2000, including MSDE, blocks installation of this release of SQL Server "Katmai".
• Side-by-side installation with a default instance of SQL Server 2005 Analysis Services (SSAS) or SQL Server 2005 Reporting Services (SSRS) is not supported. If you install a named instance of the June CTP SSAS or SSRS, installation of a default instance of SQL Server 2005 Analysis Services (SSAS) or SQL Server 2005 Reporting Services (SSRS) fails.
• Side-by-side installation with the March CTP of SQL Server "Katmai" is not supported. An existing instance of the March CTP blocks installation of this release of SQL Server "Katmai".
• Multiple instances of this release of SQL Server "Katmai" are not supported on a single computer.
• The Database Engine Tuning Advisor might not function properly in side-by-side configurations.
  In a side-by-side configuration with a default instance of this release of SQL Server "Katmai" and a named instance of SQL Server 2005, DTAEngine.exe does not correctly load the InstAPI.dll. InstAPI.dll is loaded from the Program Files\Microsoft SQL Server\90\ folder instead of from the Program Files\Microsoft SQL Server\100\ folder.
• By default, Error and Feature Usage Reporting is enabled.
  To disable Error and Feature Usage Reporting after Setup is finished, use the SQL Server Error and Usage Reporting tool on the Configuration Tools menu. To run the reporting tool, click Start, point to All Programs, point to SQL Server 200x, point to Configuration Tools, and then click SQL Server Error and Usage Reporting.
• In SQL Server 2005, SQL Server Browser was an integrated installation together with the Database Engine or SSAS. In SQL Server "Katmai", SQL Server Browser is a separate installation, performed automatically with the Database Engine or SSAS. If you have multiple instances of SQL Server "Katmai", SQL Server Browser will uninstall automatically when the last instance of SQL Server "Katmai" is uninstalled.
  However, if SQL Server 2005 exists on the system with one or more instances of SQL Server "Katmai", SQL Server Browser will not be removed automatically when the last instance of SQL Server "Katmai" is uninstalled. The SQL Server Browser installed with SQL Server "Katmai" will remain on the system to improve connections to the instance of SQL Server 2005. You can leave SQL Server "Katmai" Browser installed with SQL Server 2005 and it will continue to function correctly.
  To uninstall all components of SQL Server "Katmai", you must uninstall SQL Server Browser manually using Add or Remove Programs in Control Panel. If you remove SQL Server Browser when a named instance of SQL Server 2005 is present, connectivity to SQL Server 2005 might become disrupted. If this occurs, you can re-install SQL Server Browser in one of the following ways:
  • Repair the instance of SQL Server 2005 through Add or Remove Programs.
  • Install an instance of the SQL Server 2005 Database Engine or SSAS.
• SQL Server "Katmai" Books Online does not automatically install if SQL Server 2005 Books Online is already installed.
  To install SQL Server "Katmai" Books Online, run SqlServerKatmai_BOL.msi after SQL Server "Katmai" Setup is finished. In the June CTP, this file is located in the \Tools\Setup folder. You can also download this file from the Microsoft Connect Web site June CTP Books Online download page.
• By default, sample code and sample databases are not installed in this release.
  Do not use AdventureWorksDWSamples, SQL_AdventureWorksASSamples, or SQL_AdventureWorksSamples unattended parameters in this release. You can download samples from the Microsoft SQL Server Samples and Community Projects Web site. For more information about samples, see the documentation at Start/All Programs/Microsoft SQL Server "Katmai"/Documentation and Tutorials/Microsoft SQL Server Samples Overview.
• SQL Server Browser and SQLWriter files install to the SQL Server 2005 default directory: <drive>:\Program Files\Microsoft SQL Server\90\Shared\.
• SQL Server 2005 Configuration Manager uses the SQL Server Native Client 9 configuration. SQL Server "Katmai" Configuration Manager uses SQL Server Native Client 10. Both versions can function in a side-by-side configuration.

Updating the Location for the .NET Framework CLR Samples

Before you compile .NET Framework common language runtime (CLR) samples for SQL Server, verify that the path of the version of the .NET Framework compiler is the first Framework directory in the PATH environment variable. The location of the compiler that is shipped with this release of SQL Server is C:\<Windows root directory>\Microsoft.NET\Framework\v2.0.x. Drive C is the installation drive, <Windows root directory> is either Windows or WINNT, and x is five digits.

3.4 Planned Removal of SQLXML

SQLXML is included in this CTP release of SQL Server "Katmai". SQLXML will be removed from the "Katmai" installer in a future CTP release and offered as a separate component instead.

Database Engine

The following issues apply to Database Engine when SQL Server "Katmai" is installed.

4.1.1 Stored Procedure sp_helpuser Fails When Passing Role Name

The following message appears when you supply the role name as a parameter for sp_helpuser:

Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 166

Do not supply a role name as a parameter for sp_helpuser in the "Katmai" June CTP. This stored procedure is changing and will support passing a role name in a subsequent CTP release.

4.1.2 Language Reference

• MERGE is a fully reserved keyword when the database compatibility level is set to 100. The MERGE statement is also available under both 90 and 100 database compatibility levels, but the keyword is not fully reserved when the database compatibility level is set to 90.
• Full-text predicates are no longer allowed in the OUTPUT clause when the database compatibility level is set to 100.
• The target table of an OUTPUT INTO clause cannot have any enabled triggers defined on it.
• A user-defined function cannot be created if it contains an OUTPUT INTO clause whose target is a table.

4.1.3 Cleanup Tasks Cannot Be Added by Using Maintenance Plan Wizard

In this version of SQL Server "Katmai", the Maintenance Plan Wizard fails if you try to add a cleanup task. To add a cleanup task to a maintenance plan, use the maintenance plan designer instead of the Maintenance Plan Wizard. For example, you can create the basic maintenance plan by using the wizard, and then add a cleanup task by modifying the plan in the designer. To modify the plan using the designer, right-click the plan and then select Modify.

4.1.4 Error When Trying to Connect to a SQL Server Compact Edition version 3.5 Database

In SQL Server Management Studio, you will get an error message when you try to connect to a SQL Server Compact 3.5 version 3.5 database. For example, the message appears when you use the Connect to Server dialog box and select either New database or Browse for More in the Database File drop-down menu.

This error occurs because SQL Server Compact 3.5 3.5 is not yet integrated into the June CTP. Therefore, you cannot connect to SQL Server Compact 3.5 3.5 database. Support for SQL Server Compact 3.5 3.5 will be added in a later CTP release.

4.2 Analysis Services

The following issues apply to Analysis Services when SQL Server "Katmai" is installed.

4.2.1 Clicking the Calculations Tab in Business Intelligence Development Studio Might Generate an Error Message

Double-clicking an Analysis Services project file to open both Business Intelligence Development Studio and the project might generate an error message when you click the Calculations tab. You can receive this error message if the project file is in a different directory from the default directory that Business Intelligence Development Studio uses.

To prevent this error, open Business Intelligence Development Studio, and then open the Analysis Services project from within the studio environment.

4.3 Integration Services

The following issues apply to Integration Services when SQL Server "Katmai" is installed.

4.3.1 Version Change for SQL Server Native Client

SQL Server "Katmai" uses a different version of the SQL Server Native Client from the one included in SQL Server 2005. (SQL Server Native Client was previously known as SQL Native Client.) Therefore, Integration Services packages that use SQL Server Native Client and were created in SQL Server 2005 cannot run automatically in SQL Server "Katmai". To run these packages, follow one of these steps:

• On the computer that is running SQL Server "Katmai", install the version of SQL Server Native Client (sqlncli.dll) that SQL Server 2005 uses.

  Note:

  As of the March CTP of SQL Server "Katmai", you can install the version of SQL Server Native that SQL Server 2005 uses side-by-side with the version that SQL Server "Katmai" uses. However, you cannot install SQL Server 2005 and SQL Server "Katmai" side-by-side.

• Revise the connection strings in the package to specify the version of SQL Server Native Client that SQL Server "Katmai" uses. To revise the connection strings, replace SQLNCLI.1 with SQLNCLI10 or SQLNCLI10.1.

4.3.2 Version Change for Analysis Services OLE DB Provider

SQL Server "Katmai" includes a different version of the Analysis Services OLE DB provider from the one included in SQL Server 2005. Therefore, Integration Services packages that use the Analysis Services OLE DB provider and were created in SQL Server 2005 cannot run automatically in SQL Server "Katmai". To run these packages, follow one of these steps:

• On the computer that is running SQL Server "Katmai", install the Analysis Services 9.0 OLE DB provider. The Analysis Services 9.0 OLE DB provider is the version of the provider that SQL Server 2005 includes.

  Note:

  As of the March CTP of SQL Server "Katmai", you can install the Analysis Services 9.0 OLE DB provider side-by-side with the Analysis Services 10.0 OLE DB provider. (The Analysis Services 10.0 OLE DB provider is the version that SQL Server "Katmai" uses.) However, you cannot install SQL Server 2005 and SQL Server "Katmai" side-by-side.

• Revise the connection strings in the package to specify the Analysis Services 10.0 OLE DB provider. To revise the connection strings, replace MSOLAP.3 with MSOLAP or MSOLAP.4. If you specify MSOLAP, your packages will use the latest installed version of the provider.

4.3.3 Installing SQL Server "Katmai" Removes Support for DTS in SQL Server 2005 Integration Services Packages

For this CTP release, you must uninstall SQL Server 2005 Workstation Components before installing the SQL Server "Katmai" Workstation Components. Uninstalling the SQL Server 2005 Workstation Components removes three Integration Services components: the SQL Server 2005 version of the Execute DTS 2000 Package task and two supporting assemblies. These three Integration Services components support backward compatibility between SQL Server 2005 and SQL Server 2000 Data Transformation Services (DTS). Without these three components, SQL Server 2005 packages and tools that require DTS support will not run.

However, SQL Server "Katmai" Integration Services includes a version of the Execute DTS 2000 task and the two supporting assemblies. Therefore, you can upgrade SQL Server 2005 packages that require DTS support to the SQL Server "Katmai" format by opening them in the SQL Server "Katmai" version of Business Intelligence Development Studio, and then running those packages as SQL Server "Katmai" packages.

4.3.4 Custom Applications That Use Both Integration Services and System.Data.SqlClient APIs Might Fail

A custom application that uses both Integration Services and .NET Framework Data Provider for SQL Server (System.Data.SqlClient) APIs will fail if the following conditions are true:

• The custom application uses the .NET Framework Data Provider for SQL Server APIs before the application uses the Integration Services APIs.
• The custom application runs on a computer that has both SQL Server "Katmai" and an edition of SQL Server 2005 that does not support some or all of the features of Integration Services.

Under these conditions, the custom application incorrectly detects the version of SQL Server as the edition of SQL Server 2005 that does not support Integration Services. This erroneous detection causes the custom application to fail when it calls the Integration Services APIs and to generate a DTS_E_PRODUCTLEVELTOLOW error. This error indicates that Integration Services is not supported on the detected version of SQL Server.

This failure does not occur in the following situations:

• When running packages in the design environment.
• When using dtexec.exe to run packages outside the design environment.
• When the custom application uses Integration Services APIs, but does not use the .NET Framework Data Provider for SQL Server APIs.

4.3.5 Custom Components Developed in SQL Server 2005 Integration Services Must Be Edited and Recompiled for SQL Server "Katmai"

If you developed a SQL Server 2005 Integration Services (SSIS) custom component, that custom component must be modified before it can be used in SQL Server "Katmai" Integration Services packages. You can either modify the component to run only in SQL Server "Katmai" packages or to run in both SQL Server 2005 and SQL Server "Katmai" packages.

Use the Custom Component Only in SQL Server "Katmai" Packages

After completing the following procedure, you can use the component in SQL Server "Katmai" packages, but can no longer use the component in SQL Server 2005 Integration Services (SSIS) packages.

To use a SQL Server 2005 Integration Services custom component only in SQL Server "Katmai" packages

1. In the component, change the references that point to the SQL Server 2005 versions of the Integration Services assemblies to point to the SQL Server "Katmai" versions (version 10.0) instead.
2. Change any code that uses the IDTSxxx90 interfaces to use the corresponding IDTSxxx100 interfaces.
3. Rebuild the component.
4. Reinstall the component in the global assembly cache (GAC).
5. For use in the Toolbox, redeploy the component to the appropriate subfolder under C:\Program Files\Microsoft SQL Server\100\DTS.
6. If you have changed the assembly version of the component, you must also manually edit packages to use the new version.

Use the Custom Component Only in Both SQL Server 2005 and SQL Server "Katmai" Packages

After completing the following procedure, you will have two versions of the custom component: the original version and a SQL Server "Katmai" version. You can still use the original version in SQL Server 2005 packages, but you will have to use the SQL Server "Katmai" version in SQL Server "Katmai" packages.

To create a separate SQL Server "Katmai" version of the custom component

1. Change the file name (assembly name) of the component.
2. In the component, change the references that point to the SQL Server 2005 versions of the Integration Services assemblies to point to the SQL Server "Katmai" versions (version 10.0) instead.
3. Change any code that uses the IDTSxxx90 interfaces to use the corresponding IDTSxxx100 interfaces.
4. Build the component with a new ProgID and CLSID.
5. Install the component in the global assembly cache (GAC).
6. For use in the Toolbox, deploy the component to the appropriate subfolder under C:\Program Files\Microsoft SQL Server\100\DTS.
7. Manually edit packages to use the new version.

4.3.6 Limited Namespaces Included in Books Online

For this CTP release, Books Online includes the documentation for only the following Integration Services namespaces:

• Microsoft.SqlServer.Dts.ManagedMsg
• Microsoft.SqlServer.Dts.Pipeline.Wrapper
• Microsoft.SqlServer.Dts.Runtime.Wrapper
• Microsoft.SqlServer.Dts.Tasks.ExecutePackageTask

For information about additional namespaces, see the SQL Server 2005 Integration Services Class Library on MSDN.

4.4 Reporting Services

The following issues apply to Reporting Services when SQL Server "Katmai" is installed.

4.4.1 Reporting Services in SharePoint Mode

Reporting Services in SharePoint integrated mode is not supported in this CTP release of "Katmai".

4.4.2 Reporting Services New Functionality

This version of SQL Server "Katmai" includes support for features that were released with SQL Server 2005 Service Pack 2: Hyperion Essbase data source support, report model generation from Oracle 9.2.0.3 or later data sources, and the Select All check box for multivalued parameters. These features are fully documented in Books Online.

4.5 All Business Intelligence Technologies

The following issues apply to all of the business intelligence technologies—Analysis Services, Integration Services, and Reporting Services—when SQL Server "Katmai" is installed.

4.5.1 Remove SQL Server 2005 Workstation Components before Installing SQL Server "Katmai"

In the SQL Server "Katmai" version of Business Intelligence Development Studio, the designers that you use to create projects for Integration Services, Analysis Services, and Reporting Services cannot be installed side-by-side with the SQL Server 2005 designers. For both SQL Server "Katmai" and SQL Server 2005, these designers are part of the Workstation Components that SQL Server installs. However, for this CTP release, you cannot install the SQL Server "Katmai" designers if the SQL Server 2005 Workstation Components are already installed on the computer. You must uninstall the SQL Server 2005 Workstation Components before installing SQL Server "Katmai". If you try to install SQL Server "Katmai" without first uninstalling the SQL Server 2005 Workstation Components, you will be notified that the existing components on the computer are blocking the installation. To uninstall the existing Workstation Components, in Control Panel, open Add or Remove Programs, select SQL Server 2005, click Remove, and on the Component Selection page of the wizard, select Workstation Components. Once you have uninstalled the SQL Server 2005 Workstation Components, you can install the SQL Server "Katmai" designers by installing SQL Server "Katmai".

4.5.2 Cannot Edit SQL Server 2005 Business Intelligence Projects in SQL Server "Katmai"

To create projects for Integration Services, Analysis Services, or Reporting Services, you use their respective designers in Business Intelligence Development Studio. However, the designers in SQL Server "Katmai" cannot be installed side-by-side with those from SQL Server 2005. Furthermore, installing SQL Server "Katmai" replaces designers that were installed by SQL Server 2005 with the SQL Server "Katmai" versions.

You can use the SQL Server "Katmai" designers to open and modify projects that were created by using the SQL Server 2005 designers. However, if you use the SQL Server "Katmai" designers to modify SQL Server 2005 Business Intelligence projects, you can no longer save those projects in the SQL Server 2005 format. Upon opening a SQL Server 2005 project, the designer upgrades the project in memory to the SQL Server "Katmai" format. To permanently replace the SQL Server 2005 version of the project with the upgraded SQL Server "Katmai" version, you must either save the upgraded version of the project or perform an operation, such as running an Integration Services package, that will cause the upgraded version of the project to be saved. Otherwise, the upgraded version will not be saved.

• scalability and performance
•  a server independent of IIS
•  support for new report structures (some significant enhancements/changes to RDL)
•  integrating Dundas and SoftArtisans technology.
• Reporting Services SharePoint integration to store, secure, access, and manage reports, data sources, and resources from a SharePoint site.
• Microsoft .NET Framework Data Provider for Hyperion Essbase to access Hyperion System 9.3 BI+ Enterprise Analytics data sources.
• Report model generation from Oracle data sources that run on version 9.2.0.3 or later is supported.
• Samples are now available online and are not installed via Setup.
• Side-by-Side Installation of SQL Server "Katmai" Reporting Services and SQL Server 2005 Reporting Services (SSRS).

and they are also reviewing the support of some of Dundas controls.

Also there is major enhancements in the Analaysis services Data Mining and Multidimensional data.

one of the cool features really in Katmai , is the location datatype and intelligent and spatial data type and I'll take about those two later and I'll accompany the blog with some code.

Another nice feature also is the duplicate detection which detect the duplication in data when you import the data from a place to another.

Of course there is major enhancements in the performance of the Data warehousing and the data auditing feature and a lot of other features that I'll explain in later posts.

If there is anyone intrested in downloading the sql server he can access connect.microsoft.com website for participating in the testing program.

Enjoy!!

• High Performance Data Integration with SQL Server Integration Services
• Using Analysis Services and Data Mining to understand your customers
• Reporting Services, Report Builder and Sharepoint

I'll talk in here about the first session which was from my point of view the most intresting, First he described the difference between the DTS and the SQL Server Integration Services and it's Huge from my experience in this field and then He described the OVAL Concept which He meant with it the following:

O --> Operations

V --> Volume

A --> Application

L --> Location

Let's discuss each and everyone of these factors:

Operations: All the operations that happens during the data transfer whether it was defined or hidden done by the system.

Volume: The Volume of the data that will be transfered, it differs whether i'm movind a large amount of data ( Millions of rows ) or small amount (hundreds) in the time it takes to be moved.

Application: We have to take care which application we will use to move the data , if we are moving a bulk amount of data without doing any operations , we can use BCP instead of SQL Server Integration Services , becasue it will saves us time.

Location:  We have to the Source and Destination , the Type of network between them and the type of the Harddisk we are using in both places , cause may be when i change the hardware we save time and this for many company will be very benificial.

so the conclusion is that we have to take care of these four factors when we design a SSIS package.

The second thing that i want to talk about is How you detect the time leakage in your Package and How can you optimize it?

Donald Farmer has described this nice method of calculating the time of execution in the package and how can we detect the leakage;

first we run the package as it is and we see the execution time it takes let assume that it took 29 sec

then the STD execution time was 29 sec

Secondly we replace the destination with the Row Count component which is very useful component in here; it just count rows and we see the execution time of the package without the destination let's assume its gonna be 20 sec then STC took 20 sec to caluculate the time the data took to be transfered to the destination D = STD - STC= 29-20 =9 sec
To calculate the source time, you will Replace the destinaiton and the operations with the Row Count Component and execute the package and let's caluclate the execution time, let's assume it will be 15 sec; so we know now that the source took about 15 sec then by small mathematical operation we will know that the Operations took T = STC - SC= 20-15 = 5 sec

To Optimize this Integaration Package let's analyze the situation , I guess that the operations doesn't take much time and the destination too, may be If we investigated more we will find that the problem issued from the Old Hard disk we use in the source which tooks us too much time to read the data , so it won't matter how much time and money we invest to develop a new package the problem will still in the old harddisks so if we replaced them with a new one the performance will be enhanced significantly.

This was the end of the session I hope you like it as I did.

Thank you Donald.