SSIS Pkg in SQL Agent Fails - 64 bit issue?
-
I am really stuck trying to get SSIS packages to run as steps in SQL Agent jobs. I've been through all of the BOL, etc. and currently configured such that I am creating the packages and SQL Agent is running under my login which has Admin rights on SQL and the servers involved - I am storing the packages with SERVER protection - I think this eliminates any security problems as being a factor.
I can run a job that does Native SQL to Native SQL connection as a job but any OLE DB connectivity fails (Oracle, MS Access, MS Excel) when the package is run as a job step. All of these packages run fine when executed in BIDS.
This is using Windows 2003 SP1 64-bit and SQL Server 2005 SP2 64-bit.
The consistent error I get is: 0x80040154
This points me here: http://support.microsoft.com/kb/932872/en-us and here: http://support.microsoft.com/kb/198891/en-us
I'm thinking it must be the first since I'm not doing anything outside of SQL Server. It says the workaround is to compile using a 64-bit compiler...how do I do that using SSIS??? I'm just bulding packages in BIDS - is there a way to change this operation??
Lastly, when I go into SQL Server Configuration Manager and check SQL Agent properties I see a parameter for "Running under 64 bit OS" with a YES or NO selection. This is set as NO which seems wrong (it IS running under a 64 bit OS) however when I change the parameter to YES it appears to save but everytime I bring it back up the option is still set to NO (I've tried changing it with SQL Agent stopped and does the same thing).
I'm wondering if there is something basically flawed in the install?? Has anyone else ran into this?? Any input would be hugely appreciated!!
-
For debugging you may get more useful info by remoting on to the SQL Server copying the command line from the Step in the Job into DTEXECUI and executing it.
If you post the execution results of this on the board, we may have some more ideas for you...
Kindest Regards,
Frank Bazan
-
Are you running any scripts in your SSIS Packages? We found we needed to precompile our packages before deploying them to run on our x64 systems (our development systems are x32). If you have any more information about your packages that may be relevant, that could also help.
-
Thanks for the responses!!
After spending all day on this....I know more about the problem which I think revolves around the 32-bit/64-bit issue.
I'm using a 32-bit workstation to run BIDS to develop these packages (pretty common I would expect). So when I execute a package with BIDS locally I am running 32-bit BIDS. When the job executes through SQL Agent on the server it is in 64-bit mode.
This became evident when I actually ran BIDS on the server itself and had different connections available (on the server versus my workstation) as well as the BIDS being slightly different.
In order to get the packages to run in BIDS on the server, I had to set the Run64BitRuntime option to FALSE in project properties. Otherwise they would be running as 64-bit and fail even in BIDS. SQL Agent on the server is trying to run the steps in 64-bit mode and failing - the Run64Bit parameter doesn't carry over to the package when it runs in SQL Agent.
The BOL artilcle 'Integration Services Considerations on 64-bit Computers' discusses this and indicates the 32-bit dtexec.exe has to be used in the job step to avoid this. John (laker_42) also mentioned this in another SSIS - SQL Agent thread. I am unclear as to how to build the command line - I can get it from dtexecui.exe but am not sure how I combine it to run with the 32-bit dtexec.exe...???
Does anyone have a simple command line sample?? (I'm command line challenged).
'Deployment' with precompiling was suggested - is that another way to get around this problem or does it just solve the issue that I find with running BIDS on my workstation versus running BIDS on the actual 64-bit platform?? We're basically a one server shop - I'm trying to keep the development/implementaton simple for this environment....
Thanks again,
Wyatt
-
OK, seem to have resolved most issues with the 32-bit command line dtexec use (thanks laker_42!).
My template for the command line is:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec command line from dtexecui(ie. /SQL "\PackageName".......) This is the default install location.
Or a full example:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec /SQL "\BasicPkg2" /SERVER svhpsql /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
The above pasted into the command box of an Operating System type job step...
Wyatt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply