SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query that runs in SSMS doesn't run in SSIS OLE DB source


Query that runs in SSMS doesn't run in SSIS OLE DB source

Author
Message
Robert Frasca
Robert Frasca
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1565 Visits: 798
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search