July 22, 2011 at 9:37 am
Until recently, we were using the 64 bit version of dtexec.exe to run our SSIS packages from command line. Around the time that we upgraded to SQL 2008 SP2, the 64 bit executable started causing errors and wouldn't run. Changing the command to use the 32 bit executable seems to have fixed the problem, but I'm not sure what caused the problem in the first place - aka, I don't know what to tell the developers when they ask me why the commands have changed, ugh!
SQL 2008 SP2
Windows 2003 R2 Enterprise SP2 x64
Any ideas? Thanks in advance.
July 22, 2011 at 10:02 am
Details on what the errors are would be good. Were there any issues during the upgrade?
July 22, 2011 at 10:05 am
I'd bet it's a security issue.
What did you upgrade from?
Also If you could post the errors, that would be helpful.
Can you run the job from the agent?
July 22, 2011 at 10:12 am
Sorry, I guess the error message would be helpful. =)
Error: 2011-07-22 12:07:50.12
Code: 0x000002BE
Source: Data Flow Task [61]
Description: Failed to load OCI DLL.
End Error
Error: 2011-07-22 12:07:50.12
Code: 0x0000020F
Source: Data Flow Task [61]
Description: The AcquireConnection method call to the connection manager Orac
le Staging DB failed with error code 0x80004005. There may be error messages po
sted before this with more information on why the AcquireConnection method call
failed.
End Error
There were no issues from the upgrade. I upgraded from SP1 CU2 to SP2. Security-wise nothing has changed. If I switch to 32bit, the "Failed to load OCI DLL" error doesn't occur.
Thoughts?
July 22, 2011 at 10:42 am
I'm assuming everything has been running in 64bit since before the upgrade.
One thing you could try, I find this to be useful.
Run Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645 on the server, setup the filter to only look at dtexec.exe and set the result to Not Success. see screen shot attached.
then run the job in 64bit mode, review the results and look for things like Access Denied. This has saved me a lot of time in the past when troubleshooting odd errors.
July 22, 2011 at 10:45 am
Also you may want to filter out registry keys, it drasticaly cuts down on the number of records......
July 22, 2011 at 11:14 am
Alright, I know why the OCI error is occurring - because I changed the oracle connections to be 32bit.
Now, I get this error when trying to run in 64 bit.
Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_64BIT_ERROR. The request
ed OLE DB provider MSDAORA.1 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" H
result: 0x80040154 Description: "Class not registered".
End Error
I'm wondering if it isn't a problem with the SQL Server, but with Oracle.
July 22, 2011 at 12:02 pm
maybe a fresh install of the Oracle drivers would be in order (64 bit version)
July 22, 2011 at 3:14 pm
In your SSIS package, is there a step to export data to Excel? If yes, you are using 64 bit SSIS which does not support Excel driver. You need to set Run64BitRuntime to False from your solution properties and then in your SQL job step, you have to use Operating system (CmdExec) as the type and "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "your_file_name.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E if you are using file system.
--------------------------------------------------------------
DBA or SQL Programmer? Who Knows. :unsure:
July 25, 2011 at 6:59 am
BCC-493036 (7/22/2011)
In your SSIS package, is there a step to export data to Excel? If yes, you are using 64 bit SSIS which does not support Excel driver. You need to set Run64BitRuntime to False from your solution properties and then in your SQL job step, you have to use Operating system (CmdExec) as the type and "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "your_file_name.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E if you are using file system.
Nope, there is no step to export data to Excel. The package is grabbing data from Oracle and pushing it to SQL Server.
I've played with Run64BitRuntime property, but it doesn't make a difference whether it is set to true or false.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply