SSIS quirk #2 (and #3 kinda?)

So I didnt keep my promise. I did not post in my blog yesterday. I was actually quite productive at work and was unable to find time to do it. So here I am today trying to remember what it was I wanted to actually write about. I am going to rush this one. I want to talk about metadata. Column metadata. And dynamic connection objects.

Connection objects are pretty vital in an SSIS package. By default, anytime you want to access data, 9 times out of 10, you're going to need a connection object, wether its to SQL, Access, Excel or "other". That's non-trivial, since the birth of ole db/sql connection objects.

When you begin developing an SSIS package, the connection object is generally hard-coded. It is important here to realize and note that if you remove your hard-coded connection string, or change it, it will destroy the metadata. The metadata is the XML mappings to your columns and database objects.

My scenario is that I need to have dynamic connections in my SSIS package. I have users uploading Access databases to a SQL 2005 destination. But the destination is not centralized, it is unique to each deployment of the project. What I mean is, each app has it's own SQL 2005 database. The user has different Access .mdbs to transform to SQL.

So when I started developing this SSIS package I did not know that removing my connection object's connection string value would basically destroy the package. I think it is important to realize that you should create your SSIS package with a hardcoded source and destination connection. Once the package is tested and complete, you THEN are ready to make the connection dynamic.

It is pretty much impossible to keep a dynamic connection and keep SSIS metadata happy. Maybe there is a flag, property or setting I am overlooking, but in my experience, metadata must be happy in order for SSIS packages to execute. This is a weird quirk (quirk #2), and I would think that SSIS would ignore metadata at design-time.

Another quirk is that even though you get metadata errors, you can still RUN and EXECUTE your package, even successfully. You may have 12 error messages, but the package can still be DEBUGGED and RUN. I think this is a very strange quirk. Why would metadata produce errors and not warnings? Apparently the errors are ignored anyways. But not always. This can be quirk #3. It's my blog, I can do what I want.

But now you are probably thinking, why would quirk#2 be valid, if quirk #3 is true? Well, if you need to make changes, or add a task to your package, it will absolutely not work. You have lost the metadata, therefore, you cannot build upon your BROKEN SSIS package. The terrorists win. End of story. No ifs, ands or buts about it.

What a strange world SSIS is. I feel like I am absolutely lost in it sometimes. When I go to google my SSIS woes, I end up helping everyone else with problems I have already come across. In some ways I feel like the Robin Hood of SSIS. Not by choice though. I have absolutely hated SQL since day one. I still dont understand why DBA's dont have to develop SQL, but programmers do. I hate DBA's but that's another story. Maybe another day.

I will have a 4th SSIS quirk coming soon, before the end of the week. Hold on to those seats.