July 7, 2011 at 1:58 pm
This does not make sense but we started getting SSIS lookup failures on a field that has a space (lookups are space sensitive).
I traced it back to an ADO.net connection that is used to read the data from the DB2 database.
Up until the upgrade which happened yesterday this was fine, now it's an issue where the space is being stripped out as some point between the DB2 query and the insert into the sql table.
Is there some setting that may have been changed or became active with the Enterprise upgrade that would cause blanks to be removed when data is inserted?
Like I said this job has been running fine for weeks and failed on the next run following the Upgrade from Standard to Enterprise.
July 7, 2011 at 2:19 pm
Tom I have not seen this happen during a switch from S -> E, I have seen the symptom you speak of though and lookups are fairly sensitive to spaces overall. I'm wondering if you are at the same build level. R2 doesn't have a service pack yet just a CTP for SP 1. So not sure if thats it.. Any updates occur on DB2? When I had space issues like this happen in the past I explicitly stripped the data at the source connection so that I wouldn't have to deal with it later. I don't have a good answer why all of a sudden though..
CEWII
July 7, 2011 at 3:10 pm
Yeah it's strange, generally we strip off the spaces but this is an odd case when the space is actually part of the key, very frustrating indeed, we stripped the spaces and started getting duplicate keys originally, then just left them there.
We did fix the problem today by changing the ADO.net connection string and added the "retain leading spaces" directive.
Still don't know why the upgrade caused it (that's an assumption) and it may have been the reboot that caused it, perhaps an automatic update was applied during the reboot, it's a mystery but working now....
July 7, 2011 at 3:14 pm
The "spaces as part of the key" is just crazy.. Whoever designed that needs a talking to.. If you can't see it, it can't be part of the key..
CEWII
July 7, 2011 at 3:30 pm
Oh yeah you don't need to tell me about it, this is 3rd party software too, nothing we built that's for sure!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply