Post reply

Issues executing a SSIS package using SQL Agent

  • September 9, 2015 at 3:46 am

    #304230

    Hi,

    I am new to SSIS and SQL agent so I may have missed something. i have on my local machine SQL Data tools for visual studio 2012 and i built an SSIS package (the PackageFormatVersion value in the XML is 6 confirming that the SQL version is 2012 as is the VS version) to transfer data from a local database (SQL 2008 R2) to a database on our server which is SQL 2014.

    I tested the SSIS package and it runs fine from within SQL Data Tools, but I now want to run this package using SQL Agent. My machine doesn't have SQL agent so I copied the package to the server and created a SQL agent job to run it

    When I then run the job I got the following error:

    09/09/2015 09:33:43,Bulk_Transfer,Error,1,UKFHPDBDPESQ01,Bulk_Transfer,Transfer,,Executed as user: GDC\ukfhpdbdpe01AGT. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2402.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 09:33:43 Error: 2015-09-09 09:33:43.86 Code: 0xC0011007 Source: {F2D144A2-9875-4FEE-BBF6-4C461DD91BAF} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2015-09-09 09:33:43.86 Code: 0xC0011002 Source: {F2D144A2-9875-4FEE-BBF6-4C461DD91BAF} Description: Failed to open package file "C:\Users\southwm\Documents\SQL Server Management Studio\BulkTransfer\BulkTransfer\Package.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This occurs when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of specifying an incorrect file

    I read some forums which suggested it might be a permissions issue on the SSIS package, and also checked the file path, but this was correct as well.

    I have uploaded screenshots from the SQL Agent config and the folder path for the package.

    Any suggestions would be welcome

  • September 9, 2015 at 3:59 am

    #1825347

    Since the package file is stored under your own user folder, I would say that it's very likely the SQL Server Agent service account doesn't have access to open it. I would recommend that you deploy all SSIS packages to a central location (somewhere under the SQL Server data folder is a good start) that the SQL Server Agent account has access to.

    John

  • September 9, 2015 at 4:20 am

    #1825351

    Thanks John, will try that

Viewing 3 posts - 1 through 3 (of 3 total)

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