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

Introduction to SSIS Projects in Denali

The next version of SQL Server has been unveiled and it has a codename of Denali (aka SQL11, not aka SQL2011). SQL Server Integration Services (SSIS) is receiving quite an overhaul in Denali and over the next few days I’m going to outline some of the changes. Today? Projects!

In SSIS today (i.e. prior to Denali) we build these things called SSIS packages. Packages can and often do call other packages and this type of multi-package topology is prevalent in many SSIS implementations today however there isn’t much provided with SSIS (other than the Execute Package Task – and even that has issues) that directly supports it. For example:

  • We have to pass values from package to package using esoteric mechanisms like Parent Package Configurations
  • All logging in SSIS is configured on a per-package basis
  • Deployment of a system that utilises SSIS means moving each package as a single file and making sure that each package goes to the place where other packages expect it to be
  • There is nothing in the management infrastructure for SSIS that recognises implementations using this multi-package topology
  • No relative paths. Anywhere.

Most if not all of those issues are solved in Denali which introduces the notion of SSIS Projects. A SSIS project at its most basic is an encapsulation of multiple packages into a single “thing” that you build, deploy & execute. Let’s take a look.

 

Ostensibly within Business Intelligence Development Studio nothing much looks different, an SSIS project in Solution Explorer still looks pretty much the same:

image

On closer viewing though there are some differences not least when we right-click on a project. The image on the left shows what we see when we right-click on a project in SSIS2008, the one on the right shows the same in Denali:

image image

The addition of items like “Deploy” and “Convert to Legacy Deployment Model” point to a fundamental change in how we deploy our SSIS artefacts and indeed that is the change to deploying a project as opposed to many packages. You will notice though that we still have packages within Solution Explorer so you might ask “How do we convert many packages into a deployable project?” Answer: Build. Prior to Denali the act of building a SSIS project would do little more than copy the SSIS packages to the project’s bin folder and I don’t know anyone that actually did that; in Denali building your project (which is a requirement) puts those packages into a file called an .ispac file. If I build the “ProjectParametersDemo” depicted above then I find a ProjectParametersDemo.ispac file in my bin folder:

image

And here is the output I get from building the project:

------ Build started: Project: ProjectParametersDemo, Configuration: Development ------
Build started: SQL Server Integration Services project: Incremental ...
Starting project consistency check ...
Project consistency check completed. The project is consistent.
ProjectParametersDemo -> C:\Users\jamie\Documents\Visual Studio 2008\Projects\Denali test\ProjectParameters\bin\ProjectParametersDemo.ispac
Build complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

Build? Consistency checking? Its starting to feel like a proper development experience isn’t it? In case you were wondering whether the innards of this .ispac file bear any resemblance to the .dtsx files that it includes then the answer is a categorical “no”. If I open ProjectParametersDemo.ispac in Notepad then this is what I see:

image

Not XML anymore, its binary gibberish (UPDATE: Its actually a zip file) thus proving that building a SSIS project does now actually do something. I think this is important because it emphasizes the point that you develop source code, you build into object code, you deploy. SSIS is moving to a traditional development paradigm where the .dtsx files are the source code which get “compiled” into object code in the form of .ispac files.

OK, I’ll keep this brief and stop here. At this stage we have a .ispac file, aka a Project file, that encapsulates all of the packages that we have built in this implementation and in subsequent posts I’ll talk about what you do with it.

@Jamiet

Published Wednesday, November 10, 2010 8:06 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

 

Ed Gillett said:

I look forward to seeing if the dtsx source still gets mangled when you open anything just to look at it without changing anything. Source control of BI projects in 2005 and 2008 is a pain.

November 11, 2010 5:47 AM
 

Rhys said:

Mmm, binary now? That's made a load of third party add-ons useless!

November 11, 2010 6:15 AM
 

jamiet said:

Ed,

Indeed it is. There was talk of building something to alleviate this problem but I don't know if it made the cut or not. I guess we'll find out in a future CTP.

-Jamie

November 11, 2010 6:22 AM
 

jamiet said:

Rhys,

Which add-ons did you have in mind? Remember, the .dtsx file doesn't contain any 3rd party add-ons/tasks/components, merely references to them. I don't see why that should break just because the packages are distributed in a binary format.

-Jamie

November 11, 2010 6:23 AM
 

Peter said:

ispac files are just zip files in disguise

November 11, 2010 6:50 AM
 

jamiet said:

Hi peter,

Yeah, I've just realised that myself. I'll update the post.

thanks

Jamie

November 11, 2010 6:59 AM
 

SSIS Junkie said:

In my last blog post Introduction to SSIS Projects in Denali I talked about the new Project deployment

November 11, 2010 2:58 PM
 

SSIS Junkie said:

In my blog post of 10th November Introduction to SSIS projects in Denali I spoke of how, in Denali, SSIS

November 12, 2010 1:41 PM
 

SSIS Junkie said:

In the last few days I have written 3 blog posts covering some new features in SQL Server Integration

November 13, 2010 1:50 PM
 

nicolas said:

Hey,

it is starting to look a lot like a Java Web deploymnet.

it creates a compressed file with all the packages, and it needs a deployment file to know how everything works together.

Next things, we might even get the ability to create projects that are used libraries by other projects.

thanks a lot,

Nicolas

February 24, 2011 1:14 PM
 

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

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