CODEWITHSUNDEEP
sql-serverstored-proceduresssis
Tamila
Tamila

Reputation: 177

Errors when executing SQL Server SSIS Package From Stored Procedure

I have an SSIS package stored in the file system, that imports Paradox (.dbf) files. The pacakge runs fine in BIDS, but errors out when I try to call it from the stored procedure.

I use the following syntax to call it from the sp

SET @packageString = 'dtexec /f [Packagename].dtsx  /Set \package.variables[ActivityDate].Value;"' + convert(VARCHAR(20),@dateTo, 101) + '"'
EXEC master..xp_cmdshell @packageString

This is the error I get:

Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered -- perhaps no 64-bit provider is available. Error code: 0x00000000. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

I know the problem is with dbf files, because when I disable that task in SSIS, the rest of the package executes fine. It looks like this package needs to run in 32-bit mode, but I can't figure out how to do it here. Help, please!

Upvotes: 0

Views: 6231

Answers (2)

Tamila
Tamila

Reputation: 177

Ok, I finally found the solution! Might not be the most elegant one, but it works. For some reason, the double quotes were getting in the way. So I got rid of them alltogehter:

SET @packageString = 'D:Progra~1\Micros~2\100\DTS\Binn\DTExec.exe /f [Packagename].dtsx /Set \package.variables[ActivityDate].Value;"' + convert(VARCHAR(20),@dateTo, 101) + '"'

Upvotes: 1

billinkc
billinkc

Reputation: 61211

There are two dtexec's available on 64bit machines. The windows Path variable has the 64bit Microsoft SQL Server listed first so you are invoking 64bit dtexec when you run it in the agent. When you execute it from within BIDS, you are always using the 32bit version.

Update your variable to explicitly use the 32bit version of the exe like (Edit based on comments in BOL one needs to double up all their double quotes)

SET @packageString = '""C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe"" /f [Packagename].dtsx  /Set \package.variables[ActivityDate].Value;"' + convert(VARCHAR(20),@dateTo, 101) + '"'

This assumes 2008 but if you had 2005 the above would replace the 100 with 90

Upvotes: 2

Related Questions