I have a tab delimeted file which i BCP into a temp table using BULK INSERT
When i use DTS Transform data task i get all 7000 records in the table
In DTS data transform i choose Column Delimeter is a tab ann row delimeter
is {CR}{LF}

When i do Bulk insert in using command below a lot of these records are
combined with previous records dud to improper row terminators on some records

How do i make this work in BULK INSERT same way as it works in DTS
BULK INSERT #stage_obligor_exposure
   FROM "k:\sqldb2\ermg\data\apr2005\exposures\test1.txt"
   WITH
      (
         FIRSTROW = 2,
    FIELDTERMINATOR = "\t",
        ROWTERMINATOR   = "\n"
      )

Pls help.............

One thing I noticed is that you use "\r\n" as your row terminator in
your DTS package but only "\n" in your BULK INSERT statement.  Perhaps
every row in your input file doesn't have a "\n" to terminate but only a
"\r" (from memory, although it's been a while so I could be wrong,
typically text files generated with Unix/Linux tools only use a "\r"
(ASCII 13) to terminate a line).  I don't know why \r\n would work in
the DTS package if a line only has a \r but maybe you could try
different combinations of row terminators for your BULK INSERT statement
(like "\n", "\r\n", "\r").

Show quoteHide quote

I have gotten this bulk insert statement to work and all you would have to do
is change the fieldterminator to '\t'.  Notice the file I was working with
was CSV and it had "," for field termination.  You can try using a FORMAT
file parameter but so far I have been unsuccessful in getting that to work. 
You can make a format file from the table using the following code:

EXEC master.dbo.xp_cmdshell 'BCP sqlsrv.dbo.SystemTemp FORMAT -Usa
-Ppassword -N -fc:\TBL_Format.fmt' 

Which will make you a format file of the table you are trying to bulk import
into.
--------------------------------------
BULK INSERT [dbo].[SystemTemp]
FROM 'c:\SystemTempIn.csv'
WITH
   (
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = '","',
    FIRSTROW        = 2,
    ROWTERMINATOR   = '"\n'
    )



Show quoteHide quote

Newbie: Help with csv Import and duplicates
ballpark time for shrinkfile
TEMPDB Size Too Large?
Performance Monitoring
tempdb size
Activating Server Logs
another xp_smtp_sendmail question
forced restore
database modes in sql server 7
Phisical and Logical Name