July 8, 2015 at 7:28 am
I am trying to use SSIS to transfer objects. When using my own account in SQL DataTools with full permissions on both the SQL and the Windows(destination and source) machines the dtsx package runs fine. When running it under SQL Server Agent the job fails with a permission error on the very first schema. Doing some normal trouble shooting I elevate the permissions from the destination Agent to full sysadmin on the source server, still the same error. I then log onto the destination server as the Agent account and tried to connect back to the source server, no issues. I decided to then run SQL data tools as the destination Agent, which is the local agent account, on the destination box. I was able to create the SMO connection to the source server and select the DB, however for the destination server I was not able to return the list of available DB's
Any idea's?
July 9, 2015 at 5:32 am
What specifically is the error?
July 9, 2015 at 5:50 am
The exact error was
Copy Data: Error: Table "[Schema].[TableName]" does not exist at the source.
Well this turned out to be a real error. Somebody had deleted the table. The one issue I found is even though I have updated the dtsx job not to include that table until I recreated the SQL job step using the package I was still getting that error.
July 9, 2015 at 6:05 am
Update your package to include IF NOT EXISTS before you create the objects. That should avoid your errors.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply