November 21, 2008 at 4:34 am
We are having difficulty with a Pervasive OLEDB connection on the 64 bit server (runs fine on 32 bit)
when I run the job using dtexec.exe I am getting a class not registered error on the pervasive OLEDB connection.
Did find something about choosing 32 bit execution option when setting up job step from Microsoft:
"To run a package in 32-bit mode from a 64-bit version of SQL Server Agent, select Use 32 bit runtime on the Execution options tab of the New Job Step dialog box."
Source: http://msdn.microsoft.com/en-us/library/ms141766.aspx
Where is this option? (standard edition) I don't see it.
The other option was to run using cmdexec and call the dtexec.exe, this does not work when running batch but the package does execute when I run it through the dtexecui.exe.
Any suggestions would be great. I will post my resolution in detail once I get this working.
November 21, 2008 at 4:53 am
The Pervasive OLEDB driver needs to be a 64 bit driver. Make sure it is and that the 64 bit driver is correctly installed. Also make sure your connection string is using the 64 bit provider string.
The option you are looking for (use 32 bit) is in the SQL 2008 job step dialog. Since you posted in the SQL 2005 forum, I will assume you are not using SQL 2008 yet. In SQL 2005, you have to use a CMD job step type and call the DTExec.exe command from the 32 bit program files folder.
November 21, 2008 at 5:17 am
I am now able to run the package from the command line using the dtexec.exe from the Program File(x86) directory.
Here is the command I used to launch the package:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\MyPackageNameWithoutFileExt" /SERVER "My64BitSQLServer" /CONFIGFILE "FilePathToConfigFile.dtsConfig"
The package was deployed using the SQL server option, not file system.
This worked great. Now I should be able to this same command and use the CmdExec option for job step type.
I will update this post with the results.
Michael, thanks so much for the quick response and solution.
November 21, 2008 at 5:39 am
Job still fails when running from SQL Agent.
The error is coming from the OLE Provider for pervasive.
"Pervasive.SQL V9 OLE DB Provider" Hresult: 0x80004005 Description: "-1305: No such table or object". End Error Error: 2008-11-21 06:23:23.07 Code: 0xC020801C
Error is deceiving because the table does exist.
I think this might be an authority issue because the Agent account has limited rights. Is there a way to execute under a different account. I created a proxy but the only option in the dropdown is to run as SQL Agent Account.
Is this a case where I should use the "EXECUTE AS" to change the excection context within the SSIS package?
November 21, 2008 at 6:02 am
Within SQL Server under Security you need to create credentials which you will then use when you setup the proxy under the SQL Server Agent for the SSIS package execution. When you have both of these setup properly then you will be able to select this in the step for the job to run the package as. The other thing to verify would be the protection level of the package, but it doesn't appear that you are having an issue with at, but something else to be aware of.
http://support.microsoft.com/default.aspx/kb/918760
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
November 21, 2008 at 6:08 am
You could verify it is a permissions issue by logging into the server using the SQL Agent account and trying to run the package through BIDS.
If it is, a proxy is the way to go. Make sure you create the proxy as an "Operating System (CmdExec)" proxy, not an SSIS proxy.
Also, if you are using package configurations, make sure your package configuration information is stored in the correct place. This is a deployment mistake lots of people make. The package configuration location is in relation to the server running the package (in this case, where the SQL Job Agent is running) and for the user running the package (either the job agent account, the job owner account, or the proxy).
November 21, 2008 at 8:17 am
I have it working now. Thanks so much for the help.
Here is a summary of what I did:
First deployed the SSIS package to the server using SQL Server storage
Usign DTEXECUI.exe I built the OS command to run the package.
Then added to the command the location of the 32 bit dtexec.exe so the command reads:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /SQL "\MySSISPackageNoFileExt" /SERVER "My64BitSQLServer" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Tested by running the command logged in as myself (administrator on sql and pervasive servers)
Package ran fine.
Setup a new credential (under sql server security) using my logon (temporary)
Ran the following script to create the proxy:
Use msdb
go
EXEC Sp_add_proxy @proxy_name='MyProxy', @credential_name='NameGivenToCredential'
go
EXEC Sp_grant_login_to_proxy @login_name='TheAccountUsedInCredential', @proxy_name='MyProxy'
go
EXEC Sp_grant_proxy_to_subsystem @proxy_name='MyProxy', @subsystem_name='cmdexec'
go
Setup the sql job step by selecting cmdexec as the step type and pasted the command from above.
Select from the Run As dropdown the proxy that was created.
Now just need to create an AD account with only the premissions needed to execute the job. and change the credential to use that account.
Thanks again
November 21, 2008 at 8:21 am
Nice. Good job and yeah I forgot that you needed to reference the 32 bit dtexec so using the cmdexec instead of SSIS execution was the way to go.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply