Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Query that runs in SSMS doesn't run in SSIS OLE DB source Expand / Collapse
Posted Friday, February 22, 2013 9:12 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, February 26, 2016 4:09 AM
Points: 568, Visits: 716
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.
Post #1423118
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse