blogs.conchango.com

welcome to the conchango blogging site
Welcome to blogs.conchango.com Sign in | Join | Help
in Search
Blogs

SSIS Junkie

SSIS: Case sensitive SQL Server configurations

I just received an email from Michael Groh who had a warning about using SQL Server configurations in SSIS and he suggested I post about it on my blog which I'm more than happy to do. Here's what Michael had to say:

 

We finally tracked down a really vexing problem with configurations in a simple SSIS package. The package populates a number of variables from the SSISconfig.ssis_configurations table. We noticed that, after moving the configuration data to another db server, all of the variables were blank, and we couldn't figure out what was wrong -- no error, nothing to indicate a problem, etc.

One of our people finally noticed that the "ConfiguredValueType" was spelled "string", not "String".

This tiny misspelling was enough to cause her package configs to fail. More importantly, they failed without an error or warning.

 

Thanks Michael, that's very useful info. Just for illustration I've highlighted the attribute that Michael mentions in this example configuration file:

image

Its interesting to ponder WHY this mistake has occurred, its not as if anyone would manually go and change that value (unless they are really malicious). Its got me foxed to be honest, anyone any ideas?

 

Does anyone have anymore SSIS gotchas that you'd like to share? If so feel free to send them my way.

-Jamie

Published 22 August 2008 08:49 by jamie.thomson

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

John Welch said:

Since this was using SQL Server configurations, I wonder if the configuration rows from the original system were manually typed into the new system, rather than exporting them as INSERTs?

August 22, 2008 15:09
 

Peter said:

I don't know about why they did this, but I know I've been sorely tempted to open up the code and do a global replace to set things to varchar instead of nvarchar and similar.  I've also wanted to do massive find/replace of anything that was "String" and bump up the value quite a bit rather than deal with finding each string column, setting it, hoping I didn't miss one, etc.

The column analyzer is okay at times, but it does have some shortcomings. Add in the fact that some "date" columns contain invalid dates (0000-01-24, anyone?) and it would just be nice to do global replaces.

And on a related note, I'm pretty sure I managed to do this to my mapping files for the wizards - all of the Access mappings come across as "130" instead of (varchar|datetime - can't remember which).  I'd manually edited them to map them over to varchar instead of nvarchar, but get the feeling that I messed up the spelling or something.  Not quite sure what I can do to get the originals back for a compare.

August 26, 2008 16:57
 

Mike Groh said:

John Welch got it right -- someone had INSERTed the config records into ssis_configurations, and simply overlooked the case applied to the ConfiguredValueType column.

Because the config table is in SQL Server, no one thought that preserving case was important. However, as Jamie has pointed out numerous times, SSIS is frequently case-sensitive when you least expect it!

My main complaint is that a simple "case" issue caused the package to fail with no warnings. It seems inconsistent to use a case-insensitive data store to store data is actually case-sensitive when consumed. It's always bad when an honest, simple mistake causes failures like this.

September 9, 2008 21:59
 

jamie.thomson said:

Hi Mike,

Glad you got to the bottom of it.

I'm wondering about XML in general here, forget about SSIS. Are XML parsers usually expected to be case-sensitive? I don't actually know.

cheers

Jamie

September 10, 2008 09:52
 

Andy Leonard said:

I experienced a similar issue with SQL Server configurations and Int32 data types. SSIS created the row in the [SSIS Configurations] table with a ConfiguredValueType = "int32". The package found the value, but only some of the time.

October 16, 2008 14:24
 

nitin said:

As XML itself is case sensitive, hence the parsers are designed to be case sensitive as well.

November 5, 2008 16:26

Leave a Comment

(required) 
(optional)
(required) 
Submit

This Blog

Syndication

News
© 2008 - Any comments or statements made herein do not necessarily reflect those of Conchango.
Powered by Community Server (Personal Edition), by Telligent Systems