September 12, 2007 at 7:41 pm
Hi guys, I am running an "unspecified error" during configuration of the lookup transformation.
In lookup transformation, I specify the reference table with a table in "XYZserver", for example. Clicking preview gives me a preview of the data in that table no problem. Clicking column to define the lookup condition immediately gives me an "unspecified error" and i cannot proceed.
Changing the reference table to another server is OK, and I am able to click column OK. Any reference to XYZserver would immediately give me an error, no matter which database in the server , as I have tried multiple database.
I have also updated to SP2, from SP1 just now, and DOES NOT solve the problem
Please help!!! this has completely stopped all development! how can i develop a star schema without lookup??
Thanks!
September 14, 2007 at 5:32 am
What can you tell us about the server that it does not work on and the one that does. There must be a difference. I have found that the lookup component does not work with SQL 7 or 6.5 servers.
September 14, 2007 at 11:16 am
So far, they are all the same. So I don't understand. All the SQL Servers are 2005 with SP2. Originally it was SP1, so just to try, we installed SP2 and hoped it would be fine. But no. No such luck.
Also, same server, but different instance of SQL Server is actually FINE as well. So just that particular instance on that particular server is giving us problems.
September 14, 2007 at 11:21 am
You still have not given much information to work with here. You are doing a lookup to a SQL 2005 server. Regardless of the database or table you choose for the lookup source, you are unable to click the columns tab in the lookup component. If you change the server name in the OLEDB connection manager to another SQL 2005 server, the lookup component works fine.
Can you use any other components with the connection manager in question? What about using it in an OLEDB source component?
September 14, 2007 at 11:40 am
The problem happens in Dev machine. Dev also has multiple other databases, and we have been writing TSQL code on it for a while, everything works fine. No red flags.
Now we start to use SSIS, extracting and / or loading to/from the same DB is fine. We are able to do other things with it. But when we get to lookup transformation, and specifying the reference table. As long as you specify one of the databases in the instance, and click column, an error box pop up saying "unspecified error" which by itself is not helpful. I tried it with 3 different DB on the same instance, all of them have the same problem. All of them can be previewed fine though.
Choosing reference table to another instance is fine. Even referencing another instance on the same server is OK.
Installation and configuration wise, the 2 instances on that server are supposed to be identical, yet one is fine and one is not. Version are both 9.0.3042
Checking the server properties on both instances reveal exactly the same settings as well.
hmmmm.
September 14, 2007 at 11:53 am
I would tend to suspect permissions, but start with seeing if the lookup component is querying the database correctly. Start profiler and run a trace just before clicking the lookup tab.
The component should:
1) call sp_reset_connection
2) call SET ROCOUNT 1
3) turn on ParseOnly
4) select all columns and rows from the lookup table
5) turn off ParseOnly
6) SET NOBROSETABLE ON
If it does not do all of this or SQL kicks out an error, it should clue you into what is going wrong.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply