Error with WHERE in OPEN QUERY Statement

Question

Error with WHERE in OPEN QUERY Statement

Viewing 3 posts - 31 through 32 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Pam Brisjar (3/12/2012)
Data Warehousing | Integration Services
http://www.sqlservercentral.com/Forums/Topic1265061-364-1.aspx
basic pseudocode:
INSERT INTO [table1]
(
col1
, col2
, col3
SELECT oq.c1
, oq.c2
, oq.c3
FROM OPENQUERY(DW_DB,
'
SELECTDISTINCT
c1
,c2
,c3
FROM dw.sourcetable
') oq
LEFT OUTER JOIN SSDB.dbo.table1 AS t1
ON oq.c1 = t1.col1
WHERE t1.col1 IS NULL

Pam,

Have you used OPENQuery from within SSIS?

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Pam Brisjar SSChampion Points: 12094 More actions · Answer 2

When I need to do this within SSIS, I place the code within an SP and call that SP from SSIS. OPENQUERY itself doesn't work that well from SSIS. There is a way you can do a direct DB2 query in SSIS, but we have found that it doesn't work as well as doing it from within a stored procedure.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thanks again Pam.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/