How To Refresh Lookup Output Columns in SSIS
-
I have an existing SSIS package with a lookup to a permanent table using provider "Native OLE DB\SQL Native Client". The permanent table has had two new columns added to the table, but the Lookup does not recognize the additional columns. I cannot see any way to have SSIS refresh the output columns available from the lookup table. On the normal lookup editer, the Preview function shows the additional columns, but the Columns page does not show them as available columns.
When I try to add one of the columns manually in the Advanced Editer, I get fatal errors such as:
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are: Error at Translate {...} [DTS.Pipeline]: The output column "{...}" (167438) had an invalid datatype value (0) set.
Trying to add a datatype value gives error:
Property value is not valid: Error at Translate {...}: The component {...} does not allow setting output column datatype properties.
Trying to set the CopyFromReferenceColumn property on the new column gives error:
Property value is not valid: Could not set the property value: Error at Translate {...}: Unable to locate reference column {...}.
The only workaround I could find for this problem has been to drop the lookup transformation, and re-add it from scratch. Unfortunately doing this invalidates all references to the lookup transformation output columns in the subsequent transformations, and they have to be manually re-associated to the new lookup transformation. This is getting more and more painful each time; there has to be a better way.
Any ideas?
Thanx... Steve
-
what i do is change the lookup table in the drop down to any other table. this gives a warning saying the connection manager attributes have changed and the mappings of previous columns are no longer valid.
then change the lookup table back again. click on the columns tab to remap the lookup mappings, and you should see the new columns also.
tom
Life: it twists and turns like a twisty turny thing
-
Why are you selecting the whole table though?
If you are not going to use all the columns as either keys or returned values, do not select the whole table. It wastes memory.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Good day
I have the same error.
I however changed my Table lookup to a SQL select statement with viewer fields to enhance the query.
What am I doing wrong?
-
don't fiddle the advanced editor.
When you change your query, just add a 1 as xxx to it, it should break the mappings. CLicking back on the mappings shows the new fields.
Do you do this and it is still failing? What is the error?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Crispin
I have a MultiCast before my lookup.
I added a new lookup, gave aliases to ALL the fields, and even tried to give aliases to my source fields.
I STILL get the same error....
It is VERY frustrating....
-
ok, try this. Delete the lookup (Bare with me here) and add a new one. Does it work?
If so, add a new column to the lookup query. When you click on columns, does it reflect the changes?
I have on many occasions "broken" a component by clicking on to many things and chaning things without know what I changed. I appears you only allowed a certain amount of clicks. :hehe:
Try from scratch, we will see from there.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Crispin
I have 40 sequence containers with each at least 1 data flow task.
I need to change my queries on all of them.
Does not matter witch one I choose I get the same problem.
Redoing my project is not an option....
Tried deleting as you suggested, with no luck.
What concerns me is that the error seems to be a "Connection Manager" error, and not the lookup conponent.
"Connection Manager attributes have changed. The mapping of previous columns are no longer valid."
PLEASE HELP....
-
That is not a connection manager problem.
If you have 40 data flows with 40 lookups with 40 queries, guess what? You have many many places to change this.
If you have a lookup who's query has changed, either because you a fool for selecting the whole table or a query using * or because you thought of something you needed to add (which is unfortunate), You have to go through each one and change it. There is not Change All button.
Now, your problem still confuses me. You say you have a lookup that is complaining about a changed query.
When you delete this and re-add it, and redo the mappings, you still have this problem?
Can you provide a screen dump?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I've been having the exact same problem -- but finally figured out a way to get the table refreshed and the additional columns to show up. On the Reference Table tab of the Lookup Transformation, I had "Use Table or View" selected and was performing a lookup directly against a table (e.g., dbo.MyTable). To get the table refreshed, I changed my selection to "Use results of an SQL query" and entered select * from dbo.MyTable. When I clicked on the Columns tab, all of the new fields I added instantly appeared and all of my previous Output Aliases remained intact. I then went back to the first tab and chose the table again, and it worked. Maybe this will work for you?
Ryan
-
For so many reasons I and others have mentioned - do not use the table. ALWAYS use a select col1, col2, col3 from table.
Search the forum or google for lookup best practices.
When I am more bored than I currently am, I will post, again, the reasons not to do it.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
This Error appears if there are somehow not serializable characters in the sql statement. f.e {CR}{LF}. You may run into that by pasting the query from an external editor.
Although you get a right preview and a positive parsing result it comes out to this "Connection has changed.." error message" which is completly displaced here.
This characters appears as small squares in the query field. Delete them out and it should work.
jens
-
jens.fache (5/6/2009)
This Error appears if there are somehow not serializable characters in the sql statement. f.e {CR}{LF}. You may run into that by pasting the query from an external editor.Although you get a right preview and a positive parsing result it comes out to this "Connection has changed.." error message" which is completly displaced here.
This characters appears as small squares in the query field. Delete them out and it should work.
jens
OOHHHHHH!! YEAH! That was it for me. I pulled the carriage returns out and works like a charm. YOU ARE MY HERO! Thanks Jens.
Chad
-
UPDATE: It appears just using the Build Query (click to open, then hit OK) resolves the issue. (for now anywya 😉
------------
I tried numerous things, including having to delete the entire package and do exactly the same steps to rebuild and it would work. <sigh>. This is not an option for the package Im working on today. In playing around, I used the Build Query option. I had a very simple query and the only change it made was putting parens around my WHERE condition. It seems to be working fine now.
SELECT col1, col2, col3 FROM table1 WHERE (col4 = something)
I hope this helps. VERY frustrating bug.
-
I had the same problem and the only way I was able to resolve it was to click the Build Query button to go into the query builder. Then click OK to come back. Then I as able to click Columns and save.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply