SSIS Lookup - Mismatched sqlcommand & sqlcommandparam Property Values
I had an issue recently and I'd like to solicit feedback as to whether this is an SSIS flaw: In the lookup component, when you select the Partial or No Cache option, a property in addition to the sqlcommand property becomes available called sqlcommandparam. Originally this package was developed using Lookups pointing to a dimension on "schema_A" and we wanted to change the reference to "schema_B". Now, obviously the sqlcommandparam property became available because you can elect to use the Advanced SQL option in the component. However, I never chose to do that. So, long story short, I go into this package to change the schema reference in the Lookup's query. It changes the sqlcommand property value to be "select value from schema_B.table" but it didn't change the corresponding value in the sqlcommandparam property. That property remained "select value from schema_A.table". SSIS never threw an error to say the schema's didn't match. And the Lookup worked as designed, picking up values from the table on schema_B. Not until we finally decommisioned schema_A did I get an error at runtime that said "Table or view does not exist". So, can anyone (hopefully Microsoft) confirm that this is in fact a flaw? I am on MSFT VS 2008 V 9.0.30729.1 SP.Brian
October 28th, 2010 11:22am

Microsoft Connect is the right place to submit software defects. I personally do not think this is a flaw, but a warning should be emitted, afterall the schema was valid. So it is rather a suggestion even though it broke your package.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 11:40am

I agree on all accounts. I definitely think there should be a warning. Microsoft's Connect site didn't show that SSIS was accepting reports at this time.Brian
October 28th, 2010 12:06pm

Where you actually using the Partial or NoCache option on the Lookup? If so, I wouldn't say that it's a bug. In that scenario, the SQL query on the advanced tab is the only one that really matters - the other one never gets executed. As far as a warning - there are some valid scenarios where you may not have the two statements pointing at the same table. I'd rather not see warnings on something I've done intentionally. I think the better solution in SSIS would be to elimate the 2nd query, and just let you specify a single query for the lookup.John Welch | www.varigence.com | www.agilebi.com | ssisUnit.codeplex.com
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 6:00pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics