Impoting Multiple Flat Files to Multiple Tables

[dave32079]

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?
-Dave

--Batch call--

REM SOURCE:http://www.databasejournal.com/features/mssql/article.php/3325731
dir D:\TEST_Legacy\Testi\*.csv /b > D:\TEST_Legacy\Testi\Dirlist.txt
IF ERRORLEVEL == 1 GOTO BAD
 
REM Execute DTS package for every file name in the Dirlist.txt
for /f "tokens=1,2,3" %%i in (D:\TEST_Legacy\Testi\Dirlist.txt) do (
    REM Get table name
    for /F "tokens=1 delims=." %%A in ('echo %%i') do (
        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" 
        IF ERRORLEVEL == 1 GOTO BAD
    )
)
 
REM Rename all the files with "old" as suffix
ren c:\myimport\*.csv *.csvold
GOTO END
 
:BAD
ECHO Error
 
:END
PAUSE