469,902 Members | 2,039 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

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

--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
  3. IF ERRORLEVEL == 1 GOTO BAD
  4.  
  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. )
  13.  
  14. REM Rename all the files with "old" as suffix
  15. ren c:\myimport\*.csv *.csvold
  16. GOTO END
  17.  
  18. :BAD
  19. ECHO Error
  20.  
  21. :END
  22. PAUSE
  23.  
Aug 9 '07 #1
0 1227

Post your reply

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

Similar topics

13 posts views Thread by raykyoto | last post: by
22 posts views Thread by Matthew Louden | last post: by
4 posts views Thread by HLCruz via AccessMonster.com | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.