Question related to Data Import

Hi,

I have a scenario where I either have an option of writing a VB based import routine to import "FORMATTED" data in a text file and populate it in my underlying tables (SQL2K). However someone suggested that I should use DTS.

I am not very familiar with DTS and would therefore appreciate:

1. What would be a better solution - using the VB based import routine that I was planning to have in place or use a DTS?

2. What are the benefits of using a DTS?

3. How easy is it to define/develop a DTS?

4. Aee DTSs flexible?

5. Can someone recommend a URL from where I can read more about DTS and ideally with an example which I can follow to define/develop a DTS myself?

I'll appreciate your reply. Thanks.DTS is the way to go. DTS is very functional in transforming data. Read this link for more info:
http://www.databasejournal.com/features/mssql/article.php/3086891

and also look up DTS in Books Online, and the internet

Hope this helps|||You need access to enterprise manager (to make life easier)

sql server's client side tools...

But why not use BULK INSERT with a Format file? Or bcp

I've seen DTS do wierd things...but that was back in 7.

And if your VB routine was going to do singleton inserts, I would recommend against taht...

Holy Tranny log batman...|||Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.|||Originally posted by Joozh
Thanks for your replies. I'll try out the DTS option. I am not clear what you mean by "bcp".

I have one question (for now) related to the DTS option:

If I go with the DTS approach, can I define/develop (I do know what is the correct terminology) a DTS package and then "DEPLOY" at the the user's side so that everytime the user receives a text file with a similar format he can simply "RUN" the DTS package without requiring any help?

My second related question is that if I want to use the DTS deplyment option at the end-user's machine, would that require the Enterprise Manager to be installed at the end-user's machine or can he simply invoke it in some othr manner. I have a feeling (based on my limited knowledge) that the user would require the Enterprise Manager to be installed on his/her machine but I want to confirm.

As you might have figured out, I want to put in place a solution which I can "deploy" and then after a litle bit of training, let the user(s) import the data without my involvement.

I'll appreciate your feedback based on the above.

Thanks.

Once you create the DTS package, make sure it's portable, ie, it's can be used on virtually any computer. Read the Following article portability of DTS packages, it will give you some idea.

http://www.sqlservercentral.com/columnists/kfeit/portabledtspackages.asp

Secondly, once the DTS package is completed you can schedule a job for the package. This job can run on any server. So you can transform/format files or load the data from files to tables etc. So the user would just see the finished product of the DTS packages.

Hope this helps.