Importing a malformed flat file using DTS
If you ever had the pleasure of importing a large text file into SQL server using DTS Import and Export data, and had it fail on row 10.000, just to drop you back to the start again, you might find this a life saver.
You can get a DTS import to ignore malformed data using this process:
When using DTS import and export data, select "Save as DTS package". Run the task as usual and when it fails, open Enterprise manager, select your database, Select Data Transformation services > Local packages. Right click on the DTS package, and select "Design package"
Go to menu > package > Disconnected Edit then go to Tasks and click on the task that is failing due to errors. Located within this task properties is MaximumErrorCount and change that to a large number. Click the close button. Press package then execute to run the query
see: http://sqlservercentral.com/forums/shwmessage.aspx?forumid=92&messageid=173561 for more info…
