I am experiencing problems with data load from some of the excel files.
We get the excel files from a particular vendor everyday and some times
the DTS package fails. In DTS package, I am using "Microsoft Excel
97-2000" for the data source.

Whenever the package fails. I get the following error:

Step 'DTSStep_DTSDataPumpTask_2' failed
Step Error Source: Microsoft JET Database Engine
Step Error Description:Unexpected error from external database driver
(22).
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:5003000

I tried couple of things to point out the problem, one of them is:
I opened the excel file did save as "Excel 97-2002 & 5.0/95 Workbook"
(I have Excel 2002 SP1) and then when I tried running the package, it
worked fine.

But the problem is, since the dataload process is automatic and runs
everyday, we don't expect anyone to manually do this (saveas) everyday.
I am sure there is another way to handle this issue. Any help in this
regard is appreciated.

Thanks,
Yunus.

Yunus,

Since you've discovered the fix, why could't you include it into an ActiveX
step? It will then be automatic.

Ilya

"Yunus's Group" <yunusasmath (AT) gmail (DOT) com> wrote

Hello IIya,

Within the activeX step, I can definitely do save as. But I don't know
how I can do save as "Excel 97-2002 & 5.0/95 Workbook" format. If you
have the sample code to do this, please post it here.

Thanks,

Yunus,

Use SaveAs method:

ActiveWorkbook.SaveAs Filename:= _
"C:\Book1.xls", _
FileFormat:=xlExcel9795

I got it by recording a macro and then reading then reading the code it
generated.

Ilya

"Yunus's Group" <yunusasmath (AT) gmail (DOT) com> wrote