I have a query that uses the HASHBYTES function with the intent of comparing the hash of the source table to the hash of the target table. The idea is to identify new, updated, and deleted rows from the source. The query runs fine in SSMS but I get a "the data source names must be unique" error in SSIS. (Note, this is returned as a Visual Studio error.) Obviously, since the query works in SSMS this isn't really the problem.
Originally, the source table was on a linked server and the query does a FULL OUTER JOIN so I thought that might be the problem. I copied the source table over to the local server (to a different database than the target) so the query did a local cross database join. Still works in SSMS but not in SSIS.
Finally, I copied the source table to the target database and did a simple join with the same results. The column it's throwing up on is the primary key that I'm joining on. [Individual ID] This does have the same name in the source and target but they're referenced by aliases, i.e. src.[Individual ID], dest.[Individual ID] so this isn't an ambiguity issue. (Did I mention it works in SSMS?)
Anyone have any thoughts? While this is the first time I've used this pattern, I know it works because I have friends who have used it elsewhere and provided me with samples.
"Beliefs" get in the way of learning.