Run SSIS Packages in SQL SERVER 2005 x64 with Jet4 OleDB connections (eg:excel)

2 comments

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 DTEXEC

DECLARE @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

Advertisements

2 comments on “Run SSIS Packages in SQL SERVER 2005 x64 with Jet4 OleDB connections (eg:excel)”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s