Calling DTS from ASP vs. VB - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Calling DTS from ASP vs. VB

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-19 : 09:49:34

Justin writes "I've got an extremely simple DTS package which parses a CSV file and inserts into a given table.

Why I'm choosing DTS for this:
In the past I was using BULK INSERT to parse fixed-width files, but with CSV and wrapped text fields I ran into problems. The BULK INSERT fails on this file because some of the text fields contain actual commas.
For example:

1, 3, "1966-09-09", "Carter, Rubin, Hurricane", "Canada"
...

The problem is obvious. I couldn't find a way to use my trusty BULK INSERT task and successfully parse this file type.

DTS has no problem with this file, parses very fast.
It only takes a few lines of code to execute the DTS from VB. I have two global variables to initialize: the data source (file) and the destination table (created on-the-fly before calling the DTS).
The first step in the DTS is a "Dynamic Properties" task which grabs the globals and assigns them to their proper location.
Second step is the transform, and we're through.

Calling the DTS from VB works perfectly, but once I compile my VB code into a DLL and call the methods from ASP, it fails.
It seems the precedence constraints are lost, and DTS attempts to execute the steps in reverse order.

I searched and searched for information, but nothing I found mentioned this issue with precedence constraints.
I just read your little article at 4Guys on why you don't like DTS and thought maybe you could give advice on how to accomplish my task without DTS (I must be missing something with BULK INSERT).

I posted for help at SQLMag.com a few weeks ago, but got no help.

Using SQL Server 2000, VB6 and classic ASP.
Here's a code snippet of the failing ASP code:

dim dts, oStep
Set dts = server.CreateObject("DTS.Package")
dts.LoadFromSQLServer "localhost", "tpausa", "password", DTSSQLStgFlag_Default,"","","", "Import CHP MediCal File"

For Each oStep In dts.Steps
    oStep.ExecuteInMainThread = True
Next

dts.GlobalVariables("gv_DataSource") = FileName
dts.GlobalVariables("gv_DataDestination") = parser.TableName
dts.Execute

For Each oStep In dts.Steps
    Response.Write oStep.Name & ": " & oStep.ExecutionResult & "<br />"
Next

The same exact code works fine from an *all* VB app, not when compiled into a DLL and not when copy/pasted into ASP.

Sorry this is long, I wanted to provide as much detail as possible."

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-19 : 11:04:36

Here is how to handle quoted fields with bulk insert
http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

But you shouldn't be seeing the problem you are with dts.
Try adding an activex script at the begining of the package to do the work and dtsrun to set global variables.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources