Link to home
Start Free TrialLog in
Avatar of printmedia

asked on

SQL Server 2008 Job Fails but runs fine when package executed manually

Hi all.

I have a SSIS that has a vb script with the code below, it deletes and imports data into a linked server (the linked server is SQL Server 2000):

Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        con.ConnectionString = "Data Source=myserver;Initial Catalog=Product;Integrated Security=True"


        cmd.Connection = con
        cmd.CommandText = "DELETE FROM [mylinkedserver].[mydatabase].dbo.BlanketOrder_ROP"

        cmd.CommandText = "INSERT INTO [mylinkedserver].[mydatabase].dbo.BlanketOrder_ROP(ItemNumber, ROP) SELECT ItemNumber, ROP FROM BlanketOrder_ROP"

		Dts.TaskResult = ScriptResults.Success

Open in new window

When I execute the package in SQL Server Business Intelligence Development Studio it runs fine with no errors (it actually deletes the data and then inserts the updated data). But when I create a SQL Server Agent Job it fails with the following error:

Source: Import into mydatabase database on mylinkedServer BlanketOrder_ROP table      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: DELETE permission denied on object 'BlanketOrder_ROP', database 'mydatabase', owner 'dbo'

What do I need to do to have the SSIS run through the SQL Server Agent Jobs?
Avatar of ste5an
Flag of Germany image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of printmedia


Ah yes, I forgot to change the "Run as:" user to my LinkedServerUser, it was using the default SQL Server Agent Service Account.

Thank you!