Hi there, I've got a job that runs weekly, importing a text file into SQL Server using a DTS package. Everything runs through a web page, where I use ASP to call a stored procedure, pass in a filename, and then the stored procedure calls the DTS package and passes the filename as a global variable. To create the DTS package to begin with, I simply saved a file import task to SQL Server, and then added in the dynamic filename global variable.

Anyway, all of that is quite a bit of work, especially when the file structure changes I have to redo a lot of that work. So what I'm wondering is what other people have done to accomplish similiar tasks... looking for the 'best', most clean and efficient method. Thoughts?

Thanks, Patrick

i am too need a simple module to do that

Ahmed Ali
Software Developer

Why don't you just keep everything inside the DTS package? It has a VB Script task that allows you to execute the same code as you do in your ASP page. It also has RunSQL tasks to execute sprocs or plain SQL statements.

This way, you're keeping everything together in one tight package that is easy to create, schedule, move, update, etc.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I've been having trouble passing my global variables from the webpage into the DTS package, so that's why I've been calling them via the stored proc. Thanks for your thoughts Imar, what I was more concerned with on the import is whether I should be using a bulk insert, or a transform data task, ect. The text file is always 'supposed' to be in the exact same format, but they change it a little bit from time to time, and it's a pain to re-create (right now i'm using a transform data task).

(newbie to dts but I'll add my thoughts)
In a similar situation, I've broken up my tasks into a format-the-file task and an import task. The format-the-file sets up the file format from whatever comes in and outputs the file as a standard format and filename. The import then sweeps up without change.

In this scenario, you'd still have the format concern, but perhaps it's easier from another, simpler, dedicated task?

I'm curious, also, how you are able to use a dynamic filename.

I'd like to hear your final solution once you've cracked it. I, too, am working on something similar.

hi everyone..
I, like broniusm, am a newbie to DTS. BUt i was assigned to a job which was to write a program to take a filename, table, and a couple other parameters and insert that file into the database.
Problem is..they are supposed to be able to partially insert the file into the database (ie, only insert into the first two rows, insert row 3 in the file into row 2 of the database).

So naturally, i cannot do a simple BulkInsert 'pop-and-go'.
what i'm planning on doing...is doing the simple formatting of the input path (ie, remove extra spaces, quotes, ect), then searching the database...and find the destination table. After i find the table, i'm going to read in the format of the table, and dynamically create a format file. Then i'm going to look at what the user wants (ie, the input file row 2 in database column 3 stuff) and create a custom format file to send along with everything else to the DTS BulkInput Task object.

if anyone has a easier solution, i'd love to hear it.
oh, btw..i'm using Visual Basic6, and SQL Server 2k
thanks

Hi Patrick,
Do u have the ASP and SQL code for where you say in the opening post "where I use ASP to call a stored procedure, pass in a filename, and then the stored procedure calls the DTS package and passes the filename as a global variable" ?
Thanks,
Dave..

mobius-
I recently solved this one with a two-pass set of queries, all in a stored procedure. In my case, the flat data file containing many record types specified each given record's "record type" (destination table) with the values in the first couple columns.

I used a bulk insert into a single temp table, splitting the incoming file as "key fields" and "data". The keys were things like record type, count, and the like. Data was a "raw" 800 length varchar. The next step was to iteratively query-into new temp tables, based on a "where" on the record type. The end result here is that I've got a big, Raw, temp table (to be dropped) and a bunch of temp tables which can easily be queried and joined to in order to make actual database insertions.

An alternate solution also involves a two-step process, but I deemed the above method to be better for my needs and involving less irksome table column def's. This second method was to first set up all my empty temp tables, then import into that big raw table and finally, select * query against the Raw temp using joins on record type.

May be too late-- but hope this helps someone.