February 18, 2009 at 6:43 pm
Hi all.
I must admit I haven't read the whole 5 pages of this thread, so apologies if I'm repeating something someone else already said, but I'd be curious to know how many of you are running 64-bit SQL Server.
I had the same problem, and the answer was that there's a bug in the 64-bit dtexec.exe, which causes it not to run successfully from a job.
To get around this, you simply call the SSIS package from within your job from a command line that calls the 32-bit version of the dtexec.exe, rather than letting it default to the 64-bit one.
Your command line syntax will look something like this (note the path of the 32-bit exe, and replace the package name and server name with your own, unbracketed):
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\[SSIS Package name]" /SERVER [Server Name] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
In short, unless they've fixed this bug, which I'm not aware they have, if you can successfully run an SSIS package on its own, but it fails miserably, returning some pretty vague messages when trying to run from within a job, and your SQL Server is 64-bit, this will be your problem.
Hope this helps!
June 5, 2009 at 3:57 pm
can you tell me how this cmd would change if i have the ssis package stored in Integration services, ssis store?
June 8, 2009 at 2:34 am
sharon.bender (2/18/2009)
Hi all.I must admit I haven't read the whole 5 pages of this thread, so apologies if I'm repeating something someone else already said, but I'd be curious to know how many of you are running 64-bit SQL Server.
I had the same problem, and the answer was that there's a bug in the 64-bit dtexec.exe, which causes it not to run successfully from a job.
To get around this, you simply call the SSIS package from within your job from a command line that calls the 32-bit version of the dtexec.exe, rather than letting it default to the 64-bit one.
Your command line syntax will look something like this (note the path of the 32-bit exe, and replace the package name and server name with your own, unbracketed):
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /DTS "\MSDB\[SSIS Package name]" /SERVER [Server Name] /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E
In short, unless they've fixed this bug, which I'm not aware they have, if you can successfully run an SSIS package on its own, but it fails miserably, returning some pretty vague messages when trying to run from within a job, and your SQL Server is 64-bit, this will be your problem.
Hope this helps!
We are using virtually only 64 bit SQL Server, and have a few hundred SSIS pakcages being run from jobs. They are preominantly MSDB deployed. We invoke them via the built-in SQL Agent SSIS task, rather than using DTEXEC, and they work without problem. Just thought I'd add that ... DTEXEC is not the only way to run them..
June 8, 2009 at 5:53 pm
Yes Regan, if SSIS is not invoked directly from the job, then 64-bit probably doesn't crash.
Either that, or you're invoking the 32-bit .exe from the agent/task you call.
Of course there are many ways to do this.
However, my point was that if you want to call an SSIS package direct from a job, you need to use 32-bit SSIS, one way or another.
And as I found out last week, if you want to use SSIS to export to an excel spreadsheet, you also have to invoke the 32-bit exe, as 64-bit simply can't interface with Excel.
I created a package from DTS running a task from the Management Console. The export ran fine, the data was exported and an SSIS package based on this export was created. I then opened the newly created SSIS package in VS2005 so that I could make the save to filename dynamic and add a daily increment to it. When I ran it, it bombed out with weird error messages.
Adjusting the project to use 32-bit SSIS in Project Properties > Debugging > Run64BitRuntime = false quickly identified it was a 32-bit issue as well, as it runs fine when not running in 64-bit.
Just another handy tip if anyone's reading.
These 64-bit things can get pretty frustrating with all the odd messages they throw. It's not blatantly obvious this is the problem unless you've experienced it before.
Just as a bonus, Microsoft seems to be in denial, having written precious little to help people experiencing these bugs. It's 2005 for heavens sake, and they don't seem to have any fixes for these issues yet, which is pretty slack.
June 8, 2009 at 7:19 pm
Hi so you need to do both dtexec and change the properties of the ssis package?
June 9, 2009 at 1:17 am
It depends on what you're doing.
In the first case, I was creating a package that output CSV files. This SSIS Package did not fail to execute properly from the Visual Studio interface nor if just launching the package itself from SQL Management Console. It only failed when calling the package from a Job.
So while you could run it fine in every other instance, if launching it from a job, you have to call a command line explicitely using the 32-bit dtexec.exe, and then it will run fine.
In the second case, trying to output to Excel, you can create an SSIS Package as you are doing a simple export Right-click database > Tasks > Export from the SQL Management Console. When you execute the export from within the SQL Management Console (where it creates the package) it runs fine.
However, if you then bring that same package into Visual Studio and try to run the package, it will fail. It is at this point in Visual Studio, where you need to adjust the project properties to run 32-bit runtime if you want to see the package execute successfully from this interface.
Presumably (and I haven't tried it yet), in the second case with the Excel Export, you would also need to launch the package from a job using the 32-bit dtexec.exe in order for it not to crap out as it did in the Visual Studio environment.
So yes, in both cases, you would have to launch the 32-bit exe when launching form a job. Outputting CSV files from within Visual Studio works fine, while outputting to Excel fails. I am presuming here that this is because Excel is 32-bit? I would suspect there is a 64-bit version of excel available, but in this case, we don't have Excel installed on the given server at all, so I don't know if it makes a difference whether you have a 64-bit version of Excel at your disposal when trying to do this, but I doubt it.
Sorry, I hope that's a little clearer than mud, but that's the information I have managed to gather so far.
Basically, if a package fails and you are getting error messages that don't make a whole lot of sense, try executing as 32-bit, and it will probably resolve the problem.
June 23, 2009 at 4:45 pm
thank you so much for your clarification. 🙂
August 27, 2010 at 12:01 pm
I have read and tried every example provided in this post but have been unable to get my SSIS package to run – I’m really hoping there’s someone out there who can help …
I am running 64 bit SQL '08 sp1. I’ve created an SSIS package and have changed the run64Bitruntime to False (have to run as 32-bit). I have a batch file that 1-creates a backup of a flat file, then 2-kicks off the SSIS package. The backup gets made but the package never kicks off.
I can run the package directly in SSIS, as a job, and from a command line [DTSRUN packagename].
I have saved the package with EncryptSensitiveWithPassword and then imported it into File System. My latest try looks like this:
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /dts "\File System\myPackageName" /SERVER myServerName /DECRYPT myPassword /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT
I’ve tried using the DTEXECUI but I get errors with any of the following
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe"
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe"
(though I’ve looked and my dtexec.exe is in the 100 folder)
DTEXEC … etc.
My packages are at C:\Users\me\Documents\Visual Studio 2008\Projects\xxx\Package.dtsx. … if I wasn’t getting the error in the DTEXECUI that I am, I’d wonder if it was a permissions issue – I just don’t know anymore and having worked on this for days now I’m at wits end – any suggestions??
TIA!!!
August 27, 2010 at 2:32 pm
Thanks for letting us know what you tried - but it would be nice to know what the errors were when you tried those things. Can you post them?
Todd McDermid - SQL Server MVP, MCTS (SQL 08 BI), MCSD.Net
My Blog - Dimension Merge SCD Component for SSIS - SSIS Community Tasks and Components
January 20, 2015 at 3:53 pm
Thank you Laker. changing the Package protection level to Encryptsensitivewithuserkey and the package owner to my domain account helped me run the ssis package sql agent job successfully.The job was also running on my domain account 🙂
Viewing 10 posts - 46 through 54 (of 54 total)
You must be logged in to reply to this topic. Login to reply