Scheduled Job to run SSIS Package on SQL Server 2005

[Van T. Dinh]

Windows 2003 (Standard x64) SP2 named "W2003XXX". SSIS Server is therefore
named "W2003XXX".
SQL Server 2005 (Standard x64) Build 9.00.1399.06 named instance
"W2003XXX\SQL2005YYY". Waiting for I.T. Support Group to install SP3.

In SQL Server Business Intelligent Visual Studio, I created a fairly simple
SSIS Project/Package "CannedSpecs" deleting records from JET Tables and then
append records from SQL Server Tables to the now-empty JET Tables.

The SSIS package CannedSpecs.dtsx worked fine in Visual Studio after setting
the Project property "Run64Runtime" to False. If this Property is set to
True, I get errors:

Error: 0xC0202009 at CannedSpecs, Connection manager "Connection 2":
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".


I then saved the SSIS package on both the SSIS Server (as File System SSIS
Package) and also as SQL Server SSIS Package.

If I use DTExecUI, all 3 options (File/SSIS Package Store/ SQL Server)
succeeded.

If I use DTExec, all 3 options (File/SSIS Package Store/ SQL Server) failed
with the following commands:

DTExec /FILE "Full-path to CannedSpecs.dtsx"
DTExec /DTS "\File System\CannedSpecs" /SERVER W2003XXX
DTEXEC /SQL CannedSpecs /SERVER W2003\SQL2005YYY

If I create a SQL Server Agent scheduled job using both types "SQL Server
Integration Package" and "Operating System (CmdExec)", each with the 3
options as above, everyone of the 6 possible combinations failed.

All failures described above have errors similar to posted errors above
indicating that they all were run as if Run64Runtime = True.

Questions:

1. Is there anyway I can specify Run64Runtime = False in using DTExec or in
SQL Server Agent Job step?

2. Even if the general solution is not possible, is there anyway I can
create a SQL Server Agent Scheduled Job that can run the SSIS package using
1 of the 3 modes above?

Many thanks for any advices/suggestions offered.

[Van T. Dinh]

SQL Server has been updated to SP3 (Build 9.00.4035) but the results of the
repeated tests are exactly the same as previous post.

Any idea?

Thanks & cheers
--
Van T. Dinh

Post by Van T. Dinh
Windows 2003 (Standard x64) SP2 named "W2003XXX". SSIS Server is therefore
named "W2003XXX".
SQL Server 2005 (Standard x64) Build 9.00.1399.06 named instance
"W2003XXX\SQL2005YYY". Waiting for I.T. Support Group to install SP3.
In SQL Server Business Intelligent Visual Studio, I created a fairly
simple SSIS Project/Package "CannedSpecs" deleting records from JET Tables
and then append records from SQL Server Tables to the now-empty JET
Tables.
The SSIS package CannedSpecs.dtsx worked fine in Visual Studio after
setting the Project property "Run64Runtime" to False. If this Property is
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".
I then saved the SSIS package on both the SSIS Server (as File System SSIS
Package) and also as SQL Server SSIS Package.
If I use DTExecUI, all 3 options (File/SSIS Package Store/ SQL Server)
succeeded.
If I use DTExec, all 3 options (File/SSIS Package Store/ SQL Server)
DTExec /FILE "Full-path to CannedSpecs.dtsx"
DTExec /DTS "\File System\CannedSpecs" /SERVER W2003XXX
DTEXEC /SQL CannedSpecs /SERVER W2003\SQL2005YYY
If I create a SQL Server Agent scheduled job using both types "SQL Server
Integration Package" and "Operating System (CmdExec)", each with the 3
options as above, everyone of the 6 possible combinations failed.
All failures described above have errors similar to posted errors above
indicating that they all were run as if Run64Runtime = True.
1. Is there anyway I can specify Run64Runtime = False in using DTExec or
in SQL Server Agent Job step?
2. Even if the general solution is not possible, is there anyway I can
create a SQL Server Agent Scheduled Job that can run the SSIS package
using 1 of the 3 modes above?
Many thanks for any advices/suggestions offered.
--
Van T. Dinh

[Van T. Dinh]

For anyone who has similar problems, the solution is found in MSDN article:

Integration Services Considerations on 64-bit Computers
http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx
--
Van T. Dinh

Post by Van T. Dinh
SQL Server has been updated to SP3 (Build 9.00.4035) but the results of
the repeated tests are exactly the same as previous post.
Any idea?
Thanks & cheers
--
Van T. Dinh