Moving an SSIS .dtsx from one server to another

Question

Post reply

Moving an SSIS .dtsx from one server to another

juniorDBA13

Ten Centuries

Points: 1052
More actions
January 17, 2023 at 11:56 am

#4138533

I have a ,dtsx package stored in a folder and trying to move it from one to server to another. The SQL version is remaining the same it's just the OS version being updated.

I have setup the package to run as a SQL job from the File System as we don't have SSIs catalog or Db and this was the way it was running on the old server.
When I run the job I'm getting the below errors
Failed to complied scripts contained in the package. Open the package in SSIS Designer and resolve the compilation errors.
The directory is not empty (exception from HRESULT: 0X80070091
Access is denied (exception from HRESULT: 0X80070005)
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

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

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

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 1

Sounds like the user executing the SSIS package hasn't got permissions on a folder.

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 2

User is part of the local admin group of the server so should have all the access

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

I would make sure the user explicitly has permissions to the folder the package is in and any other folders used by the package.

To give permissions to a Windows folder, you can do the following:

Right-click on the folder you want to give permissions to.
Select "Properties" from the context menu.
In the Properties window, select the "Security" tab.
Click on the "Edit" button to open the permissions editor.
Click on the "Add" button to add a new user or group.
Type in the name of the user or group you want to give permissions to, then click "OK."
In the permissions editor, select the user or group you just added.
Check the boxes next to the permissions you want to give to the user or group.
The available permissions are:

Full Control
Modify
Read & Execute
List Folder Contents
Read
Write

Click "Apply" and "OK" to save the changes.

You will need to have administrative rights on the PC in order to make changes to the folder permissions.

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 4

I provided the account with explicit permission but still the same errors

Michael L John One Orange Chip Points: 27060 More actions · Answer 5

juniorDBA13 wrote:

User is part of the local admin group of the server so should have all the access

That is usually not a good idea.

And, are you sure the access is denied is from the folder you may be reading or writing to? You may want to grant full control to the folder where SQL is installed to.

As an example, on all of the SQL servers we manage, the service account has been granted full control of the data, log, tempdb, and backup drives, and is also granted full control on the folder where SQL is installed to. On my local machine, that is C:\Program Files\Microsoft SQL Server.

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/

Michael L John One Orange Chip Points: 27060 More actions · Answer 6

juniorDBA13 wrote:

User is part of the local admin group of the server so should have all the access

That is usually not a good idea.

And, are you sure the access is denied is from the folder you may be reading or writing to? You may want to grant full control to the folder where SQL is installed to.

As an example, on all of the SQL servers we manage, the service account has been granted full control of the data, log, tempdb, and backup drives, and is also granted full control on the folder where SQL is installed to. On my local machine, that is C:\Program Files\Microsoft SQL Server.

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/

Ed B Hall of Fame Points: 3669 More actions · Answer 7

Have you tried adding the package to an empty SSIS project and building any script tasks? If the package runs in debug then deploy to file system again.

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 8

I thought once the account is in the local admins group for the server it should have all the required access to SQL directories iswell

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 9

No, I presume when I move a package from one server to another it should work when the SQL version isn't changing

juniorDBA13 Ten Centuries Points: 1052 More actions · Answer 10

I have given the account all the access it should require but still getting the 3 errors

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

juniorDBA13 wrote:

I thought once the account is in the local admins group for the server it should have all the required access to SQL directories iswell

Not true.

Think about it. Your account may be a local admin on the server. There are folders where you get the popup saying you do not have access when you attempt to open them.

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

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

MarkP Old Hand Points: 305 More actions · Answer 13

How are you executing the package?

Have you tried running it from a SQL agent job?

Jonathan AC Roberts SSCoach Points: 18362 More actions · Answer 14

I've got some SSIS packages that I run from the file system.

To run them I have a batch file and use a Windows Scheduled Task to run batch file.

It might be worth trying this rather than getting a SQL job to run them.