Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server DTS
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server DTS Discussion specific to Data Transformation Service with SQL Server. General SQL Server discussions should use the general SQL Server forum. Readers of the book Professional SQL Server 2000 DTS with questions specific to that book should post in that book forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server DTS section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 3rd, 2004, 03:12 PM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default Best way to setup DTS text file import

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
  #2 (permalink)  
Old March 4th, 2004, 03:27 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: Cairo, , Egypt.
Posts: 336
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to alyeng2000
Default

i am too need a simple module to do that

Ahmed Ali
Software Developer
  #3 (permalink)  
Old March 4th, 2004, 03:36 AM
Imar's Avatar
Wrox Author
Points: 71,804, Level: 100
Points: 71,804, Level: 100 Points: 71,804, Level: 100 Points: 71,804, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,052
Thanks: 80
Thanked 1,581 Times in 1,558 Posts
Default

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.
  #4 (permalink)  
Old March 5th, 2004, 11:01 AM
Authorized User
 
Join Date: Jun 2003
Location: , , .
Posts: 29
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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).
  #5 (permalink)  
Old March 5th, 2004, 06:50 PM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #6 (permalink)  
Old April 16th, 2004, 09:37 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old September 20th, 2004, 05:26 AM
Registered User
 
Join Date: Sep 2004
Location: Dublin, , Ireland.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

  #8 (permalink)  
Old September 20th, 2004, 09:52 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mobius-
Quote:
quote:
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).
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.
 


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import from Excel sheet to a text file zone Excel VBA 1 July 26th, 2007 10:34 AM
How can i import a local text file into a jsp file ravibabu Java GUI 1 April 25th, 2007 04:05 AM
How to import data from text file? starnet SQL Server 2000 1 September 18th, 2006 06:56 AM
DTS Fixed Width File Import rochak SQL Server DTS 3 September 26th, 2005 09:12 AM
import text file to SQL Server DB via DTS pabloron SQL Server 2000 0 July 31st, 2003 06:41 PM



All times are GMT -4. The time now is 07:17 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.