What to do when your tool tries to do too much

I have started using SSIS to propagate 40 reference tables from one system to another.
SSIS reminds me of Crystal Reports – it works well for the simple cases and does have tools to allow complicated options.  However the complex parts become very fragile.

For crystal reports I eventually took to putting as much of the logic as possible into a view or a sproc so that crystal only requires a simple select plus a few formatting options.  If you treat Crystal as a presentation layer you get two benefits – far less debugging plus a possibility of moving to a better option when one becomes availbale.

SSIS is similar.  I was having a lot of trouble coping with moving unicode data around.  The best solution that I could come up with was to alter the select commands to cast the data to the right type.  SSIS can perform automatic name matching if the column names only differ by case.

My transformation goes:

Select from Sybase -> Staging Table
Call Proc that updates/inserts the real table from the Staging Table.

How much is SSIS actually buying here?  A custom app could do all this and I could define the mapping in a seperate table (which I can’t do with SSIS).  In addition the error handling in SSIS is a bit of a joke.  Eventually it comes down to using the profiler if a problem is detected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s