I have a comma delimited text file with about 500,000 records. I want to insert data into an existing table. The catch is that I want to commit 10,000 rows at a time. Can this be done via dts. There also is about 20 records in this file that have an error. I also need need a routine that will ignore bad records.
Originally posted by rnealejr
Why 10,000 records at a time ? Have you looked at bcp ?
I do not have administrator access to the database. The log file is set to a limited size. The logfile will only let you committ a limited number of rows. I am not familiar with BCP. I am open to Any suggestions.
bcp can be difficult so I will step you through using bulk insert with DTS.
1. Open Enterprise Manager and select 'Data Transformation Services'.
2. Right click on local packages and select 'New Package'.
3. Under the 'Connection' toolbox select 'MS OLEDB Provider for Sql Server'. This will prompt for connection information - select the appropriate database.
4. Under the Task toolbox select 'Bulk Insert Task'.
5. Select the connection/destination table, source data file, format for the text file - for example, comma delimited ... You have other options but I will keep it simple.
6. Execute/save the package.
7. Thats it !
Let me know if you want an example - I will create one using Northwind so you can test it yourself.