Hi All,

 another infrequent Blog post hereBig Smile.

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 Tongue Tied 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 Big Smile 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 Embarrassed

 Enjoy Angel