Executive summary:
How do you create a DTS connection in vb.net 2003 code to an Excel spread
sheet?

Background:
I'm creating an Excel to SQL dts package from scratch in code for an SQL
2000 server in vb.net 2005. (Don't ask - I'm a programmer. I write code.)

SQL Enterprise Manager makes this easy because it will save a package in vb
6. You have to modify the code as advertised in various place on the web to
get it to compile in vb.net (Never in a KB article as near as I can tell from
google hint, hint.)

One of the things you need to convert is something like:
FROM
' oConnection.Properties("Extended Properties") = "Excel
8.0;HDR=Yes"
TO
' oConnection.Properties.Item("Extended Properties").Value =
"Excel 8.0;HDR=Yes"

This compiles BUT you get a run time error that there is no such property!

MOREOVER, it is not at all obvious how to add properties to the connection
since no means appears to have been provided for it

In the absense of this line of code, DTS tries to open the excel spreadsheet
as an access database and of course fails.

HELP!

BTW: The work around is to create a access database with links to the excel
workshee pages as tables. THAT works wonderfully but is as inelegant as all
get out.

MS Lurkers if there is no easy work IN CODE around this definitely
represents a deficiency of the DTS model in VB.NET. If the work around
involves standing on your head at noon in the Roman Forum, well it still
represents a deficiency.

--
Regards,
Al Christoph
Senior Consultant and Proprietor
Three Bears Software, LLC
just right software @ just right prices @3bears.biz

Does Dounglas' article help any

Converting a DTS Package from Visual Basic 6.0 to Visual Basic .Net
(http://www.sqldts.com/default.aspx?264)

Allan

On Tue, 30 Aug 2005 01:53:04 -0700, Al Christoph <AlChristoph (AT) discussions (DOT) microsoft.com>
wrote:


Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know

That was the artidle that allowed me to get as far as i did:-))) However, it
does not address the problem of the "missing" properties. Thanks for taking
the time to document the article

The real ansower to this problem for me was to chuck DTS, create a odbc
connection to Excel, data sets for the table and use Fill to get the data and
bruite force to put it where I wanted it. (I gave up trying to make the
sqldataadaptor do an update and just wrote a loop to insert one row at a
time.)
--
Regards,
Al Christoph
Senior Consultant and Proprietor
Three Bears Software, LLC
just right software @ just right prices @3bears.biz


"Allan Mitchell" wrote: