SQL Agent can't run SSIS Package

Question

SQL Agent can't run SSIS Package

Phunhog

SSCommitted

Points: 1562
More actions
May 21, 2008 at 11:10 am

#154379

I have an SSIS package that imports a table from a MS Access db into a SQL server 2005 database. I can't seem to get my SQL agent service to run the package successfully. It was originally created in Business Intelligence Development Studio and saved to the local file system. I later saved the SSIS package directly into my named instance of SQL Server 2005 where the agent service is running.
The package works splendidly in BIDS. I can also run the package successfully via a DOS command or batch file using the DTexec command. Creating a Job to run the package was quite simple. Ive tried running it using the SQL Agent Service Account as well as a Proxy with with a sysadmin role. It always returns this error:
----------------------------------------------------------------------
Executed as user: DHMCMASTER\sql agent dmssurg2. ...0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:50:44 PM Error: 2008-05-21 12:50:44.79 Code: 0xC0011007 Source: {34C05164-EFF3-4C00-BDE6-28E75F073BB1} 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: 2008-05-21 12:50:44.79 Code: 0xC0011002 Source: {34C05164-EFF3-4C00-BDE6-28E75F073BB1} Description: Failed to open package file "D:\DMSSURG2K5_SSIS\CTSurg2K5_SSIS\CTSurg2K5_SSIS\CTSurgerySSISInboundRecords.dtsx" due to error 0x80070003 "The system cannot find the path specified.". 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... The package could not be f... The step failed.
--------------------------------------------------
I've recently enabled and attached and XML configuration file, but i still get the same error.
Ive read a few recent SSC forum threads detailing the exact same problem but still cant seem to get it to work. I'd really like to get a SQL Agent job working to handle this.
Thanks for any help in advance
jhh

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

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

It looks like you're using a config file, right?

When you run something from bids, it runs from your local workstation, and when you run it from the server, it runs from the server.

Did you copy the config file or any other supporting files to the exact same location on the server that they are on your workstation?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Phunhog SSCommitted Points: 1562 More actions · Answer 2

Ive been trying to use a config file.... but i'm way new to this SSIS.

thanks for the feedback

i'll give it a try and report back

jhh

Greg Charles SSC-Forever Points: 45403 More actions · Answer 3

I got this error when trying to execute a package stored in the file system and found that the SQL Agent account didn't have access to the folder where the package file was stored.

Greg

Phunhog SSCommitted Points: 1562 More actions · Answer 4

Thanks

I've granted permissions to the SQL Agent Account to access the source data folder.

I've also tried crever's suggestion.

Still no success.

Any other advice?

jhh

Greg Charles SSC-Forever Points: 45403 More actions · Answer 5

Is the source data folder where the package file is stored i.e. D:\DMSSURG2K5_SSIS\CTSurg2K5_SSIS\CTSurg2K5_SSIS\CTSurgerySSISInboundRecords.dtsx?

That's what the Agent login needs access to.

Greg

Phunhog SSCommitted Points: 1562 More actions · Answer 6

yes

I granted all those permissions with no success and have ruled that out.

This is my first experience with SSIS and have never 'deployed' a package and I suspect that is my oversight.

Any advice on that? and how to use an XML configuration file?.

jhh

Luc Magnée Say Hey Kid Points: 683 More actions · Answer 7

Hi John,

From your error description, I take you are running SQL Server 2005 x64.

An Access database is accessed using the Jet 4.0 OLE DB provider, which, unhappily enough has not been ported to 64bits (and if the latest news I've heard are correct, won't be...). This means you have to force the 32 bit execution of your package by running it in SQL Agent as a Operating system (CmdExec) step, where you start your command by specifying the path to the 32 bit version of DTExec, normally "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" followed by the string /SQL "\PackageFolder\PackageName" ...

If like me you have some problem remembering that lengthy last string, just create a standard SQL Server Integration Services Package, copy the string you find under the tab Command Line, and then change to Operating system (CmdExec) and paste this string after the path to the 32bit DTExec.

For more information about this subject, pls have a look at http://msdn.microsoft.com/en-us/library/ms141766.aspx "Integration Services Considerations on 64bit Computers"

I hope this will help you.

Luc Magnée
MCTS BI
Antwerp - Belgium

rubyengg1 Old Hand Points: 300 More actions · Answer 8

Hello,

I have mapped S drive to some server.I have kept the package to this mapped driver folder S:\Packages\Codes.But on running the job i am getting the following error. Any help on this...how to run the package stored on mapped drive.Mapped drive is on server 1 and i am running the job from my m/c in server1 itself

Message

Executed as user: HQDOMAIN\!svcSQLAdmin. ...0.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:55:06 AM Error: 2008-06-18 00:55:06.37 Code: 0xC0011007 Source: {C463116F-DAFD-4D69-9301-385FA2B1CC16} 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: 2008-06-18 00:55:06.37 Code: 0xC0011002 Source: {C463116F-DAFD-4D69-9301-385FA2B1CC16} Description: Failed to open package file "S:\Packages\Codes\Codes.dtsx" due to error 0x80070003 "The system cannot find the path specified.". 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. ... The package could not be f... The step failed.

Randy Doub SSCertifiable Points: 5322 More actions · Answer 9

Is this just a probelm from using mapped drive letters instead of fully qualified UNC designations?

mashadaG SSC Veteran Points: 269 More actions · Answer 10

Hi

I am having the same problem, not sure what I am doing wrong

Here is my error output, hope somebody can make heads and tails of? My package is trying to access data from an MS Access database. I have been reading stuff about 32-bit/64-bit from the forum, not sure whether that might be a solution???

Message

Executed as user: LEWISHAM\sqlagent. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:37:15 Error: 2008-10-03 11:37:19.08 Code: 0xC0202009 Source: pckgResTest Connection manager "SourceConnectionOLEDB" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "'I:\Planning & Performance-SH\Systems\Data\ProAchieve\RetentionData.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.". End Error Error: 2008-10-03 11:37:19.09 Code: 0xC020801C Source: Data Flow Task Source - export [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "SourceConnectionO... The package execution fa... The step failed.

paul.yearron Grasshopper Points: 14 More actions · Answer 11

Hi,

I am having the same problem with the job hanging on 32 bit SQL. I have created a CMD exec job as well as the original job to run the SSIS package but the job still hangs at 'Executing'

Any help greatly appreciated,

Thanks

Paul.

Phunhog SSCommitted Points: 1562 More actions · Answer 12

paul,

read further up the thread

SQL 2005 does not have the capability to use the MS Access jet database engine as a linked server.

You'll have to take some other approach

I don't use the SQL agent in 2005 to run SSIS jobs. I use a third party app.

good luck

jhh

David-1011936 Ten Centuries Points: 1378 More actions · Answer 13

Actually, SQL 2005 can use the Jet engine as a linked server. Under "Other Data Source", select "Microsoft Jet OLE DB Provider".

paul.yearron Grasshopper Points: 14 More actions · Answer 14

Hi,

Just to confirm the Job runs as an SSIS package and also as a batch file which calls the package,

"D:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\Test"

but not as a job in SQL agent. The agent hangs whenever it runs the SSIS job or the batch file by using CALL "d:\batch\test3.bat" where test3.bat is as above,

Cheers

Paul.