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