By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,633 Members | 1,753 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,633 IT Pros & Developers. It's quick & easy.

DTS Package using Excel Spreadsheets

P: n/a
I will start off by giving you a background of the process I am taking.
The nature of my DTS package is that I recieve an Excel Spreadsheet, run
it through the DTS Package applying validation to the records, create an
excel file that contians the records that do not pass the validation,
send this Excel file back to the person who sent me the original
spreadsheet. They will send the Excel Spreadsheet back once they correct
the records. Then the process starts over and keeps going until there
are no records left.

The problem I have ran into is that I have to create an excel file using
an ActiveX script, the DTSPump will not, as far as I am aware of, create
an Excel file automatically. So, I have created a ActiveX script that
will create an Excel object using Set
objExcel=CreateObject("OWC10.spreadsheet") . I do not add any data to
the spreadsheet at this point, just the column headings. A few steps
later in my DTS package I attempt to use a data pump and process
information from a table into the Excel worksheet that was just created.
However, when I choose the file and click on the OK button it throws an
erro "The External table is not in the expected format".

The problem lies in that I HAVE to use Excel files to complete this
DTS package, but the excel files that I create are not compatible with
the Excel 97-2000 connections in DTS. The object that I have created
using the ActiveX script shows as an Excel Spreadsheet in My Computer
and will open with Microsoft Excel. I have also tried to use the other
DTS Connect formats for Excel such as 5.0 and 6.0, these did not work
either.

Does anyone know how I can work around this problem? If I need to clear
things up or provide more information just let me know.

Thanks,
Jeff

* * * Sent via DevBuilder http://www.devbuilder.org * * *
Developer Resources for High End Developers.
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.