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.

SQL SSIS RetainSameConnection property

I have not posted any blog entries in quite some time. obvious. I am currently knee deep in the trenches of SSIS and have found a few quirks that I would like to note in my next few entries. Today's SSIS quirk is [RetainSameConnection] property. I discovered this little gem when I was trying to add data flow tasks to an existing SSIS package. The package worked for the first data flow task, but was un-successful in the 2nd data flow task. Both tasks access the same servers, same databases, just different tables. Why would one work, but the other NOT work? It was quite obvious it was the [RetainSameConnection] property on your DATA CONNECTION OBJECT, right? No, actually it was not quite obvious. One would think that SSIS would be user-friendly enough to re-use your connection for you. Apparently not. I can see and understand WHY, but I just found it interesting and noteworthy. I promise I will have more SSIS quirks just as silly as this in the future. Stay tuned and hang on to your seats!!