Error 0xC0202009 while preparing to load the package

I have an SSIS project that is deployed to a sql server.

The setup of the project is a parent dtsx package, and many child packages.

The parent executes each of the chid packages.

If I run the parent from BIDS, calling the children that are on the SQL Server, it works fine.

If I execute it from SQL Management Console running on my laptop, connected to the SQL Server, It works.

If I remote into the SQL Server and run it from SQL Agent or SQL Management Console, the job fails with the error:

Error 0xC0202009 while preparing to load the package. SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x%1!8.8X!.

for each of the children.

This has quite suddenly stopped working, and I cannot figure out why it will work being executed from my laptop, but not on the server.

Any ideas?

Thanks

BobP

When you remote in, are you logging in with the same user/password that you use on your laptop? If not, it is probably a problem with security.|||

Yes I am. That's what is stumping me. Since I am running it from SQL Management Studio logged in as the same user, it should run from both machines.

Thanks

BobP

|||The difference is that for the first two scenarios you listed above, you are likely executing them from your machine. That is, dtexec.exe on your machine is executing the packages. When you remote in, you are using the dtexec.exe on the server. This means that things like network connectivity/firewalls/etc... come into play that might not be on your local machine.

What version of SQL Server do you have installed on your local workstation? Do you have the full SSIS client installed?|||

Hey Phil!

I didn't realize that if i run a sql package, logged into SSIS in SQL Management Console that it ran with the DTExec on my machine. Thanks for that bit of info.

However... Yes, I have the full SSIS installed on my machine, and the versions are:

Server 9.0.3042

Workstation: 9.0.3042

The irritating thing about this is that it started happening after it had been working for 2 months.

And as far as I know, nothing has changed on the server.

BobP

|||

BobP - BIM wrote:

However... Yes, I have the full SSIS installed on my machine, and the versions are:

Server 9.0.3042

Workstation: 9.0.3042

More specifically, Standard, Developer, or Enterprise versions? Also, which is installed on the server?|||

Sever is running enterprise 64 bit

Workstation is running developer.

Thanks!

|||

BobP - BIM wrote:

Sever is running enterprise 64 bit

Workstation is running developer.

Thanks!

Okay, so using this arrangement, when connected to Integration Services, through SSMS, you should be executing the copy on the server. That's my understanding.

Has this package run successfully before on a 64-bit machine? Do you have package logging turned on?|||

Nevermind... After a server re-boot, it is working fine now.

Thanks for all the help!

BobP

Error - Cannot open the datafile

Hi there,

I have written a program that loads a package (SomePackage.dtsx) from the physical drive and executes that. The package does nothing but imports data from a csv file to the Sql server 2005. But I can see that the package is failing continuously. I meant the package.Execute() method is returning a DTSExecResult.Failure. I investigated the Package.Errors property that contains the error collection and found that there are two DTSError objects into the collection.

The first one’s description says that

Cannot open the datafile "D:\SOME.csv".

And the later one’s is

component "SOURCE FLAT FILE COMPONENT" (1) failed the pre-execute phase and returned error code 0xC020200E.

But the most interesting thing is if I execute the package through the Execute package Utility (double clicking onto the SomePackage.dtsx file) ships with Sql server 2005 then it executes fine and works as expected. I have checked the permission of the csv file and it has everyone’s full access.

Can anyone help me on this?

I will appreciate all kind of suggestions.

Thanks

Moim

Hi,

"right click" the "flatfile connection manager"

click on "Edit". there are several properties here that needs to be configured

one of which is the "format".. make sure its "delimited" or something that suits your need.

if it didn't solve your problem try other properties

in this area.

regards,

joey

|||

What is different about when you use your tool vs dtexecui?

Is this the same machine?

Is this the same user running both tools?

|||

Darren,

Basically I can see the only difference is when I am running through the code it is failing and when I am running from the windows explorer it is working.

Nothing more. It is same machine, same user.

Any Idea?

Thanks

Moim

|||

joeydj,

Im afraid, I have created that package programmatically so I dont have a scope to edit that inside the Sql Server BI editor. anyway, I checked out and these properties are set.

Any Idea?

Thanks

Moim

|||Is your file on a mapped drive, by chance?|||

Brammer,

No its on a local drive (drive D: ).

Any other idea?

THanks

Moim

|||

Ok, so same package, same machine, same user, same execution parameters, just a different execution host... does not make much sense.

Try adding logging to the package, something simple like log all warnings and errors to a text file. Any other errors? There must be some difference but that error is not very clear as to what.