wrote:

>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.

"Allan Mitchell" wrote:

> 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@discussions.microsoft.com>
> wrote:
>
> >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.
>
>
> 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