David Burrows (7/21/2014)

---

If the files were output by SSMS set to output to file then your...

Ummmm... Would it be too much trouble to post it again here just to make life easy on the people trying to help you? I just don't have the...

Jeff Moden (7/21/2014)

---

Ok... I took the time to find your BCP format file and the example text file. This is easy. All the columns need to be treated...

David Burrows (7/21/2014)

---

If the files were output by SSMS set to output to file then your...

J Livingston SQL (7/21/2014)

---

halifaxdal (7/21/2014)

---

yes the wizard is straight forward, I just did it again, if I simply chose Ragged right, there is only one column defined and executing the...

yes the wizard is straight forward, I just did it again, if I simply chose Ragged right, there is only one column defined and executing the package will error out,...

J Livingston SQL (7/18/2014)

---

halifaxdal (7/18/2014)

---

Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

I think the issue is the last column, like I...

Here is the format file, .xml is changed to .txt to get uploaded on this forum. Thanks.

I think the issue is the last column, like I indicated in previous post,...

ScottPletcher (7/18/2014)

---

I put a "\" (backslash) immediately followed by "n" in my previous comments, after the \r, but the site must remove it as some type of special editing character.

Try...

Now I think the issue happens in the last field which presumably is 20 characters but in the case the value is single digit it uses only 1 character, however,...

The format file is:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharFixed" LENGTH="12"/>

<FIELD ID="2" xsi:type="CharFixed" LENGTH="12"/>

<FIELD ID="3" xsi:type="CharFixed" LENGTH="17"/>

<FIELD ID="4" xsi:type="CharFixed" LENGTH="12"/>

...

I got some hints when I tried another file, it seems the first couple characters in the raw data file has some non-numeric character so the script throws error like:

Msg...

Thanks ScottPletcher, I removed and also removed the last line which shows some info like "totally xxx rows"

The script returns me error:

Msg 8114, Level 16, State 5, Line 1

Error...

ScottPletcher (7/17/2014)

---

Since it's from SSMS, it's likely just a standard text file .rpt, rather than a Crystal Reports .rpt.

I suggest using SSIS to import that data. It will of...

I should have mentioned in the OP that I can't ask for a different format at this moment, I need to get this done for this batch of data.

Thanks.