Moving an SSIS .dtsx from one server to another

Question

Moving an SSIS .dtsx from one server to another

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Michael L John One Orange Chip Points: 27055 More actions · Answer 1

juniorDBA13 wrote:

Ok so the only possible issue is permissions are missing somewhere?
I have checked the file using notepad and any locations mentioned I have given full control so I am lost

Not the only possible issue, but most likely.

When you said:

The account running the package is the build in service accounts for SQL and I have added them to the administrator group on the server. The folder is empty so I am lost what else could be the problem

What account owns the SQL Job?

What "build in service accounts " is this?

I would suggest changing the service account for SQL Agent Service and SQL Database Engine to a domain account. This does not need admin perms on the server, or sysadmin in SQL

Grant that account full control as described above. Make sure you have all of the possible locations covered.

And if a different account than the service account owns the job, it will execute under that context.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 2

It is running as a SQL job. This is the way it was running on the previous server so would prefer to use SQL agent than windows task

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

How do you run it from the file system from within an Agent job?

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 4

I select Type SQL Server integration Services Package. Then for package source choose file system and provide the location of the package

MarkP Old Hand Points: 305 More actions · Answer 5

MarkP

Old Hand

Points: 305

January 19, 2023 at 10:27 am

#4139457

Who do you run it as?

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 6

NT Service\SQLSERVERAGENT

This is what is was using on the old server

I tried with proxy account is well but didn't make a difference

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 7

Should the account need access to any folders relating to SQL services specifically?

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

juniorDBA13 wrote:

Should the account need access to any folders relating to SQL services specifically?

Yes. We have already said this.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 9

Do you know specifically what folder locations? I have given to SQL server program files folder. Should it have any access to files on C drive like C:\Windows?

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 10

Should it just be the SQL agent account or SQL service and SSIS account?

Michael L John One Orange Chip Points: 27055 More actions · Answer 11

juniorDBA13 wrote:

Should it just be the SQL agent account or SQL service and SSIS account?

We do not know. You need to tell us.

What account is SQL Server and SQL Server Agent running as?

What account owns the SQL Job? If it's SA, then the SQL agent account needs access. If it's a different account then that needs access.

Is there a different owner set up in the job step?

Whatever account applies needs access to all of the folders that may be related to this job/package

The SQL binaries, which is probably the Program Files directory.
The folder where the actual package is stored
Any folder that the package may read/write from or to
If the account running this is different than the service account, then that account will need to be added as a login to SQL and granted the proper access to the database(s)
If it is the service account running SQL, then the folders where the data, log, and backup files are stored.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 12

Sql services are using built in account so NT service\mssqlserver, nt service\sqlserveragent and nt service\msdtsserver140

Ssis package is being ran using the SQL agent account. The job owner was using a domain login but even when I changed this to the SQL agent account it still fails

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 13

Is there any additional logs generated when SSIS package would be running? I mean generic logs not error handling that would be added to the package itself

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 14

Getting this error now

Errorcode:6; ErrorDescription: the script returned a failure result ErrorCode:1; ErrorDescription: Exception has been thrown by the target of an invocation

This happens after generating a file that the package reads.

MarkP Old Hand Points: 305 More actions · Answer 15

This topic might be of use: https://www.sqlservercentral.com/forums/topic/exception-has-been-thrown-by-the-target-of-an-invocation-2