 | |
DTS works...Bulk Insert doesn't...why?
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
DTS works...Bulk Insert doesn't...why? -
03-20-2006
, 11:04 AM
#2
| |||
| |||
|
|
Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZMQFsuA5Cxe3v/S5ikR5y42S2maQ== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam Subject: DTS works...Bulk Insert doesn't...why? Date: Mon, 20 Mar 2006 09:04:31 -0800 Lines: 12 Message-ID: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64553 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts I have a DTS package that successfully imports a tab delimited .txt file to a table. I had to use {LF} as the Row Delimiter. When I attempt to BULK INSERT the data from the same .txt file into the same table or a temporary table created in the proc (my preferred method), I get no error messages but no records get created. Here is my Bulk insert code: BULK INSERT #Temp1 FROM 'C:\MyFolder\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = 'char(10)') Thanks in advance! |
#3
| |||
| |||
|
|
Hello, You may want to try the following statement: BULK INSERT #Temp1 FROM 'C:\dell\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n') Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZMQFsuA5Cxe3v/S5ikR5y42S2maQ== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam Subject: DTS works...Bulk Insert doesn't...why? Date: Mon, 20 Mar 2006 09:04:31 -0800 Lines: 12 Message-ID: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64553 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts I have a DTS package that successfully imports a tab delimited .txt file to a table. I had to use {LF} as the Row Delimiter. When I attempt to BULK INSERT the data from the same .txt file into the same table or a temporary table created in the proc (my preferred method), I get no error messages but no records get created. Here is my Bulk insert code: BULK INSERT #Temp1 FROM 'C:\MyFolder\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = 'char(10)') Thanks in advance! |
#4
| |||
| |||
|
|
Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZNFu7oWQ5L/W6ST52GnVN+mOiQhA== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam References: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com evx987ITGHA.960 (AT) TK2MSFTNGXA03 (DOT) phx.gbl Subject: RE: DTS works...Bulk Insert doesn't...why? Date: Tue, 21 Mar 2006 10:40:31 -0800 Lines: 72 Message-ID: <33E43F86-4266-4F8E-9358-EB87184A268C (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64609 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts Thank you Peter. Your suggestion did not work either. I have opened the file this morning with a hexidecimal file editor and see that the row terminator is hex (0A) or char(10). So I'm not sure why it isn't working. ""privatenews"" wrote: Hello, You may want to try the following statement: BULK INSERT #Temp1 FROM 'C:\dell\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n') Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZMQFsuA5Cxe3v/S5ikR5y42S2maQ== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam Subject: DTS works...Bulk Insert doesn't...why? Date: Mon, 20 Mar 2006 09:04:31 -0800 Lines: 12 Message-ID: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64553 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts I have a DTS package that successfully imports a tab delimited .txt file to a table. I had to use {LF} as the Row Delimiter. When I attempt to BULK INSERT the data from the same .txt file into the same table or a temporary table created in the proc (my preferred method), I get no error messages but no records get created. Here is my Bulk insert code: BULK INSERT #Temp1 FROM 'C:\MyFolder\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = 'char(10)') Thanks in advance! |
#5
| |||
| |||
|
|
Hello, Based on my furhter test, I found we need a "0x0D" at the end of each row when using bulk insert. It seems to be a limitation in bulk insert tool. Please rest assured this issue is reported and we will let you know if there is any update on this. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZNFu7oWQ5L/W6ST52GnVN+mOiQhA== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam References: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com evx987ITGHA.960 (AT) TK2MSFTNGXA03 (DOT) phx.gbl Subject: RE: DTS works...Bulk Insert doesn't...why? Date: Tue, 21 Mar 2006 10:40:31 -0800 Lines: 72 Message-ID: <33E43F86-4266-4F8E-9358-EB87184A268C (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64609 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts Thank you Peter. Your suggestion did not work either. I have opened the file this morning with a hexidecimal file editor and see that the row terminator is hex (0A) or char(10). So I'm not sure why it isn't working. ""privatenews"" wrote: Hello, You may want to try the following statement: BULK INSERT #Temp1 FROM 'C:\dell\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n') Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZMQFsuA5Cxe3v/S5ikR5y42S2maQ== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam Subject: DTS works...Bulk Insert doesn't...why? Date: Mon, 20 Mar 2006 09:04:31 -0800 Lines: 12 Message-ID: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64553 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts I have a DTS package that successfully imports a tab delimited .txt file to a table. I had to use {LF} as the Row Delimiter. When I attempt to BULK INSERT the data from the same .txt file into the same table or a temporary table created in the proc (my preferred method), I get no error messages but no records get created. Here is my Bulk insert code: BULK INSERT #Temp1 FROM 'C:\MyFolder\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = 'char(10)') Thanks in advance! |
#6
| |||
| |||
|
|
Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZNs4ZP66B4nsH1RLyqGh621wadAg== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam References: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com evx987ITGHA.960 (AT) TK2MSFTNGXA03 (DOT) phx.gbl |
<FjdvCnYTGHA.7884 (AT) TK2MSFTNGXA03 (DOT) phx.gbl>
Quote:
|
Subject: RE: DTS works...Bulk Insert doesn't...why? Date: Wed, 22 Mar 2006 05:21:27 -0800 Lines: 145 Message-ID: <E5111091-393F-440D-8BFF-899AAF9A519A (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64635 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts Hi Peter, I really do appreciate your time and input to my problem. I did some more testing an had somewhat of a breakthrough. I was able to get the following statement to work: DECLARE @bulk_cmd varchar(1000) SET @bulk_cmd = 'BULK INSERT #Temp1 FROM ''c:\MyFolder\MyFile.txt'' WITH (FIRSTROW = 2, ROWTERMINATOR = '''+CHAR(10)+''')' EXEC(@bulk_cmd) But I'm not sure why. :-) ""privatenews"" wrote: Hello, Based on my furhter test, I found we need a "0x0D" at the end of each row when using bulk insert. It seems to be a limitation in bulk insert tool. Please rest assured this issue is reported and we will let you know if there is any update on this. Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Community Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZNFu7oWQ5L/W6ST52GnVN+mOiQhA== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam References: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com evx987ITGHA.960 (AT) TK2MSFTNGXA03 (DOT) phx.gbl Subject: RE: DTS works...Bulk Insert doesn't...why? Date: Tue, 21 Mar 2006 10:40:31 -0800 Lines: 72 Message-ID: <33E43F86-4266-4F8E-9358-EB87184A268C (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64609 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts Thank you Peter. Your suggestion did not work either. I have opened the file this morning with a hexidecimal file editor and see that the row terminator is hex (0A) or char(10). So I'm not sure why it isn't working. ""privatenews"" wrote: Hello, You may want to try the following statement: BULK INSERT #Temp1 FROM 'C:\dell\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n') Best Regards, Peter Yang MCSE2000/2003, MCSA, MCDBA Microsoft Online Partner Support When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. ================================================== === This posting is provided "AS IS" with no warranties, and confers no rights. -------------------- Thread-Topic: DTS works...Bulk Insert doesn't...why? thread-index: AcZMQFsuA5Cxe3v/S5ikR5y42S2maQ== X-WBNR-Posting-Host: 65.205.165.30 From: =?Utf-8?B?amltYm8=?= <crutch (AT) newsgroups (DOT) nospam Subject: DTS works...Bulk Insert doesn't...why? Date: Mon, 20 Mar 2006 09:04:31 -0800 Lines: 12 Message-ID: <A89A326B-6E38-4592-A56A-6FFB7879BE63 (AT) microsoft (DOT) com MIME-Version: 1.0 Content-Type: text/plain; charset="Utf-8" Content-Transfer-Encoding: 7bit X-Newsreader: Microsoft CDO for Windows 2000 Content-Class: urn:content-classes:message Importance: normal Priority: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830 Newsgroups: microsoft.public.sqlserver.dts Path: TK2MSFTNGXA03.phx.gbl Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.dts:64553 NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250 X-Tomcat-NG: microsoft.public.sqlserver.dts I have a DTS package that successfully imports a tab delimited .txt file to a table. I had to use {LF} as the Row Delimiter. When I attempt to BULK INSERT the data from the same .txt file into the same table or a temporary table created in the proc (my preferred method), I get no error messages but no records get created. Here is my Bulk insert code: BULK INSERT #Temp1 FROM 'C:\MyFolder\MyFile.txt' WITH (FIRSTROW = 2, FIELDTERMINATOR = '\t', ROWTERMINATOR = 'char(10)') Thanks in advance! |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.