[groan] Heeeellllpppp... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

[groan] Heeeellllpppp...

Author

Topic

AlexCold
Starting Member

39 Posts

Posted - 2002-05-20 : 21:02:10

Hi, All!
I have txt file with around 3'000'000 records. I need to pull it into the database. Not a problem.

I have created DTS package (first one in my practice

) and started to test it.
First problem is that it seems that I can't insert more then 2'522'000 records one time. Sorry, I don't recall exact error message, but I will if you need it.
I decided to split data transformation into two stages: first one will transform 2'500'000 of records and secord one will the rest of records. Here was an ambush too. And error message is really strange

WHY it trys to insert NULL value??? "Enable identity insert" *is* checked.

Any ideas?

FAQTeam member
http://www.faqteam.org/

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-05-20 : 21:26:02

Alex,

For a start, why are using a Transform Task instead of a BulkInsert!

Just get the data into SQL and then manipulate the data there as a set instead of a row by row transform.

The "Enable identity insert" means you have to SUPPLY the ID.

DavidM

"SQL-3 is an abomination.."

AlexCold
Starting Member

39 Posts

Posted - 2002-05-21 : 05:32:34

quote:

For a start, why are using a Transform Task instead of a BulkInsert!
Just get the data into SQL and then manipulate the data there as a set instead of a row by row transform.

Because in "BulkInsert" I must to pull *all* data from the text file, but I need only 4 columns from 29 supplied.

quote:

The "Enable identity insert" means you have to SUPPLY the ID.

I know it! All is fine during transformation, problem occur during insertion of last row!

--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

Edited by - AlexCold on 05/21/2002 05:33:10

Edited by - AlexCold on 05/21/2002 05:33:46

Edited by - AlexCold on 05/21/2002 05:34:25

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-21 : 09:15:52

maybe there is an extra row terminator (carrige return/line feed) at after the last row of data?

<O>

AlexCold
Starting Member

39 Posts

Posted - 2002-05-21 : 09:59:41

It is! But transformation fails 200'000 records early...

--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-05-21 : 10:45:23

quote:
Because in "BulkInsert" I must to pull *all* data from the text file, but I need only 4 columns from 29 supplied.

This is NOT true. You can specify the columns you want to import by using a FORMAT file. Here is one I use:


8.0
6
1       SQLCHAR       0       20      ","       2     HEX_GMT_Time_Stamp
2       SQLCHAR       0       15      ","       3     Loopback_IP       
3       SQLCHAR       0       50      ","       4     OID_ID            
4       SQLCHAR       0       0       ","       0     OID_Sub_ID        
5       SQLCHAR       0       0       ","       0     OID_Counter       
6       SQLCHAR       0       255     "\n"      5     OID_Value

As you can see, my text file has 6 columns, but I instruct BULK INSERT to ignore columns 4 (OID_Sub_ID) and 5 (OID_Counter).

Also, my database table has FIVE columns. Column 1 is a datatime field which defaults to GetDate(). Thus, I put text file columns 1, 2, and 3 in db fields 2, 3, and 4 and the 6th text file column into database field 5.

leeholden
Starting Member

34 Posts

Posted - 2002-05-21 : 11:10:28

1)If you CHECK Identity Insert, it thinks that you are trying to give it an identity value.
2)If you UNCHECK Identity Insert, it will create the identity for you.

I assume that you are ignoring the Identity column on import, if so, use option 2.

leeholden
Starting Member

34 Posts

Posted - 2002-05-21 : 11:17:11

Data transformation task always fails on the last row when importing text files (I think it does on everything else as well), no matter where it really fails. put an exception log on the transformation.

--------------------------------------------------
Ban all NULLs and employ Cowboy Coders

AlexCold
Starting Member

39 Posts

Posted - 2002-05-21 : 11:51:18

Where can I find syntax of format files?

--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

AlexCold
Starting Member

39 Posts

Posted - 2002-05-21 : 11:56:54

quote:

1)If you CHECK Identity Insert, it thinks that you are trying to give it an identity value.
2)If you UNCHECK Identity Insert, it will create the identity for you.

I assume that you are ignoring the Identity column on import, if so, use option 2.

Are you sure about it? I'm not kidding. I thinked that it is vice versa...

--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-05-21 : 14:00:16

BULK INSERT
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

BCP Utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

Using Format Files
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_9yat.asp

AlexCold
Starting Member

39 Posts

Posted - 2002-05-23 : 11:06:53

Thank you, MAN!!!

--
Success is a journey, not a destination!!

FAQTeam member
http://www.faqteam.org/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources