Sunday, 14 November 2010

SQL Server Denali: SSIS Configuration and Package Execution

One of the first things you note from the release notes are the changes to SSIS configurations in Denali. This is excellent news because the use of configurations in previous versions of SSIS weren't without their problems. The inimitable Jamie Thompson has a series of excellent posts about it which I won't repeat here, plus there is additional information on the MSDN Wiki.

Briefly however, the new deployment model involves deployment to a specially constructed SQL DB otherwise known as 'SSISDB' (at least in this CTP) which appears under its own node in SSMS - with its own GUI options. The SSISDB database is the SSIS 'Catalog' and includes the concepts of environments and parameters to allow run-time configuration of packages. Therefore SSIS is now 'environment aware' and there is no need to have to store configuration locations outside of SSIS in Windows environment variables and so on - which was always a bit awkward. Parameters can now be created on a project level removing the need to create Parent package configurations. All of this can be administered by using views and stored procedures, the API or the aforementioned SSMS.

However the one thing I was left wondering was: 'how do I execute a package under the new configuration model without using the SSMS GUI?'. DTExec still exists but does not appear to have options to use the new environment variables (at least in the current CTP). Happily perusal of the new MSDN documentation reveals the answer.

Firstly create and environment called 'env1', deploy an SSIS project called 'Project' with a package called 'Package1' to a folder called 'Test'. See Jamie's blog to set these up. Then do the following. First of all you need to retrieve the appropriate reference id for the project and environment:

DECLARE @execution_id BIGINT

DECLARE @reference_id BIGINT

SELECT @reference_id = e.reference_id

FROM   catalog.project_environments e

       INNER JOIN catalog.projects p

         ON e.project_id = p.project_id

WHERE  e.environment_name = 'env1'

       AND = 'Project'
Then we need to tell the SSIS Catalog that we want to execute the package with the particular reference:

EXECUTE catalog.create_execution

  @folder_name = 'Test',

  @project_name = 'Project',

  @package_name = 'Package1.dtsx',

  @reference_id = @reference_id,

  @execution_id = @execution_id OUTPUT

Finally tell SSIS to execute using the execution id created above:

EXECUTE catalog.start_execution @execution_id = @execution_id

A couple of notes:

1. The package referenced by 'create_execution' has to have the 'Entry-point package' property set in Visual Studio:

2. There is an unused parameter in the above call to 'create_execution' - '@use32bitruntime' - use this on 64-bit environments if you want to force SSIS to execute in 32-bit mode.

This leaves two overall configuration options as I see it. You could either:

1. Have one SSIS 'environment' for every application you have. This means that the SSIS environment referenced at runtime would be the same across Dev, Test, Live and so on. Obviously the parameters themselves would be different!

2. Have all of your application/network environments (ie Dev, Test, Live) created in every instance of the SSIS Catalog and reference the appropriate one at runtime. This is obviously more flexible but probably carries more risk of running the wrong configuration on the wrong environment - with potentially serious consequences!

SQL Server Denali: First Impressions

So I, along with a great many others, have been playing around with Denali CTP1 over the last few days and reading about the many announcements made at SQL PASS. Its become clear that its probably the biggest release since SQL Server 2005 in terms of new features, with possibly the biggest splash being made by 'Project Crescent' (the reporting tool I mentioned in my previous Denali post) and the Business Intelligence Semantic Model (BISM).

They're both described in quite a bit of detail here, and the introduction of BISM has provoked some misgivings in the developer community. However the introduction of the Powerpivot engine (otherwise known as Vertipaq) to developer tools, has to be a good thing even if they haven't managed to integrate it with Analysis Services. Potentially this could even mean the end of data warehousing and ETL, although this has been tried before...

Project Crescent is Silverlight-based and looks like the sort of end-user BI tool those of us working with MS BI software have been waiting for since Reporting Services came out. There's a very brief demo of it here.

On a more detailed note as I said in my previous post you won't be able to use the new Denali CTP1 projects if you have 2008 or 2008 R2 installed and I found that I had to uninstall the 2008 Team Foundation Client as well. However if you're using Windows 7 PowerShell 2.0 is already installed.

Additionally on starting SQL Server Management Studio (SSMS) I found the Create Catalog on the Integration Services folder (of which more in a later post) greyed out as follows:

When I tried to connect to the (now superceded) Integration Services Server I got the following 'Access denied' error:

On Windows 7 both problems were solved by simply running SSMS in Administrator mode by right-clicking on the SSMS icon and selecting 'Run as Administrator':

More soon...

Thursday, 11 November 2010

Denali available for download

As promised by MS the first CTP of the next version of SQL Server is available for download now. What's new? Check here and here.

One thing that immediately jumps out at me is the following;

Business users will gain greater agility and empowerment with a new highly interactive, web-based data exploration and visualization solution. Introducing new and engaging ways to discover insights through a drag-and-drop interface, smart and powerful data querying and interactive storyboarding to allow users to quickly create and share visual presentations of large datasets.
Sounds intriguing - but I'm not sure its in the CTP. Needless to say I'm downloading now...


Its worth checking the release notes here. There is a dependency on Powershell 2.0 - and more importantly no support for side-by-side installations with 2008/R2. There is also no support for XP regardless of SP version.