Monday, October 18, 2010

Connection Manager Attributes Changed error in SSIS can be resolved by cleaning up your query

Just resolved a funny issue that was making me crazy for about 15 minutes. In the "Use results of an SQL query" window in the SQL Query a Lookup task in SSIS 2005, I pasted in a query I had written in SQL Management Studio 2008 R2. The formatting looked a little funny, with the little squares in place of some line breaks and tabs all over the place, but I ignored it and clicked through to the Columns tab. At the bottom of the tab, where errors and warnings appear, I got this:

Connection Manager attributes have changed. The mappings of previous columns are no longer valid.

The message stayed there even after I changed the OLE DB connection manager, added and removed columns to the lookup, and clicked every available button in the Lookup Transformation Editor. And it prevented me from clicking OK to finalize and save the transformation. I decided that I should look through the code for any bad syntax or other anomalies, but it was difficult with the screwy formatting, so I "washed" it by cutting and pasting it into Notepad++ and then back into SSIS.

And guess what? Not only did the code look better, the warning message went away. So if you're seeing that warning, try cleaning up the formatting of your SQL query.


Rob said...

Thanks for the tip... had this issue and your post saved me who knows how long. Bizzare little bug eh?

Ira Pfeifer said...

Yeah, definitely. Glad I could help.

Raeldor said...

Ditto... helped me a lot, thanks dude.