THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

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

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:

image

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:

image

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:

image

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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…”:

image

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:

image

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:

image

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:

  1. Design your packages in BIDS as a SSIS Project, including Project and Package Parameters as appropriate
  2. Set up Environment objects on your SSIS server containing relevant Environment Variables
  3. 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)
  4. Associate your Project with a set of Environments
  5. Specify Project and Package Parameters overrides using Environment Variables (this step can actually be done within the Deployment Wizard)
  6. 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

Published Saturday, November 13, 2010 7:50 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SSIS Junkie said:

This post relates to SQL Server code-named Denali which at the time of writing is a pre-release version

November 19, 2010 2:25 PM
 

Hennie de Nooijer said:

well written!

Are package configurations obsolete in Denali?

Should package configurations replaced by parameters and environments?

November 23, 2010 6:47 AM
 

jamiet said:

Hi Hennie,

No, package configurations are not obsolete. The old way of working is still totally supported, just know that package configurations are no longer required if you move to this new way of working using projects and the SSIS Server.

In fact, during development if you opt to use the new model (which is the default) the capability to build configurations is removed from the Designer.

Hope that helps.

-Jamie

November 23, 2010 7:08 AM
 

Anil said:

When we are thinking about having a single instance of SSIS serving all three environments, how will I manage deployment of my subsequent releases? If I deploy a project/package with some modifications, will it not replace for all the environements at one go rather than sequential dev, test and then production?

April 13, 2011 5:33 PM
 

jamiet said:

Hi Anil,

Good question - and not one that I have a definitive answer to right now. I *do* know that there is a notion of project versions on the server so I assume (can't confirm right now) that you can execute old versions of packages.

I'll put that on my list of things to find out about.

Regards

Jamie

April 13, 2011 5:56 PM
 

SSIS Junkie said:

The third Community Technology Preview (CTP3) of SQL Server code-named Denali is upon us and, although

July 12, 2011 4:32 PM
 

SSIS Junkie said:

In my blog post SSIS Server, Catalogs, Environments & Environment Variables in SSIS in Denali from

July 20, 2011 11:49 AM
 

SSIS Junkie said:

In recent blog posts I have introduced the new SSIS Catalog that is forthcoming in SQL Server Code-named

October 16, 2011 10:57 AM
 

SSIS Junkie said:

My mind was wandering today after reading Andy Leonard's excellent post Name Those Connections and it

February 9, 2012 9:10 AM
 

LL said:

Do you know if there's a way to access the environments via SSDT?  For example, we might have 2 development environments - patch and release.  During unit testing, I might want to execute my package in SSDT against either of these environments.  I am unable to find a way to pass this information into SSDT without manually updating the project shared connection managers.

July 25, 2012 2:43 PM
 

jamiet said:

LL,

No, Environments are not available in the dev tools.

JT

July 25, 2012 6:31 PM
 

Visakh said:

Hi Jamie

Great article!

But one question. i couldnt get the reference,parameter tabs in project properties when i view it from catalog after deploying. Does it have something to do with my permissions?

My login is member of local sysadmin group so I dont think that can be a cause.

August 6, 2012 4:33 PM
 

jamiet said:

Hi Visakh,

No, its not you. The screenshots were taken from a beta release and it appears they were changed for the final release.

regards

Jamie

August 7, 2012 4:20 AM
 

James Serra said:

Small update: where is says "The properties page of a SSIS Project that has been deployed to a Catalog" should say "The configure page of a SSIS Project that has been deployed to a Catalog".  It seems the RTM version of SQL Server split the properties page, moving some properties to a new configure page.

October 5, 2012 3:04 PM
 

Gangadhar Seelam said:

Good artical

April 16, 2013 9:48 AM
 

Nick said:

Hi,

Good article,

In follow up to Anil, I to wonder where multiple absolute references in one folder would come in handy. as one solution would imply direct changes for each environment.

A much more management efficient way of working seems to have Three folders with one identical environment in each folder. In this way you can use relative environment reference and move your projects between project phases, keeping your solution changes restricted to a development phase.

Anyone who can elobare on the use of Absolute references and their benefits.

thx and keep up the good work.

June 4, 2013 3:04 PM
 

Can multiple servers use one set of 'Environment Variables'? said:

In our environments (dev,test,prod), we have several SSIS servers in each environment.  With 'Package Configuraions' we can point each package to a single SSISCONGIG table in the administrative database for the application.  Are Environment Variables SSISDB specific or can they be directed to a 'master' catalog?  If the former, then package configurations is still the way to go in large environments.  One SSISConfig table containing all global connections and variables can be read by all SSIS packages on all SSIS servers.

And is logging stored in a central repository, or in each SSISDB.  If the latter, then multi-server environments will need to pull the logs together into a single database to provide overall view of package activity.

BTW - While package configurations are still supported in 2012, it is my understanding that it has been announced to be depricated).

June 24, 2013 4:52 PM
 

Mike Flakus said:

My appologies, the prior post was by me.

You'd think at my age I could read a field prompt.  I'm an ETL guy, not good at UI.

June 24, 2013 4:54 PM
 

jamiet said:

Hi Mike,

Environment Variables are SSISDB specific. There is no way for multiple SSIS catalogs to reference environment variables in other catalogs (or a "master" catalog).

Likewise, logging is per-SSISDB, there is no centralised repository for logging.

The lack of ability to support your topology is a failing in SSIS currently in my opinion. I have opened a Connect submission here: https://connect.microsoft.com/SQLServer/feedback/details/726102/ssis-catalog-agent that is somewhat related so I'd appreciate it if you could add a comment explaining your scenario and why the SSIS Catalog in its current guise is not fit-for-your-purpose.

"While package configurations are still supported in 2012, it is my understanding that it has been announced to be depricated"

That's news to me if so. I haven't heard about package configurations being on the deprecated path.

Regards

Jamie

June 25, 2013 4:29 AM
 

Mike Flakus said:

One of the session at PASS in 2011 stated that Package Configurations were being depricated.  It may have been a mistatement.  Technet states "There are no deprecated Integration Services features in SQL Server 2012". http://technet.microsoft.com/en-us/library/ms403408.aspx

I added a Comment to your 'Connect' submission.  I am confused why this would be 'Closed' by Microsoft 'By Design' with the statement "Posted by Microsoft on 3/13/2012 at 12:44 AM - We will be looking at centralizing logging from the angle of SSIS scale-out implementation. We are working on a white paper on this topic and will keep you all in the loop. In the meantime, we will close this thread for now."

Until there is an actual solution for this issue, I wouldn't consider it closed.  And the product certainly doesn't exceed my expectations in this area.  https://connect.microsoft.com/SQLServer

BTW - Thank you for maintaining an excellent blog.  I have been reading it since about 2006.

June 25, 2013 12:43 PM
 

Mauricio said:

Hi,

How do you pass the current date to an Environment Variable or any other non deterministic function?

January 13, 2014 1:56 PM
 

jamiet said:

Hi Mauricio,

I don't think you can. What you *can* do however is set the value of a parameter when you execute the package. If using T-SQL to execute your package then use stored procedure [catalog].[set_execution_parameter_value] (http://technet.microsoft.com/en-us/library/ff877990.aspx).

That should do what you need.

Regards

Jamie

January 14, 2014 9:33 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement