none
SSIS package Not running on SQL Server Agent

    Question

  • Hi guys,

    Ok so basically heres the issue, I have a local instance of SQL server on my box i have created a SSIS package which gets data from a MySqlDB and from a SQL DB and then pumps that into a flat file and then emails the flat file, I am trying to execute it via sql server agent i am pointing to the dtsx on my desktop file system and have tried running it connected as sa and as windows credentials i get this error each time...

    Executed as user: Domain\PCName$. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  01:46:31 PM  Error: 2012-08-03 13:46:31.84     Code: 0xC0011007     Source: {5224565C-ECAC-4AE6-9261-504470DD69F8}      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: 2012-08-03 13:46:31.84     Code: 0xC0011002     Source: {5224565C-ECAC-4AE6-9261-504470DD69F8}      Description: Failed to open package file "C:\Users\UserName\Desktop\Package.dtsx" due to error 0x80070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.  End Error  Could not load package "C:\Users\UserName\Desktop\Package.dtsx" because of error 0xC0011002.  Description: Failed to open package file "C:\Users\UserName\Desktop\Package.dtsx" due to error 0x80070005 "Access is denied.".  This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.  Source: {5224565C-ECAC-4AE6-9261-504470DD69F8}  Started:  01:46:31 PM  Finished: 01:46:31 PM  Elapsed:  0.016 seconds.  The package could not be found.  The step failed.

    Any help please :(

    Friday, August 03, 2012 11:48 AM

Answers

  • Hi Guys,

    Thsi is a freshly installed box it never 2005 on it, i managed to solve the problem the problem was actually with the MySQL connection in the package for some reason it ran fine but when i tried to run it as a package it faield so i changed the ssis package property to not save sensitive data and then built in the connection string in the XML of the package and it runs fine now

    Thanks

    • Marked as answer by Curt GLR Tuesday, August 07, 2012 9:04 AM
    Tuesday, August 07, 2012 9:04 AM

All replies

  • Hi,

    When you schedule a JOB to run a SSIS package, agent starts that process as a Agent Service Account.

    But you can create proxy for the jobs as shown below to resolve this issue.

    http://www.mssqltips.com/sqlservertutorial/220/scheduling-ssis-packages-with-sql-server-agent/


    - Chintak (My Blog)

    Friday, August 03, 2012 12:16 PM
  • Hi Curt,

    This error is due to the multiple SSIS version installs. E.g. you have a SSIS 2005 and 2008 installed on the same machine, but you perhaps installed 2005 after 2008.

    As a result the DTEXec of 2005 version tried to open the SSIS 2008 package format.

    Anyways, the fix is described here: http://blogs.msdn.com/b/ramoji/archive/2008/12/24/package-migration-from-version-3-to-version-2-failed-with-error-0xc001700a-the-version-number-in-the-package-is-not-valid-the-version-number-cannot-be-greater-than-current-version-number.aspx

    And in short I suggest you go to the PATH environmental variable and edit it in such a way that "C:\Program Files\Microsoft SQL Server\100\DTS\Binn" path appears well before the "C:\Program Files\Microsoft SQL Server\90\DTS\Binn" path


    Arthur My Blog

    • Proposed as answer by Eileen Zhao Tuesday, August 07, 2012 9:00 AM
    Friday, August 03, 2012 12:45 PM
  • Hi Guys,

    Thsi is a freshly installed box it never 2005 on it, i managed to solve the problem the problem was actually with the MySQL connection in the package for some reason it ran fine but when i tried to run it as a package it faield so i changed the ssis package property to not save sensitive data and then built in the connection string in the XML of the package and it runs fine now

    Thanks

    • Marked as answer by Curt GLR Tuesday, August 07, 2012 9:04 AM
    Tuesday, August 07, 2012 9:04 AM