Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SSIS and Import/Export (2008)  BCP and BULK IMPORT Format File Problems

Author  Topic  aldo79 Starting Member 1 Post Posted - 2009-04-29 : 16:13:25 Hi, I'm a bit new to SSIS, BCP and bulk imports from flat files and have possibly bitten off more than I can chew with something I need to complete in work! Hoping someone can offer some advice please! I have several fairly large pipe-delimited text files from an old Oracle database which I'm trying to import into SS2008 Express Edition by any means possible. The data isn't exactly what I'd call clean so when it was initially exported from Oracle I asked for | delimited format to avoid any column overlaps (there are no " text qualifiers however). I'm trying to use a data format file with BCP and BULK INSERT however with BCP I keep getting "I/O error while reading BCP format file" and with BULK INSERT I get "Cannot bulk load. Invalid column number in the format file "D:\DSTORE....". I've checked for any spaces in the column names in the format file, that the delimiters are quoted with "\r\n" on the last line and that it's saved in ANSI format rather than unicode but to no avail. I can post the format file and a sample of data but as both are fairly large (92 columns) I thought I'd check if there is anything obvious I may be missing first.