August 20, 2008 at 12:31 am
aharuray (8/19/2008)
My dtexec gives error as it says WEBSERVICE cannot run with this edition. But I have requested access to this folder where the excel files exist. May be it is a permission issue, thats why it works from one folder (for flat file) but not from another (excel file). I will update you as soon as I am able to test this.Thanks for the help on this.
Newbee - You can find options if you select Properties of Package (for 64 bit) or for Excel (for JET engine option).
Hmmm...some suggestions:
1. Definitely confirm that the SQL Agent that will be executing the package has permission to read the Excel file.
2. Confirm that the output of the Webservice is really a good Excel file. This might be a crazy notion, but I've seen some cases where a service (not necessarily a webservice) creates an XLS file from a database, but the exported file is really some old Excel format (like 2.0 or something) such that the Jet provider was of no use. We had to change our export service to save as either CSV or TXT or some old DBF because XLS was just not right. Not sure how you'd check this...maybe open the export in Excel, save/overwrite the file making sure the XLS file-type is something newer and try your SSIS package again? Or maybe try linking to the Excel file from Access using the Jet provider? Or create a UDL on your desktop and see if you can connect to the Excel file? Just some thoughts. The point is to confirm your Excel file is good.
3. See if the JET driver is even installed on the server by creating a DSN or something to the Excel file (on th server). If it works, examine the connection string of the DSN and see how it compares to the one being created in your SSIS package.
I'm really just grabbing at straws here...hope it helps.
August 26, 2008 at 2:43 pm
I am using EXCEL in 64bit.
I am trying to import Excel into sql 2005 table.
It runs well in SSIS.
I read the posts, I set the run64bit (or something like that) to FALSE and rebuild the Solution that contained my package.
(I created the Solution (Package) using FANNY\wgaw on this sql server box (that is 64 bit).
Then I created the job using cmdexec step and
: C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /f "F:\Fanny_MissingaDocs_Sln_Packages\FANNYMissing.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
The job owner I tried: FANNY\wgaw , SA FANNY\SQLService
They all gave me the same error as the ones you guys described.
Wonder why is still happening?
August 27, 2008 at 12:53 am
HKwai - Looks like you are going by the book so far. The only other thing I didn't catch is whether you confirmed that the SQL Agent's service account has access to the Excel file you are trying to access. If not, then your SSIS package will fail when scheduled through the SQL Agent.
August 28, 2008 at 7:40 am
Grasshopper, the excel file is on the same box as the sql server box.
The sql service agent account is a Domain\account that also has sa privilege and is a Domain Admin.
August 28, 2008 at 8:41 am
I finally solve my own problem luckily without calling Microsoft which would have charged an arm and a leg (It did took a while for me to solve this one!).
I just rebuild the SSIS solution from scratch .
The original SSIS solution was migrated (exported) from another server.
However, the new SSIS solution still uses the old DTSX package from the original solution.
There must be something that I migrated that the box didn't like.
Thanks all who tried to help me solve the problem.
September 2, 2008 at 9:50 pm
HKwai - Glad to hear. From what I understand, if you set your package protection level to any of the, "EncryptAllWithUserKey" or "EncryptSensitiveWithUserKey", you essentially have encrypted security info that is somehow (not sure how this works, but I can imagine) keyed with the login of the user logged-into the system at the time the package was built. By rebuilding it while logged in with a different UserID (doesn't matter where you build it as much as WHO builds it), then maybe you essentially re-encrypted it with a new set of credentials that solved your problem.
For more info, check out this TechNet write-up:
http://technet.microsoft.com/en-us/library/ms141747.aspx
If you get a chance, see what ProtectionLevel you have set on your SSIS package and try to recall if you built the package while logged-in with the same account that is being used by your SQL Server Agent. If YES to both, then it makes sense that rebuilding it would solve the problem. Let us know what you find....
September 19, 2008 at 12:27 pm
I haven't seen this article linked in from this post and it appears that 64-bit is a factor to some of the discussed concerns.
http://msdn.microsoft.com/en-us/library/ms141766.aspx
Key Excerpts:
The Run64BitRuntime project property applies only at design time.
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. (BTW...I don't see this option in SQL Server 2005)
When you run a package in 64-bit mode, you might not be able to connect to as many data sources as you can when you run a package in 32-bit mode. Some .NET Framework Data Providers and native OLE DB providers might not be available in 64-bit versions. For example, the Microsoft OLE DB Provider for Jet, which connects to Access databases and Excel spreadsheets, is not available in a 64-bit version.
September 19, 2008 at 1:44 pm
Additional Info:
The link I posted above was tailored to SQL Server 2008. There is a SQL Server 2005 version of the page.
http://msdn.microsoft.com/en-us/library/ms141766(SQL.90).aspx
I had a "Class Not Registered" error on a 64 bit install of SQL 2005 whereas I had to connect to a provider that was only available in 32 bit mode. I mentioned above how to execute the package in 32 bit mode for SQL 2008. The new link I found illustrates how to do it in SQL 2005. My issue was resolved and I am happy to put it to rest.
Excerpt:
If you want to run a package from a 64-bit SQL Server Agent job in 32-bit mode, select a job step type of Operating system, and enter a command line or use a batch file that invokes the 32-bit version of dtexec.exe. You can use the dtexecui.exe utility to create the command line, and then copy and paste the command line into the job step.
September 23, 2008 at 11:42 am
I am getting the same error only I can execute my package manually and it works fine but when I set it up in a job the job fails: Any ideas?????
Code: 0xC0202009 Source: CAW_Vendor_Update Connection manager "Excel Connection Manager" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-09-23 12:03:07.75 Code: 0xC020801C Source: Data Flow Task Excel Source [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error Er... The package execution fa... The step failed.:w00t:
Changinagain
September 24, 2008 at 7:10 am
BKW,
I'm not sure if you're running on a 64-bit server or not but that was pretty much the same behavior I experienced. In the links above, there is this quote:
When you develop and test commands by using the Execute Package Utility (dtexecui.exe) on a 64-bit computer, remember that this 32-bit tool is running packages in 32-bit mode. You should also test your commands in 64-bit mode by using the 64-bit version of dtexec.exe before you deploy or schedule them on a production server.
Basically...
Manually running a package = running in 32-bit mode.
Scheduling a job on a 64-bit server w/ 64-bit install of SQL = running in 64 bit mode.
My quick and dirty solution was to schedule the job as an Operating System job (not SSIS job), and point the job to the 32-bit version of dtexec.exe. I hope that helps. If not, sorry.
March 3, 2009 at 9:51 am
Chrispin,
Do you know if the same bugginess when creating an Excel connection manager exist in 2008 as well?
May 11, 2009 at 6:21 am
Hi,
I faced similar kind of problem.
It was because of Some files were missing from .NET 3.5. After installing .net and rebooting, the problem got resolved.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 20, 2009 at 11:07 pm
This is driving me absolutely crazy, spent the whole day trying to get my package to run through SQL Server Agent and still getting this damn "class not registered" problem in SQL 2005 SP3.
I tried this also:
D:\Program Files\Microsoft SQL Server (x86)\90\DTS\Binn\dtexec.exe" /sq CFMOnlyPackage /ser sqldev
within the SQL Server Agent under type operating system (cmdexec) and I get
"The process could not be created for step 1 of job 0x166F8B320CB66E418EDC56BCE79BB3BB (reason: The system cannot find the file specified). The step failed."
That's 100% the corret path too.
If I run the package directly through Integration Services by right-mouse clicking the package and clicking run package, it works fine. I've tried running it under a proxy but nope, can't get it to work.
Can anyone offer any suggestions please?
May 21, 2009 at 1:18 am
When you say you can run the package manually, do you run it under the same account as Agent or your own user?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
May 21, 2009 at 1:58 am
I've tried both yes, under my account, and under the account the agent service runs under and both work fine.
Viewing 15 posts - 16 through 30 (of 54 total)
You must be logged in to reply to this topic. Login to reply