SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to efficiently import data in .rpt format into SQL 2008


How to efficiently import data in .rpt format into SQL 2008

Author
Message
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1744
Good day,

I have been told to import 100G data generated in SSMS and in .rpt format (10+ files) back to another SQL2008R2 server, I am wondering what would be the best practice to do this in a one time basis.

Thank you in advance, any suggestion is appreciated.
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1744
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.
Alvin Ramard
Alvin Ramard
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4105 Visits: 11639
I would have to see the file format before even thinking about options.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7800 Visits: 7142
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 course be a fairly long process to read, interpret and load 100GB of text data.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28032 Visits: 39925
if it is plain text, i'd just use bulk insert instead of building an SSIS package;
for me, that would be faster than anything else.

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7800 Visits: 7142
Lowell (7/17/2014)
if it is plain text, i'd just use bulk insert instead of building an SSIS package;
for me, that would be faster than anything else.


I'm thinking you're gonna have to do some serious manipulation on that .rpt file to load it to SQL tables, in which case I'd want the transformation capabilities of SSIS.

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1744
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 course be a fairly long process to read, interpret and load 100GB of text data.


It is plain text although the file extension is very misleading .rpt

I decide to take your approach and here is what I've done and what I am having issue:

raw data (test.csv):

326567 98 1026 11 35638468 3232282837 2259294439 2886995029 1 395 0
281578 9 1026 11 48210209 3232282839 3247785220 2886998653 1 119 0


Here is the format file:

<?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"
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="3" NAME="Field3" xsi:type="SQLINT" LENGTH="17"/>
<COLUMN SOURCE="4" NAME="Field4" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="5" NAME="Field5" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="6" NAME="Field6" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="7" NAME="Field7" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="8" NAME="Field8" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="9" NAME="Field9" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="10" NAME="Field10" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="11" NAME="Field11" xsi:type="SQLINT" LENGTH="20"/>
</ROW>
</BCPFORMAT>


Here is the table's schema:

CREATE TABLE [dbo].[LogDB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [int] NULL,
[Field2] [bigint] NULL,
[Field3] [bigint] NULL,
[Field4] [bigint] NULL,
[Field5] [bigint] NULL,
[Field6] [bigint] NULL,
[Field7] [bigint] NULL,
[Field8] [bigint] NULL,
[Field9] [bigint] NULL,
[Field10] [bigint] NULL,
[Field11] [bigint] NULL,
CONSTRAINT [PK_LogDB] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Here is my script:

BULK INSERT Ticket.dbo.LogDB
FROM 'C:\temp\test.csv'
WITH
(
FORMATFILE = 'C:\temp\format.xml'
);




It prompts me this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.



How do I fix it? Thank you so much for your help.

BTW: the original raw data file .rpt contains column names in its first line and separated line as the second line. Is there an option in Bulk input that can automatically generate the table based on the raw data file's name as well as the table's column names?
ScottPletcher
ScottPletcher
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7800 Visits: 7142
halifaxdal (7/17/2014)
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 course be a fairly long process to read, interpret and load 100GB of text data.


It is plain text although the file extension is very misleading .rpt

I decide to take your approach and here is what I've done and what I am having issue:

raw data (test.csv):

326567 98 1026 11 35638468 3232282837 2259294439 2886995029 1 395 0
281578 9 1026 11 48210209 3232282839 3247785220 2886998653 1 119 0


Here is the format file:

<?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"
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="3" NAME="Field3" xsi:type="SQLINT" LENGTH="17"/>
<COLUMN SOURCE="4" NAME="Field4" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="5" NAME="Field5" xsi:type="SQLINT" LENGTH="12"/>
<COLUMN SOURCE="6" NAME="Field6" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="7" NAME="Field7" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="8" NAME="Field8" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="9" NAME="Field9" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="10" NAME="Field10" xsi:type="SQLINT" LENGTH="21"/>
<COLUMN SOURCE="11" NAME="Field11" xsi:type="SQLINT" LENGTH="20"/>
</ROW>
</BCPFORMAT>


Here is the table's schema:

CREATE TABLE [dbo].[LogDB](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Field1] [int] NULL,
[Field2] [bigint] NULL,
[Field3] [bigint] NULL,
[Field4] [bigint] NULL,
[Field5] [bigint] NULL,
[Field6] [bigint] NULL,
[Field7] [bigint] NULL,
[Field8] [bigint] NULL,
[Field9] [bigint] NULL,
[Field10] [bigint] NULL,
[Field11] [bigint] NULL,
CONSTRAINT [PK_LogDB] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Here is my script:

BULK INSERT Ticket.dbo.LogDB
FROM 'C:\temp\test.csv'
WITH
(
FORMATFILE = 'C:\temp\format.xml'
);




It prompts me this error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.



How do I fix it? Thank you so much for your help.

BTW: the original raw data file .rpt contains column names in its first line and separated line as the second line. Is there an option in Bulk input that can automatically generate the table based on the raw data file's name as well as the table's column names?



I suspect the termination is \r rather than just \r. But also verify all the data lengths, just to be sure:
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\r"

SQL DBA,SQL Server MVP(07, 08, 09)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1744
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 converting data type varchar to bigint.

Where is the varchar from?
The raw data does have lots extra big numbers and I just cannot use varchar for those fields.
halifaxdal
halifaxdal
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1955 Visits: 1744
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 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '326567 ' to data type int.

Those three characters are EF BB EF, what are they for? the file is in ANSI/ASCII format.

I also tried to save the raw file in a different format UTF-8, same error.
If I save the file as Unicode, EF BB EF were removed, but then I got error:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

As I can inspect each character in hex code, I suspect that the hex 20 (which means blank space) is causing the issue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search