Moving an SSIS .dtsx from one server to another

  • 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

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

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

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

  • 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:

    1. Right-click on the folder you want to give permissions to.
    2. Select "Properties" from the context menu.
    3. In the Properties window, select the "Security" tab.
    4. Click on the "Edit" button to open the permissions editor.
    5. Click on the "Add" button to add a new user or group.
    6. Type in the name of the user or group you want to give permissions to, then click "OK."
    7. In the permissions editor, select the user or group you just added.
    8. 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.

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

  • 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/

  • 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/

  • 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.

  • 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

     

     

     

     

     

     

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

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

  • 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/

  • 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

  • How are you executing the package?

    Have you tried running it from a SQL agent job?

  • 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.

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

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