Microsoft has just officially released the highly anticipated Windows Server 2012. The newly released server operating system offers a number of features that can be utilized to benefit developers, resellers and businesses. As a premier European Windows and ASP.NET hosting provider that follow the developments of Microsoft products, HostForLIFE.eu proudly announces the support of Windows Server 2012 Hosting Platform in the world-class Amsterdam (The Netherlands) data center.

“We know that our customers are always looking for new technologies and the latest Microsoft product. With the launch of Windows Server 2012, we believe that anyone can take advantage of all the improvements available in this platform”, said Manager of HostForLIFE.eu, Kevin Joseph. “The focus on high availability, scalability, and virtualization has made this one of the most important releases of Windows Server to date. We have been working closely with Microsoft throughout the pre-release development cycle of the platform to both drive the direction of the product and ensure our team is ready to support Server 2012 solutions. We couldn’t be more excited and confident in the solutions now available to our clients with Windows Server 2012.”

With our Windows Server 2012 Hosting Platform, customers have an access directly to all the newest technologies and frameworks, such as ASP.NET 4.5 Hosting, ASP.NET MVC 4 Hosting, Silverlight 5 Hosting, WebMatrix Hosting, Visual Studio Lightswitch Hosting and SQL 2012 Hosting. All these technologies/frameworks are integrated properly on our world-class Control Panel. The package is offered from just €2.45/month and we believe that this is the most affordable, features-rich Windows and ASP.NET Hosting package in European market.

HostForLIFE.eu is awarded Top No#1 SPOTLIGHT Recommended Hosting Partner by Microsoft (see http://www.microsoft.com/web/hosting/HostingProvider/Details/953). Our service is ranked the highest top #1 spot in several European countries, such as: Germany, Italy, Netherlands, France, Belgium, United Kingdom, Sweden, Finland, Switzerland and other European countries. Besides this award, we have also won several awards from reputable organizations in the hosting industry and the detail can be found on our official website.

For more information about our service, please visit http://www.hostforlife.eu.

About HostForLIFE.eu:

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Our number one goal is constant uptime. Our data center uses cutting edge technology, processes, and equipment. We have one of the best up time reputations in the industry.

Our second goal is providing excellent customer service. Our technical management structure is headed by professionals who have been in the industry since its inception. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.

Visual Studio over the years has provided very good support for the core application development languages like C#, VB, etc. But when it comes to web development languages like javascript and CSS, Visual Studio lacked some main support such as intellisense, debugging, etc. This article discusses some key features introduced in Visual Studio 11 with respect to Javascript and CSS.

Javascript

In this section we will look at some important and useful features that the Visual Studio 11 JavaScript editor provides.

Intellisense, Type Inference & Documentation

Prior versions of Visual Studio were a pain for Javascript developers due to the lack of intellisense for Javascript. The intellisense provided was very basic; developers had to be very sure of the method and class names, including the casing. Now Visual Studio 11 provides complete intellisense support for all the javascript objects and its methods. Fig below is a sample intellisense menu and you can see the number of options.



Visual Studio 11 also provides rich intellisense support for popular third party Javascript libraries like JQuery. Since Jquery is used by almost all web developers this will be of tremendous help to them. Fig below shows a sample intellisense available for JQuery in the Visual Studio 11 IDE.



In the above screenshot you would have noticed documentation for the method in the intellisense window like C# code. You could provide the documentation for your Javascript method as shown in the below example.

< script type="text/javascript">
        function DoSummation(a, b) {
            ///      <summary>
            ///            Returns the sum of the variables a and b
            ///      </summary>
            ///      <param name="a" type="int">Integer variable 1</param>
            /// <param name="b" type="int">Integer variable 2</param>
            return a + b;
        }
</script>

The Javascript editor also performs type inference, for example if a Javascript variable is assigned with a string value and later when the variable is used then the intellisense displays only the string related methods.

Curly Brace and Bracket Matching

When I use to write a large amount of Javascript code, especially with object oriented Javascript or jQuery, I used to get confused with the brackets and curly braces on their scopes. It may sometimes be a nightmare for developers to fix a brace mismatch on a huge Javascript code file. Fig below shows how the bracket scopes are highlighted by Visual Studio 11.



Go to Definition Feature

Like the C# Visual Studio editor of Visual Studio, now the Javascript editor also provides the Go to definition feature, which will be very useful in going through the code flow or to debug a Javascript issue especially when there are a lot of files involved and the method calls are scattered. In order to make this feature work you need to add the reference path of the .js file containing the method implementation onto the caller .js file. Below is a sample.

/// <reference path="File1HavingImplementation.js" />

A generic approach to provide the reference path is to add them to the _references.js file.

Debugging

There is a new window added to Visual Studio 11 called the Javascript Console, which provides various features in terms of debuggingJjavascript code. I will cover this topic in a separate article.

CSS

In this article we will take a look at two important features on the CSS front.

Code Snippets and Writing Vendor Specific CSS

One of the most repetitive and boring tasks for the UI developer is to write the vendor specific styles in a CSS file. Vendor specific CSS is nothing but writing the same style with different names as supported by different browsers in order to get the style working on multiple browser combinations. Now Visual Studio 11 provides CSS code snippets, for example type transform and hitting tab would create the different vendor specific properties for transform in a CSS class. Below is the generated CSS class.

.MyClass
{
       -ms-transform: rotate(-90deg);
       -moz-transform: rotate(-90deg);
       -o-transform: rotate(-90deg);
       -webkit-transform: rotate(-90deg);
       transform: rotate(-90deg);
}

Color Picker

One thing that bothered me while writing CSS classes is providing the color code. I had to use some external utility to figure out the color code of the color being used, which I was trying to create a style for. Now with Visual Studio 11 the UI designer doesn’t have to move away from the CSS editor to pick the color, the editor itself has the color picker integrated. Fig below shows the screenshot of the color picker on a CSS file.



I hope these features have simplified and provided solutions for some long lasting issues faced by web developers.

Happy reading!

You can create a data alert to email notification in SQL Server 2012 reporting services. This data alert sends e-mail notification when only when specific conditions in the data are true at a schedule time. This Date Alerting feature is available only when reporting services runs in sharepoint integration mode. It works only with reports that are designed using Report Designer or Report Builder. You can not create alert for Power View reports.

Data Alerts Designer

You can create one or more data alerts for any report provided report must return the data at the time you create the data alert.

Steps to create a Data Alert

Open the report that you want to add the data alert, Select new Data Alert from the actions menu in reports toolbar



Note : You must have a permission in sharepoint to create an alert.

Using Data Alert designer you can define rules for one or more data regions in the report that control reporting services send an alert. When you save the alerting definitions, reporting services saves it in the alerting database and schedules a corresponding SQL Server Agent Job.



You can create one or more rules that compares a field value to value that you enter, Data Alert designer combines multiple rules for the same data feed by using logical AND operator.

In schedule section of the Data Alert designer , you can configure the daily, weekly intervals at which to run the SQL Server Agent job for the data alert.



Final, you must specify email address as a recipient for the data alert. Reporting services alert service manages the process of refreshing data feed and applying the rules in the data alert definition. Alerting service adds an alerting instance to the alerting database.



The email for successful data alert shows the user name of the person who created the alert and description of the data from the alert and rows from the data feed that generated the data alert. The Sample alert shown as below



If an error occurs during the alert processing then it sends an alert message to recipient describing the error message.

Data Alert Manager

Data Alert Manager lists all data alerts that you created for the report as shown below. To open the Data Alert Manager , Open the document library which has the report then click the down arrow and select Manage Data Alerts.

With Windows Server 2012 (formerly “Windows Server 8”) on the horizon and many IT shops mulling upgrades, it’s more likely upgrades to Server 2012 will be incremental rather than all-at-once. It’s likely that those with infrastructure built on top of Windows Server will have both Server 2012 and older versions of Server running side-by-side for some time.

Given that, here are a few answers to common questions in this area how the new and older versions of Windows Server might have coexistence issues.

Can I run Windows Server 2012 systems in a cluster with earlier versions of Windows Server?

The small answer is “no.” There are several reasons for this, not least of which are the major improvements in the way clustering is managed and deployed across servers in Windows Server 2012. The new clustering features aren’t backward-compatible with earlier versions of Windows Server, so clusters can’t be upgraded in a “rolling” fashion; each node in a cluster has to be evicted from the cluster, upgraded to Windows Server 2012 and added to a total new cluster of 2012-only servers.

Here are some of the key new clustering features in Windows Server 2012, which will not be supported by earlier versions of the operating system:

Storage migration . This allows cluster-managed VMs to be live-migrated to a new place while the VM is up and running, in much the same manner as VMware’s vMotion.

Clustered shared volumes . This feature is not new to Server 2012 — it was introduced in Windows Server 2008 R2 — but it’s been revised and expanded, and the expanded functionality is not available for previous versions of Server. Multiple nodes in the same cluster can share the same file system, which allows a VM hosted on any node in that cluster to be migrated to any other node on that cluster.

Cluster-aware updating (CAU) . Updates to machines in a Windows Server 2012 cluster can be applied automatically in a rolling fashion. This way, the total cluster remains online during the process. Plugins that talk to an API expand CAU’s behavior.

There are many other new features, but to use them uniformly across a cluster requires a cluster-wide upgrade to Windows Server 2012.

What do I need to know in this area using file shares between Windows Server 2012 and earlier versions of Windows Server?

Windows Server 2012 uses the new SMB 3.0 protocol (originally SMB 2.2) for establishing file shares between Windows systems.

SMB 3.0 clients will always attempt to negotiate the highest possible level of the protocol with any peer it connects with, so if you establish a share between Windows Server 2012 and earlier versions of Windows Server, the connection will be negotiated according to whatever level of SMB is available on the other server. Microsoft TechNet blogger Jose Barreto has a post with a chart that spells out the highest grade of SMB available to a connection negotiated between any two editions of Windows.

SMB 3.0′s new features are only available to other Windows Server 2012 or Windows 8 systems. Some of the new features include:

Scale-out. The same folder can be shared from multiple nodes in a cluster for the sake of failover, surpass use of bandwidth, dynamic capacity scaling, load balancing and fault tolerance.

Multichannel help. Any multiple, redundant network associations between SMB peers can be used to accelerate the connection.

End-to-end encryption. Data sent between SMB 3.0 peers is encrypted by default.

VSS help. SMB shares are now covered by volume shadow copies as well, so data on file shares can also be backed up and restored owing to any VSS-aware software.

SMB Direct. Servers that use RDMA-capable network adapters can delight in high-speed memory-to-memory data transfers with far less CPU usage and latency than conventional copy operations.

SMB index leasing. This feature reduces latency for documents accessed via the Branch Cache feature, by locally caching more of the metadata associated with the document and reducing the amount of roundtrips to the original server.

Note that if you have a mixed infrastructure where all the clients and servers use SMB 2 or surpass — Windows Vista on the client side, Windows Server 2008 on the server side — disable the use of SMB 1.x with the PowerShell command Set-SmbServerConfiguration –EnableSMB1Protocol $fake. Disabling SMB 1.x reduces the potential attack surface for the server. If the protocol isn’t in use, it’s best to disable it to preclude a possible future exploit from being used on it.

What Windows Server features are being deprecated in Windows Server 2012?

Some features in Windows Server are no longer supported as of Windows Server 2012, or are in the process of being removed. Most of these deprecations only occupy code or applications that run directly on the new OS, rather than interoperations with other editions. That said, there are exceptions especially if, for instance, you have an older application that expects the same behavior when it tries to interoperate with the newer version of Server.

Here’s a list of some of the major deprecations and feature removals in Windows Server 2012 (with more listed at TechNet), which may impact cross-server compatibility or applications running on other servers:

Clustering. 32-bit cluster resource DLLs are being deprecated and must be replaced with their 64-bit counterparts whenever possible. Also, if you have any programs that use the Cluster Automation Server (MSClus) COM API, be aware that this API is now only available via an optional component named FailoverCluster-AutomationServer, which isn’t installed by default.

Databases. 16- and 32-bit ODBC help has been removed, as have ODBC and OLEDB drivers for Oracle and Jet Red databases. (Use vendor-supplied database connectors.) ODBC/OLEDB help is also being canned for any versions of SQL Server beyond 2000; for those editions of SQL Server and higher, use SQL Native Client instead. Finally, no version of SQL Server earlier than 7.0 is supported at all. It’s unlikely that anyone is still running SQL Server 6.5 or earlier, but any attempts to connect to a SQL Server 6.5 (or earlier) instance from Windows Server 2012 will breed an error.

Committed Index. Help for resource groups and using Committed Index Lightweight Index Services as an certification store have been deprecated.

UNIX. Many UNIX subsystem features are being deprecated or removed. Microsoft entire SUA POSIX subsystem is being deprecated, along with the line printer daemon protocol that is often used by UNIX clients. As a general replacement for Microsoft’s UNIX features consider using the Cygwin or MinGW, open source tools and APIs that are maintained entirely apart from Windows’s own evolution.

WMI. Many individual WMI providers are being removed or deprecated: SNMP (because SNMP itself is deprecated); the WMI provider for Committed Index (eclipsed by PowerShell), and the Win32_ServerFeature API.

Finally, the Windows Help application (winhlp32.exe) has also been removed although it has not shipped with Windows Server since Windows Server 2008. What’s more, no add-on version of the Windows Help program is being supplied owing to Microsoft as a download, as it did with previous versions of Windows that mislaid Windows Help. (Even if, a Windows Help journal for the client journal of Windows 8 will be made available later, which must do the job.)

If you ever had the need to add a clear / reset button to your standard SQL Server Reporting Services report viewer, here’s a way to do it. Normally when reports are displayed, they are piped through the ReportViewer.aspx page that comes with SSRS. This page hosts the Reporting Server host component, and adds text boxes, radio buttton etc. based on the number of parameters you have in your report.

Something like this:



You can’t simply replace this file with your own custom page, because SSRS has HTTP handlers installed that prevents any other file to be rendered except the ReportViewer.aspx page.

So how to add a clear button to clear the text boxes? One way to do it is to modify the OOB ReportViewer.aspx page by injecting some javascript that does this for us. Initially I wanted to use jQuery, but again, the HTTP handler prohibits us from accessing the external .js file. Back to plain old Javascript it is.

Essentially, we just need to find the container that holds the View Report button, and add our custom button.

In the body tag, add a page onload event handler:

<body style="margin: 0px; overflow: auto" onload="addClearButton();">

and then add some javascript code:

<script type="text/javascript">
document.getElementsByClassName = function(cl) {
    var retnode = [];
    var myclass = new RegExp('\\b'+cl+'\\b');
    var elem = this.getElementsByTagName('*');
    for (var i = 0; i < elem.length; i++) {
        var classes = elem[i].className;
        if (myclass.test(classes)) retnode.push(elem[i]);
    }
    return retnode;
};

function addClearButton(){
    var inputs = document.getElementsByClassName('SubmitButtonCell');

    // can't find the cell, return
    if (inputs.length<1)
        return;   

    // create a button
    var clearButton = document.createElement("input");
    clearButton.type = "button";
    clearButton.value = "Clear";
    clearButton.name = "btnClear";
    clearButton.style.width = "100%";

    // add clear text boxes functionality to the onclick event
    clearButton.onclick = function (){
        var textBoxes = document.getElementsByTagName("input");
        for (var i=0;i<textBoxes.length;i++){
        if (textBoxes[i].getAttribute("type")=="text"){
          textBoxes[i].value ="";
          }
        }
    };

    // find the relevant cells
    var tdSubmitButtonCell = inputs[0];

    // find the child table
    var table = tdSubmitButtonCell.childNodes[0];
    var lastRow = table.rows.length;
    var row = table.insertRow(lastRow);
    var cellLeft = row.insertCell(0);

    // add the clear button
    cellLeft.appendChild(clearButton);
  } 

</script>

The final result will look something like this:

Authentication mechanism to login to SQL Server database engine is either Windows authentication or SQL Server account. Sometimes you will face authentication issues with database portability, example when you migrate a database from one SQL Server instance to another SQL Server instance, DBA has to ensure that all logins in Source SQL Server instance is existed on the target SQL Server instance. Organisations often experience these problems during failover when using database mirroring.

SQL Server 2012 addresses these authentication and login dependency challenges by introducing Contained Database authentication to enhance authorization and portability of user databases.

What is Contained Database Authentication?

Contained Database Authentication allows users to be authenticate directly into a user database without logins that reside in database engine. It allows authentication without logins for both SQL users with passwords and windows authentication without login. It is a great feature when you want to implement AlwaysOn Availability Groups.

Enabling Contained Databases

Contained Databases is a property which you can enable or disable via the Advanced Properties page in SQL Server Management Studio or with T-SQL

Enable Contained Database Authentication using SQL server Management Studio

1. In Object explorer, right-click a SQL Server instance, and then click properties

2. Select the Advanced page, and in the Containment section , set the property Contained Database to true and then click OK.



Enable Contained Database Authentication using T-SQL

   1: sp_configure 'show advanced options' 1,
   2: Go
   3: sp_configure 'Contained database authentication', 1;
   4: Go
   5: RECONFIGURE;
   6: GO

Creating Users

If user does not have a login in master database, the connection string must include the contained database as initial catalog. The below T-SQL can be used to create a contained database user with a password.

   1: CREATE User KennyB
   2: WITH PASSWORD = '2e4ZK933'
   3: ,DEFAULT_LANGUAGE = [ENGLISH]
   4: ,DEFAULT_SCHEMA = [dbo]
   5: GO

To migrate the SQL Server authentication login to contained database user with a password then you can use below T-SQL

   1: sp_migrate_user_to_contained
   2: @username = N '<User Name>',
   3: @rename = N 'keep_name',
   4: @disablelogin = N 'do_not_disable_login';
   5: GO

Contained Database Authentication Security Concerns

Without knowledge of DBA, user can create and grant database users in contained database if user has ALTER ANY USER permission.If user gains the access to a database via contained database authentication then user has potential to access other databases within the database engine if these databases has the guest account enabled.

Visual Studio LightSwitch is a Microsoft tool used for building business applications. If you want your LightSwitch application to be deployed as a DotNetNuke then you can.

DotNetNuke is the leading Web Content Management Platform for Microsoft .NET, powering more than 700,000 web sites worldwide. Whether you need a content management system (CMS) for your personal web site or for a Fortune 500 company, DotNetNuke has a solution that fits your needs. For details of DotNetNuke, please visit this link: http://www.dotnetnuke.com/

Before deploying yourLightSwitch applications inDotNetNuke you will need at least DotNetNuke 5 (using ASP.NET 4.0). Or you can use the newest DotNetNuke 6.

1. Open Visual Studio LightSwitch->Create new table.

2. Create a table such as Task.



3. Then, right click on Screen à Add Screen.



4. Select list and details screen->Select screen data (Task)->Ok.



5. Click on write code à Select Task_Created.



Code

using System;
 using System.Collections.Generic;using System.Linq;
 using System.Text;
 using Microsoft.LightSwitch;
 namespace LightSwitchApplication
 {
     public partial class Task
     {
         partial void Task_Created()
         {
             this.UserName = this.Application.User.Name;
         }
     }
 }

Description : This code set the Username field to the "Application.User.Name".

6. Go to properties in the Solution Explorer.



7. Click on access control-> select Use Forms authentication



8. Click on application type-> select Host application services on IIS



9. Go to configuration manager in debug.



10. Select release mode in debug.



11. Go to build in menu bar->Publish your application (DotNetNuke).



12. Click on publish for publish your application.



13. Run your application (Press F5).

SQL Server data can be transferred to a Microsoft Access database via importing or linking. Importing essentially creates a copy of the SQL Server data in the Access database. Once the data is imported, changes made in either the SQL Server database or the Access database are not reflected in one another. In other words, think of it as a one-way street. Linking, on the other hand, provides a direct connection between the two entities that will reflect any changes made to the data in either entity.

Importing comes in handy when have data stationed in your SQL Server database that you want to transfer to your Access database permanently. This specific tutorial will explain how to import SQL Server Data to an Access database. An upcoming tutorial detailing the linking process will be posted in the future.

How to Get Your Data Ready Before Import

Before you begin preparing for the import process, you will obviously need to have the proper information in place to connect to your SQL Server database. If you are not the database administrator, contact them and secure the relevant login information first.

Once you have the necessary information, connect to the SQL Server database containing the data you are planning to import to Access.

You will have the option to import several objects (tables or views) at the same time. As is usually the case when importing data from different programs, errors can occur if the formatting is not correct before the import begins. To ensure that your database’s source data gets imported without problems, follow the guidelines in this checklist:

1. Your data must be limited to a maximum of 255 columns, as that is all that Access will support.

2. Your source data cannot exceed 2GB in size. Access limits database size to 2GB, and you will also have to concede some storage for system objects as well. If your SQL Server source data is very large due to having multiple tables, you will probably encounter an error when trying to import them via an .accdb file. Should this apply to you, linking your SQL Server data to Access will provide a solution to your woes. You will be shown how to link the data in an upcoming article in this series.

3. It will be necessary to manually create relationships between new and existing tables due to the fact that Access does not create them automatically once the data is imported. You can create the table relationships by selecting the File tab, clicking Info, and selecting Relationships.

Now that you have looked over your SQL Server source data and checked that it meets the prerequisites, go to the Access database that will be the destination for the data. Before you can import anything to the Access database, you will need the appropriate permissions to do so.

You can either add data to an existing database, or add it to a fresh, blank database. To add data to a blank database, select File, click New, and select the Blank Database option. If you are adding data to an existing database, check over the tables to see that there are no errors.

How to Import Your Data

With your data prepared, it’s now time to finally import it. Open up your Access destination database, and click on the External Data tab. Go to Import & Link, and select the ODBC Database option. Select the Import the source data into a new table in the current database option. Click OK.

You must now select your data source, or .dsn file. If you see your .dsn file, click on it and select OK. If you need to create a new .dsn file, clicking New will bring up the Create New Data Source wizard, which is detailed in the next paragraph of this tutorial. If you already selected your data source, skip the next two paragraphs.

In the Create New Data Source wizard, select SQL Server from the driver list. Click Next. Enter a name for the .dsn file. You’ll need write permissions for the folder where you are saving the .dsn file. Click Next, followed by Finish.

You should now see the Create a New Data Source to SQL Server window. You have the option of entering a description of the data source in the Description box. Enter one in, or leave it blank. Enter the name of the SQL Server you want to connect to in the corresponding box, and click Next. Choose whether to use Windows NT authentication or SQL Server authentication, and click Next. Check the box next to the Change the default database to option if you plan on connecting to a specific database. Click Next, followed by Finish. Check over the summary, and select Test Data Source. If your test results are fine, click OK twice. Click OK once again until the Select Data Source window is closed.

In the Import Objects window, go to the Tables. Here is where you click on the tables or views to import. Click OK to begin importing the data.

It is recommended that you save the steps you just performed as an import specification. This will save you time as you repeat the process in the future. In the Get External Data – ODBC Database window, look for and add a check in the box next to the Save Import Steps option. Enter a name for the import specification. You can optionally enter a description in the appropriate box as well. If you have Microsoft Outlook 2010 installed and want to execute the data import during specific times, check the box next to the Create Outlook Task option. Otherwise, you can run the import at your own discretion without the help of Outlook. Click Save Import to save the specification. You will now have the import specification at your fingers the next time you want to import data between SQL Server and Access.

SQL Server Integration Services (SSIS) has under-gone through some significant changes in SQL Server 2012 which I will outline in this article.

Connection Managers

Now you have project-based connection managers which mean those connection will be available for all the packages that you are creating. This avoids recreating frequently used connections for every package. Those connections are created under Connection Manager in the Solution explorer as you can see in the below image.



As in the previous versions of SSIS, in SQL Server 2012 the connection manager will be shown in Connection Mangers region of the package. However, now there is additional text for project connections so users can easily understand and take extra care when modifying them.



By right-clicking the project connection manager and selecting Convert to Package Connection, you can demote a project connection to a package connection. Similarly, you have the option of prompting a package connection to a project connection.

Apart from the above two connection types, there are two more connection types. , namely Offline Connections and Expression Connection.



In previous versions, if a connection is invalid, every time you open the package it will hang until the connection times out to show the error. However, in SQL Server 2012, when a connection is invalid after the initial check, the connection will be set to offline and so avoid checking the connection again. When the connection is ready, you can test the connectivity and you can bring the connection online by right-clicking it. In addition, you can set the connection to offline manually. Expression Connections are simply parameters in variables.

The Execute Package Task has undergone a slight change with respect to connection managers. The Execute Package Task now has a new parameter called Reference type as shown in the below image.



Project Reference is for child packages within the project and when this is selected , you will not be shown the connections in the Connection Manager section. External reference is for the packages outside of the project.

ODBC Support

ODBC source and ODBC destination components are available in SSIS 2012. Prefviously, there were some difficulties in connecting to MySQL because of the unavailability of the OLEDB drivers for MySQL. Users were forced to use OLEDB for ODBC drivers which was comparatively slow. With ODBC support in 2012, you can directly connect to MySQL using ODBC.

Flat File Improvements

Importing flat files are very important and very frequent task used in SSIS. However, in previous versions, you are unable to import text files with variable columns and it has to have fixed number of columns. This is what you see in in preview if you try to import text file with a variable number of columns in previous versions of SSIS.



If you want to import these types of text files, you may have to use scripting which is not an easy task.

However, in SQL Server 2012 this issue (or bug the way you prefer to call it that) is fixed as you can see from the below image.



These kinds of text files are available in legacy systems such as COBOL. In such systems, there will be several different types of data in the same file. For example, Order file master details and transaction details will be in a same file. The only way you can distinguish them by the record type. For master records it will be ‘M’while for detail records it will be ‘D’.

Since the column records are different (i.e. for master records you will have customer id, date etc for detail records you will have product code, quantity, unit price, unit etc) you will need the facility to support variable columns.

Variables

You will have surely experienced difficulties when it comes to configuring variables in previous versions of SSIS. In SSIS 2012 the handling of variables has undergone significant improvements.



In SQL Server 2012, variable scope is handled different than previous versions. In previous versions, the default scope is the task which you are currently in. This led to many issues in past. If you really want to change it you could click the button at the end of row and modify the scope of the variable.

As we saw in connection managers, variables with expression now have a different icon, so that users have the ability to distinguish expression variables from others. This is very handy when it comes to trouble shooting.

Parameters

Parameters are read only variables which means you can’t change them from the package execution. Now parameters are in the package tab.



The most important feature of a parameter is the Required option. If it is set to True, you have to pass a value to that parameter. If the parameter is not passed default value will not be evaluated. By using this, you can avoid mistakes when moving from one environment to the other.

If you set the Sensitive parameter to True, you won’t be able to see the parameter value. As shown in the above image – for password parameter this is a valuable option.

In addition, you have the option of setting project level parameters where the parameters are accessible for all the packages in the SSIS project.

Data Viewer

Enabling data viewers in previous versions of SSIS required quite a bit of effort. With SQL Server 2012 SSIS, simply right click the data flow path and select Enable Data Viewer and you are done.



Similarly, if you want to disable them follow the same path.

Tasks

Before discussing about new tasks let us discuss about the tasks you won’t see in SQL Server 2012. ActiveX Script Task and Executes DTS 2000 Package Taskare removed from the SQL Server 2012. Since Microsoft has stopped supporting SQL Server 2000, it has now stopped support for DTS 2000 package execution. If you are seriously thinking about moving to SQL Server 2012, make sure you have taken steps to convert those DTS’s in SQL Server 2000 to SSIS packages.

Unlike in the previous versions, now you can edit task components while those components are not connected or they are in an error state.

Because SSIS is a development tool, and the updates are mostly of a technical nature, trying to explain their business value is quite challenging. Putting it simply, the main value to business is that with the updates, development will be easier and therefore faster.

I will focus on a few of the development improvements about which I'm the most excited.

Visual Studio 2010

Business Intelligence Development Studio (BIDS) has been replaced with SQL Server Data Tools, which uses the core of Visual Studio 2010. This does not just apply to SSIS but the whole BI development environment. This is due to Microsoft's internal realignment of their internal product delivery cycles which should help reduce the mismatch between functionality in related tools. This makes deployments much simpler and integration with Team Foundation Server 2010 a lot smoother.

Ability to debug Script Tasks

In previous versions of SQL Server, you had the ability to debug Script Components but not Script Tasks. With the release of SQL Server 2012, this is no longer the case: you can forget about having to output to the console to try and figure out where exactly your code is failing.

Change Data Capture

Although Change Data Capture (CDC) is not is not new to SQL Server, there are now CDC Tasks and Components within SSIS that make it easier to implement.

Undo and Redo

At long last you are now able to undo or redo any actions – such as bringing back the data flow that you accidently deleted – without having to reload the whole project. In my opinion this improvement alone makes it worth upgrading!

Flat File Source Improvements

Two great additions to SQL Server 2012 that will solve a lot of headaches when importing data from flat files are the support for varying numbers of columns and embedded text qualifiers.

Project Connection Managers

Gone are the days where you had to recreate connections to your source and destination within each SSIS package. Connections can now be set up at a project level which can then be shared within the packages.

Column Mappings

In SQL Server 2012, SSIS is a lot smarter about how it deals with column mappings and now uses the column names instead of the lineage ID. This means that if you decide to recreate your data source task, you do not have to remap all the columns as was the case in the past. SQL Server 2012 also comes with a Resolve Column Reference Editor which allows you to link unmapped output columns to unmapped input columns across the whole execution tree; in the past this had to be done from task to task.

Parameter Handling

Parameters are a new addition to SSIS and are very useful. In the past you had to use configurations which could only be assigned at a package level. Parameters can now be set at both a package and project level. You can assign three different types of values to parameters, namely Design default, Server default and Execution.

There are quite a few more additions to SSIS (including its built-in reporting capabilities, improvements to the user interface, and integration with Data Quality Services), but the features I have focused on in this post are improvements to issues that I have frequently come across on previous projects. I'm sure these improvements and additions to SSIS will be greatly appreciated by the industry.