Hi All,
another infrequent Blog post here
.
I have had a problem with trying to get an SSIS package running as a Scheduled Job on SQL Server. Everything appeared well - even down to executing the package from within the SSIS on the Server - no errors (well - once the developers had sorted some issues out 
as per normal). But on scheduling the package I was receiving
Date 17/06/2009 16:58:08
Log Job History (testjob)
Step ID 1
Server Servername
Job Name testjob
Step Name test
Duration 00:00:02
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
Executed as user: INTERNAL\MSSQLService. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:58:08 Error: 2009-06-17 16:58:10.05 Code: 0xC0202009 Source: OA_DG5Admin_CreateEPIFILE_integration Connection manager "RELATEPI" 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: 2009-06-17 16:58:10.06 Code: 0xC020801C Source: Data Flow Task OLE DB Destination [5716] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "RELATEPI" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Error: 2... The package execution fa... The step failed.
I theorised that this could be down to an interoperability between the package and the 64-bit envoironment. The package was interfacing with flat files and an access database [:'(] and I remembered having an issue a while back on something simular (developing on the server) and trying to interface into an Excel spreadsheet.
I found patches of the solution - however there was nothing in-depth consisting mainly of Forum Blogs. So I thought I would post up here and hopefully help some other people if they have this problem.
First off - an overview
Scheduling an SSIS Package in a job on 64-bit SQL Server 2005, causes the 64-bit DTEXEC.EXE to be instantiated to execute the package - the parameters passed can be seen if you connect to SSIS in SSMS and Right click the package and select Run Package.
You will then be presented with the "Execute Package Utility" - select the last option - "Command Line" - this shows the parameters that will be passed to the a SQL Job used to run the package when selecting an SSIS Job Step - such as
/DTS "\MSDB\<PACKAGE NAME>" /SERVER <SERVERNAME> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V.
This information will be used later.
If you are running 64-bit - and if you are reading this I suppose you are - I would suggest going looking at your file system. You will find two SQL Server directories under you install location - one looks standard - the other has (x86) in the path. This is where ther 32-bit versions of SQL Server files are located - the other directory is the 64-bit and is the one defaulted to.
So - now to set up a job.
The secret to getting this working is when creating the job, is not to use the "SQL Server Integration Services Package" but to use the CMDEXEC Step Type.
It is here that you can force the SQL Agent to call the 32-bit version of DTEXEC.EXE.
To get the Executable path - just use the location of your DTEXEC within the SQL Server (x86) directories (usually \90\DTS\BINN\DTEXEC), and to get the parameters - just copy the parameters from the Command Line option of the Execute Package Utility (as shown above)
Oh - and one last bit of advice - be carefull what your mind types - I put DTSEXEC.EXE (note the S) and encountered another problem that had me banging my head for another 10 minutes 
Enjoy 