Avatar of printmedia
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"

        con.Open()

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

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

		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?
Microsoft SQL Server 2008SSIS

Avatar of undefined
Last Comment
printmedia

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
ste5an

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
printmedia

ASKER
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!
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck