SSIS and the Perils of Embedded SQL

The perils of embedded sql are mainly due to the fact that the system in which the sql is embedded into does not have any mechanism also embedded into it such that it can parse and understand the sql itself. The reason why this is bad is that if the underlying database schema changes, then the system has no way of knowing that the embedded sql no longer matches that schema, and can therefore cause the developer to spend a lot of time attempting to debug the wrong problem.

So, to use this information in the context of SSIS, let's say that you intend to use a temporary table to handle your staging area, as per this blog post, here:

http://blogs.conchango.com/jamiethomson/archive/2006/11/19/SSIS_3A00_-Using-temporary-tables.aspx

In that post, according to steps 3 and 4, it is suggested to add an "Execute SQL Task" to handle the create statement -- it is this task that I would call "unmanaged", because it required you to either manually write the create statement, or else generate it with some other tool, such as Management Studio.

If that statement was badly formed, even if it parsed correctly, would SSIS know what the problem was? What if, for instance, you accidentally created a text field with a length of 50, but it was supposed to be 150? What kind of results would you get back from SSIS? And what if there was a new field created or remobed in/from the schema, well now you must manually hunt down all occurences of embedded sql and manually figure out how to correct the problem. (Granted the 'hacker' in me might think to use search and replace on the ASCII nature of those files, but who knows what other problems that could lead to...)

Whereas the programmer in me says that all of the schema information needed to create that temp table already exists and is accessible to SSIS, and if any modification such as special restrictions etc. are needed, they could be handled with a properties screen; errors would be avoided upfront, and if the schema changed, the "management process" could easily detect and either correct it automatically, or steer the developer to correct it with accurate information about the problem.

An example of where SOME management does already exist in embedded sql within SSIS is with the SCD component -- it generates an Ole DB Destination, and an Ole DB Command; if the inputs to the command object change, invalidating the underlying embedded sql, a warning/error icon is displayed on the object, and when you double click that object, it immediately comes up and tells you which fields are in error, and offers choices for how to handle the problem.

Finally, let me just give you a little perspective on where I'm coming from... I hold a Bachelors Degree in Information Technology -- that is to say that I am not, and am not intended to be, the best programmer on the block... My focus is the accurate flow of information. Despite this, I know so much about the problem, the schema, and programming in general, that I should be able to use SSIS to do almost anything "simple" without having to resort to work-arounds that involve the nitty-gritty details of "hard-core sql programming". Yes, I also know enough to know how to learn those nitty-gritties, but that is not what my job is, and diverting from what my job actually is will dillute my abilities to get my regular work done.

Adventures with SSIS 2005

Okay, before I get started on a rant, let me first be fair: SSIS, or SQL Server Integration Services, is a very cool and powerful new product included with SQL Server 2005 and newer. It allows you to visually design data transformations, and "ETL" (Extract, Transform, and Load) in a way that I had never thought possible.

My run-in with it is based around a major database fix I'm working on whereby a database that I've inherited from another developer has a massive no-no right in the middle of it... In a fit of "mad genious" (and no doubt under the gun of a deadline) the developer decided to attempt to circumvent the problems of a composite primary key in an important relational table of the db by creating a "derived key" by means of appending one tables pk (TripNo) to that of another (ClientNo). The resultant key was called "TripClient", and that key was then used in a multitude of other tables as a foreign key.

Like I say, there was some genious to this, but in fact there was far more madness: In order to accomplish this, both TripNo and ClientNo were created as Text values, rather than identity columns, and so the MS Access database forms were required to manage the assignment of new values using VBA. Furthermore, there was just no way in hell that the database could maintain the TripClient values, and the code that was created to manage the keys had terrible bugs which ended up in massive data integrity issues. None of these columns except for TripNo could be converted "in place" to actual numbers due to various issues, such as the presence of spaces, letters, you name it, and where these errors existed in the ClientNo, they were appended into the TripClient value, causing to be ever more invalid than ever and perpetuated through the entire database!

Worse, because the original developer had trouble assiging unique values, he resorted to adding and subtracting the number 10,000 to and from the attempted new ClientNo for new clients, and sometimes this would fail on the subtraction side, causing the ClientNo to grow in intervals of 10,000 at a time! Crapola I tell you, crapola!!!!

So, all that said, there was no possible way to upgrade/fix the database in place.

My answer? To create a new, empty, shell of the database, correct/normalize all tables using identity columns (and tossing out the whole composite key issue anyhow, because it violates the business rules! ie, far more madness than genius, once again...) and then to transform all existing data into the new database.

Enter SSIS...

After writing several lines of code and testing the performance of my efforts, I realized that I was never going to finish the job... Due to the nature of the changing primary key values of two of the most critical tables in the database, the transform that I was trying to create was just not working. The very nature of the transforms did not map well to the RBAR (row by agonizing row) code I was writing, and did not allow me to visualize the processes.

The minute I began using SSIS, however, I realized that this was the ultimate place to be performing this transformation, and was sure that I was destined for almost immediate success...

Have I been successful yet, though? No, not yet... as it turns out, SSIS is completely ignorant of "blob data fields", ie text and ntext, which are critical fields commonly used throughout the database for storing notes about the "people, places, and things" documented within the database. Because of this a whole host of work arounds are suggested by other afflicted users, and these things.

But before I go into what went wrong, I'm tempted to ask: Am I still doing this wrong??? Maybe I should post before and after diagrams of my schema and seek the advice of those with greater experience and training in this field.

What do you think?