Import huge data from text file (3G) with fixed length into a existing SQL table

[aspfun via SQLMonster.com]

I need to import huge data from text file (3G) with fixed length into a
existing SQL table.
Can someone give me a hint?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-dts/200912/1

[Todd C]

In SSIS, set up a Data Flow with the following:
A Flat File Source and an OLE DB Destination.
=====
Todd C

"aspfun via SQLMonster.com" wrote:

> .
>

[aspfun via SQLMonster.com]

Todd C wrote:
>In SSIS, set up a Data Flow with the following:
>A Flat File Source and an OLE DB Destination.
>=====
>Todd C
>

>> I need to import huge data from text file (3G) with fixed length into a
>> existing SQL table.
>> Can someone give me a hint?

Hi, Todd,
Thank you for help.
I tried to use SSIS but I am confused of how to select data source:
1) What format do I select? I select fixed width, is it ok?
2) Under data source panel, do I need to go through columns, advanced...
3) How to set row and column delimiter?

[Todd C]

Yeah, I kind of figured that first response would be just a little TOO
general, but given the original post . . ,

Drag a Flat File Source onto the Data Flow and double-click it.
In the Connection Manager drop-down, click the New button.
Set the name property and File Name as appropriate.
Set the Format as Fixed Width.
If you have a Header Row in the file, click the bos near the bottom for
Column names in first data row.
Click the Advanced page. This is where you set up your Columns and their
data types. Click the New button at the bottom. A list of properties will
come up on the right fir a olumn. Give the new column a name, an
InputColumnWidth, and an appropriate Data Type.
Repeat the New Column process until all your columns are set.
Click the Preview page to see what it would look like. If you have a
{CR}{LF} at the end of each row, you may want to add a 'dummy' column of
width 2 to account for it.
Go back and adjust InputColumnWidths until your preview looks right.

HTH. Good Luck.
=====
Todd C

"aspfun via SQLMonster.com" wrote:

> .
>

[aspfun via SQLMonster.com]

Todd C wrote:
>Yeah, I kind of figured that first response would be just a little TOO
>general, but given the original post . . ,
>
>Drag a Flat File Source onto the Data Flow and double-click it.
>In the Connection Manager drop-down, click the New button.
>Set the name property and File Name as appropriate.
>Set the Format as Fixed Width.
>If you have a Header Row in the file, click the bos near the bottom for
>Column names in first data row.
>Click the Advanced page. This is where you set up your Columns and their
>data types. Click the New button at the bottom. A list of properties will
>come up on the right fir a olumn. Give the new column a name, an
>InputColumnWidth, and an appropriate Data Type.
>Repeat the New Column process until all your columns are set.
>Click the Preview page to see what it would look like. If you have a
>{CR}{LF} at the end of each row, you may want to add a 'dummy' column of
>width 2 to account for it.
>Go back and adjust InputColumnWidths until your preview looks right.
>
>HTH. Good Luck.
>=====
>Todd C
>

>> >In SSIS, set up a Data Flow with the following:
>> >A Flat File Source and an OLE DB Destination.

>[quoted text clipped - 10 lines]

>> 2) Under data source panel, do I need to go through columns, advanced...
>> 3) How to set row and column delimiter?

Thank you so much.
I almost get there.
One more question:
How to set "Inputcolumnwidth" and "Outputcolumnwidth"?
For example, column0 width = 4 and column1 width = 10, I'll set

column0: "Inputcolumnwidth=4" and "Outputcolumnwidth=4"
column1: "Inputcolumnwidth=10" and "Outputcolumnwidth=10"

Am I right?

[aspfun via SQLMonster.com]

aspfun wrote:
>>Yeah, I kind of figured that first response would be just a little TOO
>>general, but given the original post . . ,

>[quoted text clipped - 24 lines]

>>> 2) Under data source panel, do I need to go through columns, advanced...
>>> 3) How to set row and column delimiter?
>
>Thank you so much.
>I almost get there.
>One more question:
>How to set "Inputcolumnwidth" and "Outputcolumnwidth"?
>For example, column0 width = 4 and column1 width = 10, I'll set
>
>column0: "Inputcolumnwidth=4" and "Outputcolumnwidth=4"
>column1: "Inputcolumnwidth=10" and "Outputcolumnwidth=10"
>
>Am I right?

I used the way above, it looks fine now.
But, in execution screen, there are 12 actions. the status for 11 actions are
"success", only "copying to " is "Stopped" without any error message. I open
the table, it is ok.
I do not understand why is is stopped.
It is imported from 3G txt file. Does SQL 2005 support it?
Help me to figure out the error.

--
Message posted via http://www.sqlmonster.com

[Todd C]

There's never an error without an error message. View the Output window from
the View menu (or Alt+ Ctrl + O). After your package executes and one of the
steps has failed, read the Output messages starting at the top. Look for
output messages starting with "ERROR"

What does the task do that is failing? What kind of task is it?

=====
Todd C

> But, in execution screen, there are 12 actions. the status for 11 actions are
> "success", only "copying to " is "Stopped" without any error message. I open
> the table, it is ok.
> I do not understand why is is stopped.
> It is imported from 3G txt file. Does SQL 2005 support it?
> Help me to figure out the error.
>
> --
> Message posted via http://www.sqlmonster.com
>

> .
>

[aspfun via SQLMonster.com]

For comparison test, I splited out txt file for 14 files and used Access to
import all 14 files into SQL.
Total row count are the same: 5276944 but there is big difference in data
space. Below is the list:
Using Access: 3,793,609 MB
Using SQL: 8,184,969 MB
I do not know why.