By To sum up,
I had to solve a situation in a client that wasn’t able to run a couple of SSIS package with SQLSERVER AGENT but they were running ok in BIDS.
Since SQL SERVER was running on a x64 environment, and the SSIS package was connecting to a excel files ( JET 4 OleDB ), it was throwing the following error :
DestinationConnectionExcel” 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: 2011-01-04 12:38:36.75 Code: 0xC00291EC Source: Preparation SQL Task Execute SQL Task Description: Failed to acquire connection “DestinationConnectionExcel”. Connection may not be configured correctly or you may not have the right permissions on this connection. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:38:35 Finished: 12:38:36 Elapsed: 0.765 seconds. The package execution failed. The step failed.
To solve, just create a simple process to call DTEXEC @ x86 DTS bin.
I had to make some “special t-sql” to be able to run the SSIS packages because there are no JET drivers for x64 ( connect to excel ). I’m sharing this with you,
-- Since this there is no Jet Driver for x64, processes must be run using x86 DTEXECDECLARE @dtexec_path nvarchar(100)
DECLARE @params nvarchar(150)
DECLARE @ssis_package varchar(100)
DECLARE @stmt nvarchar(350)
SET @dtexec_path='D:\mssql\sql2k5\"tools (x86)"\90\DTS\Binn\dtexec /SQL "\'
SET @params ='" /SERVER "DbInstanceName" /DECRYPT PasswordGoesHere /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E'
CREATE TABLE #report_to_execute ( report_path varchar(100) )
INSERT INTO #report_to_execute (report_path)
SELECT 'SSIS_PACKAGE_1' UNION ALL SELECT 'SSIS_PACKAGE_2' UNION ALL SELECT 'SSIS_PACKAGE_3'
DECLARE report_cursor CURSOR
FOR select report_path from #report_to_execute
OPEN report_cursor
FETCH NEXT FROM report_cursor into @ssis_package
WHILE @@fetch_status=0
BEGIN
SET @stmt = @dtexec_path + + @ssis_package + @params
EXEC xp_cmdshell @stmt
FETCH NEXT FROM report_cursor into @ssis_package
END
CLOSE report_cursor
DEALLOCATE report_cursor
Paulo's SQL Server Weblog and other bytes 
Or you could just use an Execute Process Task in an SSIS package to execute dtexec 32 bit and call the package in question.
LikeLike
Creative way also 🙂
LikeLiked by 1 person