asked on

Unable to run SSIS Package from SQL Agent

I have developed an SSIS package locally. SQL Server - Enterprise Edition

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727 SP2

Installed Edition: IDE Standard

Microsoft Visual Studio 2005 Tools for Applications 77633-163-9000041-41631
Microsoft Visual Studio 2005 Tools for Applications

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.4035.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.4035.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.4035.00

Protection Level: Don't Save Sensitive
Enabled Package Configurations (3 files used to set connection string to 2 target SQL DB and 1 MS Access source MDB)

Run64BitRuntime = FALSE

ACTIONS:

Execute package within BIDS - SUCCESS

Execute package locally using Execute Package Utility (configuration files included in the command line) - FAIL: DTS_E_PRODUCTLEVELTOLOW

Build package creating deployment utility

Deploy package to Agent Server
Exec package as step in Agent Job

FAIL:
Message
Executed as user: myUserName. ...rsion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:30:47 AM Error: 2010-06-17 08:30:51.22
Code: 0xC0202009
Source: MyPackage Connection manager "OLEDB.MDB - accessdb"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040154. An OLE DB record is available.
      Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154
      Description: "Class not registered". End Error
      Error: 2010-06-17 08:30:51.22
      Code: 0xC020801C
      Source: [Dataflow Task] [OLE source]
      Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OLEDB.MDB - accessDB" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. The package execution fa... The step failed.

I've tried a couple of different source options - including creating a simple package that includes a text file source into a SQL database and deployed the package - FAIL.

Reza Rad

did you set ProtectionLevel of your ssis package?
set it to rely on server storage, and deploy it. then it should run with DTEXEC

STHOMP02169

ASKER

As I mentioned in my question, the ProtectionLevel is set to Don't Save Sensitve - this is a business requirement.
However, for what it's worth, I did create a dummy package using the ProtectionLevel SaveSensitiveWithPassword and I still get the same error.

ASKER CERTIFIED SOLUTION

STHOMP02169

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial