How to automate importing 100 of tables?

Question

Post reply

How to automate importing 100 of tables?

Setiaman

SSC Enthusiast

Points: 113
More actions
April 25, 2006 at 4:54 am

#67361

I have the following text file to be import into sql server
Table1.txt imported into TABLE1
table2.txt imported into TABLE2
Tablen.txt imported into TABLEN
Can I use one package to imported all of these text file into sql server DB?
- Text file name is same as table name
- sequence of column are same as in sql server

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Pam Brisjar SSChampion Points: 12094 More actions · Answer 1

If it's a direct import with no translations of any kind needed, you're probably better off with a bulk copy.

See here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_2e5s.asp

for reference.

Setiaman SSC Enthusiast Points: 113 More actions · Answer 2

I can't use bcp or bulk insert. I faced the problem since some record of the table has the column with control character.

DTS can solve my problem but I need to import 400+ table. Just imagine how troublesome to create 400+ packages.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

Ouch.

Any chance you could use a parsing app prior to the BCP to take care of the control character?

Is the table structure exactly the same? If so, you could potentially create a single DTS and then create a loop where you manipulate the file and table names.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

A single DTS package can handle it. If it's a one-time thing, I'd use the Import/Export wizard. If you want to save a package and run it repeatedly, you're better off building it in DTS Designer. Use Fast Load and use multiple source and destination connections with 5-8 Transform Data tasks using each pair of connections.

Greg

Setiaman SSC Enthusiast Points: 113 More actions · Answer 5

I can change source and destination parameter. And I can make loop to execute dts but how to change the Transformation during run time. each time it run the source and destination is different and the transformation also map to different fields.

BTW, what is Fasload?

DanKennedy Hall of Fame Points: 3736 More actions · Answer 6

I would suggest either

a) going through the pain of creating the transformation for each file/table once and place them all in the same package so multiple imports can run at the same time

or

b) use ActiveX script to generate and issue bcp command lines e.g.

Dim i

Dim cmd

Dim oShell

Set oShell = CreateObject ("WScript.Shell")

For i = 1 To 100

cmd = "bcp dbo.Table" & i & " in C:\MyData\File" & i & ".txt -S ServerName -T -c"

oShell.Run cmd

This script assumes integrated security, tab delimited files in character format. Obviously you will have to specify your own parameters. There are also additional parameters for the Run method but you can easily discover and experiment with these.

Dan
www.firstcs.co.uk

Himanshu Saxena Grasshopper Points: 15 More actions · Answer 7

Hi,

You can create a DTS Package, using the dynamic properties task, and change the source text file and destination table, each time, the package is run. You just need to keep a track of the last file used counter 1,2,3 etc. and change the settings detailed above accordingly. And, to run it repeatedly, just schedule it to run, after every 2-3 seconds, So you need not do anything manual, after package creation.

Himanshu

Himanshu Saxena Grasshopper Points: 15 More actions · Answer 8

Hi,

You can create a DTS Package, using the dynamic properties task, and change the source text file and destination table, each time, the package is run. You just need to keep a track of the last file used counter 1,2,3 etc. and change the settings detailed above accordingly. And, to run it repeatedly, just schedule it to run, after every 2-3 seconds, So you need not do anything manual, after package creation.

Himanshu

Himanshu Saxena Grasshopper Points: 15 More actions · Answer 9

Hi,

You can create a DTS Package, using the dynamic properties task, and change the source text file and destination table, each time, the package is run. You just need to keep a track of the last file used counter 1,2,3 etc. and change the settings detailed above accordingly. And, to run it repeatedly, just schedule it to run, after every 2-3 seconds, So you need not do anything manual, after package creation.

Himanshu

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 10

Are any of the tables the same structure or are they all different? You can do this in one package either way, but if some have the same structure, you can loop through those tables (see http://www.sqldts.com for some examples of looping).

Otherwise, you're probably looking at defining a transform per file just because the mappings are different. You may have better luck if you can strip out the characters causing problems with the bulk insert/bcp - that would make for a pretty straightforward loop in DOS batches or other programming languages.

-Pete

Setiaman SSC Enthusiast Points: 113 More actions · Answer 11

Thanks for giving me hints and your oppinions.

Actually I was trying to Migrate data from ASA to SQL server. I though I need to export out all tables then do the DTS import, but actually I can direct import from ASA to SQL server. I use DTS import wizard to create a DTS package which containt the whole available tables. It has another benefit which I can set it to run the sub-set task concurrently. It turn out to be faster.

Setiaman