I have an export to Excel package created through SQL Server Import/Export
wizard. While it works by execute it manually on SQL Server\Integration
Services, it fails when schedule it to run as a job with the following errors
(see end of message).

Steps to reproduce:

1. Create an export to Excel package through SQL Server import/export
wizard, save it on SQL Server.
2. Run the package manually from SQL Server\Integration Services\Stored
Packages\MSDB (this should works)
3. Create a job to run the same package through SQL Server\Database
Engine\SQL Server Agent.
4. Run this job. It should fails. If enable logging through the package,
the following errors reveal.

#Fields:
event,computer,operator,source,sourceid,executioni d,starttime,endtime,datacode,databytes,message
PackageStart,SEADSQL02,BLINELLC\svc_sqldev,FliesMa in,{D1C66248-F559-460D-AA11-7B77D2BBD630},{E5C22E84-BB25-4B96-9802-B68FC02B0422},6/13/2006
3:43:33 PM,6/13/2006 3:43:33 PM,0,0x,Beginning of package execution.

OnError,SEADSQL02,BLINELLC\svc_sqldev,Message Monitor,,,6/13/2006 3:45:58
PM,6/13/2006 3:45:58 PM,-1071636471,0x,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".

OnError,SEADSQL02,BLINELLC\svc_sqldev,FliesMain,,, 6/13/2006 3:45:58
PM,6/13/2006 3:45:58 PM,-1071636471,0x,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".

OnError,SEADSQL02,BLINELLC\svc_sqldev,Message Monitor,,,6/13/2006 3:45:59
PM,6/13/2006 3:45:59 PM,-1073573396,0x,Failed to acquire connection
"DestinationConnectionExcel". Connection may not be configured correctly or
you may not have the right permissions on this connection.

OnError,SEADSQL02,BLINELLC\svc_sqldev,FliesMain,,, 6/13/2006 3:45:59
PM,6/13/2006 3:45:59 PM,-1073573396,0x,Failed to acquire connection
"DestinationConnectionExcel". Connection may not be configured correctly or
you may not have the right permissions on this connection.

PackageEnd,SEADSQL02,BLINELLC\svc_sqldev,FliesMain ,{D1C66248-F559-460D-AA11-7B77D2BBD630},{E5C22E84-BB25-4B96-9802-B68FC02B0422},6/13/2006
3:45:59 PM,6/13/2006 3:45:59 PM,1,0x,End of package execution.

PackageStart,SEAWTIMOTHYL1,BLINELLC\timothyl,Flies Main,{D1C66248-F559-460D-AA11-7B77D2BBD630},{996D04B8-6C8C-4129-B7C5-45FD2F0F7C06},6/14/2006
11:18:18 AM,6/14/2006 11:18:18 AM,0,0x,Beginning of package execution.

PackageStart,SEAWTIMOTHYL1,BLINELLC\timothyl,Flies Main,{D1C66248-F559-460D-AA11-7B77D2BBD630},{73B15646-C65C-4B02-B281-C0154123FD9C},6/14/2006
11:21:50 AM,6/14/2006 11:21:50 AM,0,0x,Beginning of package execution.

Please note I am running all this on SQL Server 64-bit environment. I
haven't test on 32-bit to see if I would have the same error.

Any help would be appreciative.

Thanks,
-TL

"Timothy Le" wrote:

I was able to get this to work in 32-bit environment, so it is a 64-bit
related issue.

Found this article:

Integration Services Considerations on 64-bit Computers
http://msdn2.microsoft.com/en-us/library/ms141766.aspx

Excerpts from article:

Limitations at Run Time

You may not be able to connect to as many data sources from a package
executing in 64-bit mode as you can from a package executing in 32-bit mode.
Some .NET Framework Data Providers and native OLE DB providers may not be
available in 64-bit versions. >>For example, the Microsoft OLE DB Provider
for Jet, which connects to Access databases and Excel spreadsheets, is not
available in a 64-bit version.<<

Selecting 32-bit or 64-bit Package Execution in a SQL Server Agent Job

When you configure a SQL Server Agent job with a step of type SSIS Package
Execution, >>the job always runs the package in 64-bit mode on a 64-bit
computer<<, because this step type invokes the 64-bit version of dtexec.exe
to run the package. (This assumes that you have installed and are running the
64-bit version of SQL Server 2005 and SQL Server Agent on the 64-bit
computer. If you have installed and are running the 32-bit version of SQL
Server 2005 and SQL Server Agent on the 64-bit computer, this step type
invokes the 32-bit version of dtexec.exe to run the package.)

So the fix is to schedule a job with a step typed "Operating System". For
some who doesn't know, the 32-bit version of DTExec.exe is typically locate
here by default:

C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

So I guess I fixed my own issue. Hope this will help others.

-TL