472,805 Members | 834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,805 software developers and data experts.

Impoting Multiple Flat Files to Multiple Tables

I am moving data from Oracle to MS SQL 2000.

I have been given the Oracle table structures and about 130 CVS files to populate these structures, 1 file per table. I have converted the table structures to a MS SQL format and successfully created the tables but I am having problems creating the DTS package.

I have set the package with Global Variables that allow me to specify which file to send to the given tables (see batch call below). The table names match the file names.

I am running into problems specifying the transformation for the different columns in each table.

Is there something I can put in an ActiveX Script that would let me pull the column names out of the file being processed and specify these column names in my package? The flat files have a header row.

Is there a better way to do this?

--Batch call--
Expand|Select|Wrap|Line Numbers
  1. REM SOURCE:http://www.databasejournal.com/features/mssql/article.php/3325731
  2. dir D:\TEST_Legacy\Testi\*.csv /b > D:\TEST_Legacy\Testi\Dirlist.txt
  5. REM Execute DTS package for every file name in the Dirlist.txt
  6. for /f "tokens=1,2,3" %%i in (D:\TEST_Legacy\Testi\Dirlist.txt) do (
  7.     REM Get table name
  8.     for /F "tokens=1 delims=." %%A in ('echo %%i') do (
  9.         DTSRun /N "Import_Test" /V "{064D46BB-776D-4650-9EE8-CB42833B0648}" /F "D:\TEST_Legacy\Testi\Import_Mult_Tbl_TESTi.dts" /L "D:\MyImport\ImportError.txt" /A "Database":"8"="TESTi" /A "File":"8"="D:\TEST_Legacy\Testi\%%i" /A "Sever":"8"="TEST_SERV" /A "Table":"8"="[dbo].%%A" /W "-1" 
  10.         IF ERRORLEVEL == 1 GOTO BAD
  11.     )
  12. )
  14. REM Rename all the files with "old" as suffix
  15. ren c:\myimport\*.csv *.csvold
  16. GOTO END
  18. :BAD
  19. ECHO Error
  21. :END
  22. PAUSE
Aug 9 '07 #1
0 1339

Sign in to post your reply or Sign up for a free account.

Similar topics

by: raykyoto | last post by:
Hi all, I'm sure this is a popular question that comes up every few months here. Indeed, I've looked at some of the past postings, but I would like to ask things differently. Basically, I'm...
by: DB2 Novice | last post by:
I am trying to use DB2 Control Centre (version 8.2) to load one flat file into multiple tables. However, I don't see the options in Control Centre that allows that. Anyone knows how to do this?...
by: Matthew Louden | last post by:
I want to know why C# doesnt support multiple inheritance? But why we can inherit multiple interfaces instead? I know this is the rule, but I dont understand why. Can anyone give me some concrete...
by: Joe | last post by:
Hi I have a dataset with 2 tables and Relations What is the best way to flatten the 2 files to a new table or xml or file I can loop thru table1 and get the childrows or I can do an Xpath on...
by: HLCruz via AccessMonster.com | last post by:
I am working with a database that has client information separated in to 4 related tables - tFolder, tAddress, tEmail, tPhone number. In addition there are related tables tGifts and tCalls. The...
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
by: hkhella | last post by:
I receive several flat files daily that need to be improted into my Access Database. Each file record starts with a specific letter or group of letters, the following lines or rows in the flat file...
by: Pete | last post by:
I need to import multiple tab delimited files for which I do not have the formats. All files have column headers in the the line. The files must be dynamic. The only common value is that the...
by: Tim | last post by:
Hi Folks, I'm used to a UNLOAD command that allows me to dump to a named flat file the results of any SELECT statement. Hence one can build a single SQL file which contains multiple SQL...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.