Importing and exporting between VB.NET and SQL Server 2000 -- sql server dts

Therese
5/30/2004 12:40:52 AM

Hi! I have current project wherein only certain fields in
the tables in SQL Server would be extracted and
transferred to a new Excel file. The thing is, data
extraction would be done through the front-end, Visual
Basic.NET form, and this would be performed by clicking
the Extract command button on the form. What I'm thinking
of doing is to make a stored procedure to import the Excel
file to SQL Server and then call the stored procedure
through VB.NET code. My question is, what would be the
proper syntax in importing the Excel file in SQL Server?
If not, can I use DTS for this and how is DTS used in this
manner?

In addition, once the imported data is in Excel format, it
is sent out for updating of data. Once udpated Excel file
is available, it would be sent back to us to update our
records. How can we export the Excel file back to SQL
Server so that we would not have to re-type the data? Can
we do this through a stored procedure or DTS and how?

I would very much appreciate it if anybody could help me
on this matter asap.

Thank you and looking forward for favorable reply.

Allan Mitchell
5/30/2004 9:22:09 AM

DTS can do this sure. Where it may get messy with DTS is the fact that it
sounds like you are going to be changing what gets exported each time. This
is where it can get messy with DTS as it likes things to be the way they
were previously. You can change the transformations but this will need to
be done in code.

So VB.Net will export attributes from a SQL Server entity to an XL
spreadsheet.

To import that Excel spreadsheet to SQL Server using TSQL I would look at
OPENROWSET

There are plenty of examples here

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&q=openrowset+Excel&btnG=Search

You can then use INNER JOINs on th Excel data with your SQL Server data.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

[quoted text, click to view]

sql server dts : Importing and exporting between VB.NET and SQL Server 2000