Hi All,
I am a newbie to SSIS packages. I am trying to achieve following query to update destination table from source table as a scheduled task. We only insert/append new data to destination, no updates required.
Query:
INSERT WEB_DB.dbo.USER_LIST
(ID, Surname, FirstName,UserType,Timestamp)
SELECT ID
, Surname
, FirstName
, UserType
, Timestamp
FROM LOCAL_DB.dbo.USER_LIST
WHERE ID > (SELECT MAX(ID) FROM WEB_DB.dbo.USER_LIST)
ORDER BY ID
SQL Users:
For LOCAL_DB > LocalDBUser
For WEB_DB > WebDBUser
My main problem is SELECT part requires two different authentication since WEB_DB and LOCAL_DB has their own credentials. Package runs using only one credential and throws out an error.
I tried storing max(ID) in a variable, i couldn't achieve passing that value.
I also tried OPENROWSET with WEB_DB's credentials. Same error message. (LocalDBUser cannot access to WEB_DB)
Is there any tutorials similar to this task?
Thank you in advance,
Day