SSIS Scheduled Job

• day-476284

  SSCommitted

  Points: 1844

  More actions

  August 8, 2008 at 11:59 am

  #382207

  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

• Sarang Bobde

  Mr or Mrs. 500

  Points: 534

  More actions

  August 8, 2008 at 4:17 pm

  #855573

  Can you post the error message as well ?