SSIS Server, Catalogs, Environments & Environment Variables in SSIS in Denali

jamiet — Sat, 13 Nov 2010 18:50:00 GMT

In the last few days I have written 3 blog posts covering some new features in SQL Server Integration Services (SSIS) in the next version of SQL Server, code-named Denali:

In this blog post I’m moving onto talking about the new SSIS server and some new terminology associated with it.

In SSIS 2005 and 2008 there was the notion of an SSIS server but it didn’t particularly do very much:

Not much in there except for the ability to view running packages and also see what you have stored. Not particularly useful in my opinion and certainly not something I have ever used.

That all changes in SSIS code-named Denali. The SSIS server is now an integral part of the new way of doing things that involves the new deployment model that I explained in the posts linked to above. For starters the SSIS server is no longer a separate service that you connect to, it now appears as a node in Object Explorer when connecting to a SQL Server database instance:

When you first try and expand that node you’ll discover that there is nothing underneath it so you need to create yourself a Catalog which requires you to specify a database master key:

You don’t get to choose the name of the Catalog (not in this CTP anyway), it is called [SSISDB] and you can only have one of them per SQL Server instance. Under the covers a database with the same name as the Catalog is created:

Note that in order to create the Catalog you must enable SQLCLR which (for easy reference) is done using:

sp_configure 'clr enabled', 1; GO RECONFIGURE GO

A Catalog stores (amongst other things):

Deployed Projects
Environments
Package execution logs

This last item, Package execution logs, is important. In previous SSIS versions you the developer were responsible for configuring all of your logging options such as what events you were going to log, which containers you were going to collect logs for, and where you were going to store those logs. Back in 2003 I used a competitor tool called Informatica and in the intervening period I have became incredibly frustrated that SSIS did not include any built-in logging infrastructure because Informatica did include it and I hated having to go through the rigmarole of setting up a logging infrastructure and all the associated paraphernalia on every single new SSIS project that I went onto, especially as it is so time consuming. I, like I know many others have done, came up with my own method of doing it (read Custom Logging Using Event Handlers – my 2nd most popular ever SSIS-related blog post) as did Davide Mauri (read DTLoggedExec) because the logging mechanisms in SSIS have been so woefully inadequate.

In SSIS code-named Denali that all goes away; the SSIS server is responsible for managing all of your deployed projects/packages and in turn manages both the execution of them and the collection of log information. It is this fundamental shift that has me most excited about SSIS code-named Denali; instead of having lots of options for logging we now have a definitive one and over time it is the one that everyone will use. That consistency has to be a good thing because we as developers can get on with the serious business of implementing business logic rather than worrying about dull matters like deployment and logging.

Within the Catalog you create a one-level folder hierarchy (you must create at least one folder) and under each folder is a collection of Projects and a collection of Environments.The Projects folder is the place into which you deploy your SSIS Projects:

N.B. My naming convention here which categorises Projects into business units (HR, Marketing) within an organisation is for demonstration only – it is not a recommendation.

Notice also in this screenshot that each folder in the Catalog has a node called “Environments”. In SSIS code-named Denali an Environment is an object type that you can create multiple instances of – in this case I have created Development, Test & Production. Environments are fairly self-explanatory – they are a wrapper for all environment-specific information (e.g. Connection Strings) that you want to maintain outside of a package and when you execute a package you have to choose which Environment to execute it against. In short Environments are the replacement for SSIS configurations and they work hand-in-hand with Parameters that are also getting introduced in SSIS code-named Denali. Let’s take a closer look:

There’s nothing of interest on the General tab, the interesting stuff is in the Variables tab. Here I have defined one Environment Variable called CustomerName and given it a value which in this case is a string “Beatrice”. Just as with Parameters they can be set to Sensitive which means that they will be encrypted on the server. When we execute a package from the same folder we can replace a Parameter value (either a Project or a Package Parameter) with a value from an Environment Variable.

The properties page of a SSIS Project that has been deployed to a Catalog has a References tab where we can associate a Project with one or more Environment objects in the Catalog – this is called an Environment Reference:

There are two types of Environment References:

Relative – The referenced Environment must be in the same folder as the Project
Absolute – The referenced Environment can be anywhere in the Catalog

the difference between the two is that any Project using absolute references can be moved to other folders in the Catalog without fear of them breaking – it is up to you to decide which type of reference suits you best. Notice that Environments {Development, Test, Production} appear twice in the screenshot above which is because an Environment that is available for a Relative Environment Reference can also be referenced as an Absolute Environment Reference.

Once we have added the required Environment References to our Project they appear like so:

We now have our environments, each of which contain a Variable called CustomerName (not shown here so you’ll have to trust me on that), associated with our Project. If we wish the project to make use of that Environment Variable when one of its packages is executed then we must associate that Environment Variable with a Project Parameter which we also do in the Project Properties dialog:

Note that we can also associate an Environment Variable with a Package Parameter in the same way.

This bit might take a bit of explaining. Observe the following:

A Project Parameter (of a project called ProjectParametersDemo) called CustomerName that has a Server Default value of “Harry”
A Package Parameter (of a package called Child.dtsx) called CustomerName that has a Server Default of “William”
I have clicked on the ellipsis next to the Project Parameter and am assigning it the value of an Environment Variable, also called CustomerName (there is no requirement for Environment Variables to have the same name as a Parameter by the way – I just happened to have named them identically here)
At this point I have not told it which Environment it should take the value from, only that it should be taken from an Environment.

We are now ready to execute a package from our Project which we do by right-clicking on it and selecting “Run…”:

This brings up the Run Package dialog and it is in here that we tell the execution engine which of our three environments should be used to override our CustomerName parameter:

We select one of them, click OK, and our package executes. The logging output from our execution is displayed in a new Package Running Information dialog:

One final note on logging. The fairly limited information that gets captured in the current CTP (depicted above) is not the same as what will be in the product upon release – there is a lot more to come. For example, the name of the container that raised the event is currently embedded at the start of the log message – at the very least I am expecting that to become a field of its own.

That’s it, that’s how you execute a package. Let’s review the steps:

Design your packages in BIDS as a SSIS Project, including Project and Package Parameters as appropriate
Set up Environment objects on your SSIS server containing relevant Environment Variables
Deploy your SSIS Project to the SSIS server by using the SSIS Deployment Wizard (not something I have covered here or in previous blog posts)
Associate your Project with a set of Environments
Specify Project and Package Parameters overrides using Environment Variables (this step can actually be done within the Deployment Wizard)
Choose which Environment to use for a particular execution instance and start the execution

There are quite a few steps here though I feel the whole process will become fairly intuitive once someone gets introduced to it. It is certainly very flexible as Parameters can either maintain their Server Default values or get overridden using an Environment Variable. I like the fact that multiple execution environments can be maintained from a single SSIS instance although time will tell whether people choose to use this setup or instead have a SSIS server for each environment – the infrastructure that I have covered in this blog post fully supports both.

One other thing to note is that these UI screens are merely front-ends to some stored procedures, views and functions so everything that I’ve shown here can also be done with T-SQL scripts which I think is great news because we can now call SSIS packages from within our T-SQL scripts. This gives us great flexibility over how we build our solutions.

@Jamiet

Search results matching tags 'denali' and 'Catalogs'

SSIS Server, Catalogs, Environments &amp; Environment Variables in SSIS in Denali

SSIS Server, Catalogs, Environments & Environment Variables in SSIS in Denali