64bit SSIS package calling 32bit SSIS package

[Mike]

Hi there, having some issues with the above scenario...

64bit sql 2005 std edition.

A control package is running in 64bit mode to execute a large number of
packages in the correct order (this is parameterised through a config table
so we can turn them on and off and change the order.) As we have a number of
packages using 32bit jet theres also a config value to execute these with the
32bit dtexec instead.

I have a proxy account setup with the required rights onto the network and
associated sql servers.

I can -
Execute the control package from the command line on the server as my proxy
account user with the 64bit dtexec and it runs fine calling both 64bit &
32bit packages with no errors.

I cannot run the same package from a sql agent job as whe nthe control
package calls the 32bit dtexec it comes back with the following error -

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
AcquireConnection method call to the connection manager
"AgencyNumbersDatabase" failed with error code 0xC0202009. There may be
error messages posted before this with more information on why the
AcquireConnection method call failed.


However if I schedule the offending 32bit package on its own from SQL Agent
it runs just fine.


Now to make matters more interesting if I change the proxy account to be in
the local admininstrators group on the server everything works. THis is the
bit I really dont understand, I cant see what extra rights it should need
running from sql agent as opposed to running from a command line.


Any idess appreciated,

Thanks, Mike.

[Mike]

Now tried 32bit end to end from sql agent (so running the control job in
32bit mode) and this works. However not a very good solution.

I cant find any known problems with 64bit SSIS executing a 32bit process
other than the well known ones regarding drivers etc.

Post by Mike
Hi there, having some issues with the above scenario...
64bit sql 2005 std edition.
A control package is running in 64bit mode to execute a large number of
packages in the correct order (this is parameterised through a config table
so we can turn them on and off and change the order.) As we have a number of
packages using 32bit jet theres also a config value to execute these with the
32bit dtexec instead.
I have a proxy account setup with the required rights onto the network and
associated sql servers.
I can -
Execute the control package from the command line on the server as my proxy
account user with the 64bit dtexec and it runs fine calling both 64bit &
32bit packages with no errors.
I cannot run the same package from a sql agent job as whe nthe control
package calls the 32bit dtexec it comes back with the following error -
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
AcquireConnection method call to the connection manager
"AgencyNumbersDatabase" failed with error code 0xC0202009. There may be
error messages posted before this with more information on why the
AcquireConnection method call failed.
However if I schedule the offending 32bit package on its own from SQL Agent
it runs just fine.
Now to make matters more interesting if I change the proxy account to be in
the local admininstrators group on the server everything works. THis is the
bit I really dont understand, I cant see what extra rights it should need
running from sql agent as opposed to running from a command line.
Any idess appreciated,
Thanks, Mike.

[Mike]

I got it working :)

Used filemon to see what was going on and discovered that my proxy account
was trying to use the temp folder that was owned by the account I run sql
agent under.

I've given it access to this folder and it now works. Very , very weird.

Post by Mike
Hi there, having some issues with the above scenario...
64bit sql 2005 std edition.
A control package is running in 64bit mode to execute a large number of
packages in the correct order (this is parameterised through a config table
so we can turn them on and off and change the order.) As we have a number of
packages using 32bit jet theres also a config value to execute these with the
32bit dtexec instead.
I have a proxy account setup with the required rights onto the network and
associated sql servers.
I can -
Execute the control package from the command line on the server as my proxy
account user with the 64bit dtexec and it runs fine calling both 64bit &
32bit packages with no errors.
I cannot run the same package from a sql agent job as whe nthe control
package calls the 32bit dtexec it comes back with the following error -
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The
AcquireConnection method call to the connection manager
"AgencyNumbersDatabase" failed with error code 0xC0202009. There may be
error messages posted before this with more information on why the
AcquireConnection method call failed.
However if I schedule the offending 32bit package on its own from SQL Agent
it runs just fine.
Now to make matters more interesting if I change the proxy account to be in
the local admininstrators group on the server everything works. THis is the
bit I really dont understand, I cant see what extra rights it should need
running from sql agent as opposed to running from a command line.
Any idess appreciated,
Thanks, Mike.