Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

How to efficiently import data in .rpt format into SQL 2008 Expand / Collapse
Author
Message
Posted Friday, July 18, 2014 11:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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"/>
<FIELD ID="5" xsi:type="CharFixed" LENGTH="12"/>
<FIELD ID="6" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="7" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="8" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="9" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="10" xsi:type="CharFixed" LENGTH="21"/>
<FIELD ID="11" xsi:type="CharFixed" LENGTH="20"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r" LENGTH="2">
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLCHAR" LENGTH="12"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLCHAR" LENGTH="12"/>
<COLUMN SOURCE="3" NAME="Field2" xsi:type="SQLCHAR" LENGTH="17"/>
<COLUMN SOURCE="4" NAME="Field2" xsi:type="SQLCHAR" LENGTH="12"/>
<COLUMN SOURCE="5" NAME="Field2" xsi:type="SQLCHAR" LENGTH="12"/>
<COLUMN SOURCE="6" NAME="Field2" xsi:type="SQLCHAR" LENGTH="21"/>
<COLUMN SOURCE="7" NAME="Field2" xsi:type="SQLCHAR" LENGTH="21"/>
<COLUMN SOURCE="8" NAME="Field2" xsi:type="SQLCHAR" LENGTH="21"/>
<COLUMN SOURCE="9" NAME="Field2" xsi:type="SQLCHAR" LENGTH="21"/>
<COLUMN SOURCE="10" NAME="Field2" xsi:type="SQLCHAR" LENGTH="21"/>
<COLUMN SOURCE="11" NAME="Field2" xsi:type="SQLCHAR" LENGTH="20"/>
</ROW>
</BCPFORMAT>

Why SQL throws error:
Msg 9436, Level 16, State 48, Line 1
XML parsing: line 16, character 9, end tag does not match start tag

It points to </RECORD> as the error location.

Thanks.
Post #1594163
Posted Friday, July 18, 2014 11:18 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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, there are cases that it has a large number as its value, like 1631571 instead of 0

That will automatically push the 0D 0A.

That also means the csv is not completely fix-width for all columns, there is an exception to be handled, either in bulk insert format or in SSIS design.

The SSIS design works fine until it hits the special line and screws up after that line.

Any idea for this?

I am attaching the csv here for easier to understand. I changed .csv to .txt so it can be uploaded.

Thanks to all replies.


  Post Attachments 
test.txt (9 views, 76.30 KB)
Post #1594166
Posted Friday, July 18, 2014 11:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 2,266, Visits: 3,419
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 setting the end terminator to:

TERMINATOR="\r\~~n"

but removing the ~~ first, so the \ and the n are directly next to each other.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1594168
Posted Friday, July 18, 2014 12:23 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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 setting the end terminator to:

TERMINATOR="\r\~~n"

but removing the ~~ first, so the \ and the n are directly next to each other.


I tried your reply ( removing the ~~):
  &lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r\~~n" LENGTH="2"&gt;

It still says
Msg 9436, Level 16, State 48, Line 1
XML parsing: line 16, character 9, end tag does not match start tag
Post #1594192
Posted Friday, July 18, 2014 12:59 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 2,266, Visits: 3,419
halifaxdal (7/18/2014)
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 setting the end terminator to:

TERMINATOR="\r\~~n"

but removing the ~~ first, so the \ and the n are directly next to each other.


I tried your reply ( removing the ~~):
  &lt;FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r\~~n" LENGTH="2"&gt;

It still says
Msg 9436, Level 16, State 48, Line 1
XML parsing: line 16, character 9, end tag does not match start tag


REMOVE THE ~~ FROM THE STRING.


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1594204
Posted Friday, July 18, 2014 1:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.


  Post Attachments 
format.txt (12 views, 1.59 KB)
Post #1594207
Posted Friday, July 18, 2014 1:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 1,932, Visits: 19,934
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 indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.


hi Halifax...I am no bcp expert...far from it.
however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

regards


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1594212
Posted Sunday, July 20, 2014 11:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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 indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.


hi Halifax...I am no bcp expert...far from it.
however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

regards


Thanks for letting me know, did you get all 11 fields? can you provide the detail of each step here?
Post #1594408
Posted Monday, July 21, 2014 12:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:22 PM
Points: 1,932, Visits: 19,934
halifaxdal (7/20/2014)
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 indicated in previous post, the last column is NOT a width fixed column, in some cases it is 0, in some cases it is a big number

BTW: I have posted both my test data and format file here in this thread, everyone can you please try them out on your environment, thank you very much.


hi Halifax...I am no bcp expert...far from it.
however, and for what its worth....I could import your test data using SSIS import wizard with ragged right format on the flatfile data source.

regards


Thanks for letting me know, did you get all 11 fields? can you provide the detail of each step here?


yeah...all eleven columns.
not sure what you are asking for on detail of each step......the wizard is straightforward.....have you tried the wizard yourself?


______________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Post #1594451
Posted Monday, July 21, 2014 6:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:38 PM
Points: 827, Visits: 1,477
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, that's why I asked if you get 11 columns and if you can tell me more details, thanks.


- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task 1: The "output column "Column 0" (10)" failed because truncation occurred, and the truncation row disposition on "output column "Column 0" (10)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Rxie\Weblog\test.txt" on data row 1.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - test_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Post #1594556
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse