I have a problem, Can any one help me out ?

I am using VB as a client and SQL server 2000 as the database.The user will
select a .CSV file from the VB Client which may contain a max of 1,00,000
records. I want to upload this file into the temp table, do some
manipulations in the temp table, show the mismatched records alone to the
user using VB Client and once he confirms, copy these records into the
original table.

To my knowledge, this can be done in 3 ways

1. Read the .CSV file from the VB client itself, do some manipulation in the
VB client and upload to the original table.
2. Read the .CSV file from the VB client, do some manipulation in the SQL
server using stored procedures and update into the original table from the
stored procedure itself.
3. Using DTS wizard, upload the file into the temp table, do some
manipulation in the SQL server using stored procedures and update into the
original table from the stored procedure itself. From VB Client, I will have
to call the store procedure by passing the file name, which in turns calls
the DTS wizard and executes the same.

I think the 3rd option is the fastest of all.

But in the third option, the issue is I want to pass the file name from the
VB client to a stored procedure, and from a stored procedure I will have to
call the DTS wizard by passing the file name and execute the same. Can this
be done in SQL server ?

FYI - The format of the file will not change. It is fixed.

Thanks,

-Peri

Why not use the BULK INSERT task to do this in DTS. You can then pass in
the filename through the object model from VB
You then use TSQL to manipulate the temp table


--

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


"Peri" <peri (AT) cspl (DOT) com> wrote

I am not able to get you. Can you please explain in detail and the steps to
be followed.

What do you mean by Object model from VB ? Can you please explain this also
?

With best regards,

-Peri

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

DTS has an object model that you can manipulate for your package when you
call it.

How can I change the source data file
for a Bulk Insert Task
(http://www.sqldts.com/default.aspx?231)

In this example a Global Variable is assigned to the property of the text
file name. This can be populated using the Dynamic properties task or you
can simply convert this piece of VBScript into VB to run in your program.

--

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


"Peri" <peri (AT) cspl (DOT) com> wrote