April 22, 2008 at 11:10 am
SSIS is driving me crazy. I have a Server 2003 64 bit OS with SQL 2005 standard edition. It has all of its windows updates done on it.
I've built a package that grabs an excel document off of a file server. The file server does not have excel installed on it and neither does the sql server. If I remote into the server and execute the package just by clicking on the file it works fine.
If I schedule a job then I get this error:
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 10:54:26 AM
Progress: 2008-04-22 10:54:26.31
Source: Data Flow Task
Validating: 0% complete
End Progress
Error: 2008-04-22 10:54:26.52
Code: 0xC0202009
Source: ImportFringeHours 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-04-22 10:54:26.52
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
Error: 2008-04-22 10:54:26.52
Code: 0xC0047017
Source: Data Flow Task DTS.Pipeline
Description: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
End Error
Progress: 2008-04-22 10:54:26.52
Source: Data Flow Task
Validating: 33% complete
End Progress
Error: 2008-04-22 10:54:26.52
Code: 0xC004700C
Source: Data Flow Task DTS.Pipeline
Description: One or more component failed validation.
End Error
Error: 2008-04-22 10:54:26.52
Code: 0xC0024107
Source: Data Flow Task
Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 10:54:26 AM
Finished: 10:54:26 AM
Elapsed: 0.469 seconds
NULL
Any ideas?
April 22, 2008 at 11:52 am
Oh how I agree, the error is indicating you do not have a connection to the package. In your package in Visual Studio do you have an instance for Excel on the Connection manager section?
Another thing you may want to check. Where did you create the package? I have found that if you create the package on SQL tools that are not up to SP2 and try to push to SSIS Integration Services to a server with SP2 it will fail.
Tools, Server all have to be at the same Build number for it to work.
April 22, 2008 at 12:06 pm
The SP could be it, but I will assume you are using sp2 everywhere.
I would start with it being a permissions issue. Does the account executing the package (your SQL Agent service account if you have not configured a proxy) have access to the spreadsheet?
I am fairly certain that double-clicking on a dtsx file on a 64 bit server runs the 64 bit SSIS execution library by default, but it cannot hurt to make sure this is the case. If it is running the 32 bit under WOW when you execute it manually but running the 64 bit version from the SQL Agent, you may have a driver conflict (which is a common deployment problem with 64 bit servers and SSIS).
April 22, 2008 at 2:13 pm
I've done all of the windows updates and rebuilt the packages several times. Out of shear frustration with the jet driver issue I decided to skip it all and used csv files instead. Low and behold, no issues. Thanks everyone for your help.
June 17, 2008 at 4:16 am
I have another issue. When does an SSIS package give the below error:
Code: 0xC0024102
Source: Copy Reference Data Tables
Description: The Validate method on the task failed, and returned error code 0x80131501 (Failed to connect to server alias_tmx.). The Validate method must succeed and indicate the result using an "out" parameter.
And what are the common things that need to be checked and possible resolution for this ?
Details about my SSIS
1) Used to archive data from one database to another
2) Uses Config files to store the sql server details, credentials etc
June 18, 2008 at 6:45 am
I think the problem is that there is no 64 bit Jet driver and packages that need Jet have to run in the 32 bit WOW layer. I vaguely remember a blurb about needing to force the package execution to the 32 bit version in SQL Agent.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
June 18, 2008 at 8:21 am
I think you'll have to use command line in the scheduler and point to the x86 DTExec.
Here's a sample I found in another discussion group:
C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe /f "C:\PackagePath\PackageName.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
Here's the link:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=167907&SiteID=1
Hope that helps...
June 18, 2008 at 9:03 am
While saving the SSIs packages ,in the last combo box select the option rely on sql server storage and save it.
and then schedule the job it will run fine.
July 11, 2008 at 1:14 pm
I wanted to thank all for the insight on this and was able to resolve the error.
Here is a case where if you read too much into the error message you will never get to the root of the problem.
The real problem to this...drumroll.....the SSIS service on the server was being ran but a user (this case NT AUTHORITY/SYSTEM) that didn't have rights into SQL Server 2005 from which I am running the package from.
Once I switched the service to our domain user account, it validates and runs fine....
Always the little things!
July 7, 2009 at 4:06 am
I am using a 64 bit server and had the same issue out of the blue. Exactly the same package was failing with the same error message when I remotedesktop on the server and run it manually from Visual Studio. Resolved it by using the 32 bit executable on the Server.
Panos.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply