MS SQL Server DTS - DTS works...Bulk Insert doesn't...why?

Author

DTS works...Bulk Insert doesn't...why?

jimbo

2006-03-21, 3:31 am

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!

privatenews

2006-03-21, 3:31 am

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@newsgroups.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@microso
ft.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!
>

jimbo

2006-03-21, 1:31 pm

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.
>
>
> --------------------
> to a
> but
>
>

privatenews

2006-03-22, 3:27 am

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@newsgroups.nospam>
>References: <A89A326B-6E38-4592-A56A- 6FFB7879BE63@microso
ft.com>
<evx987ITGHA.960@TK2MSFTNGXA03.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@microso
ft.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:
>
rights.[color=darkred]
file[color=darkred]
temporary[color=dark
red]
messages[color=darkr
ed]
>

jimbo

2006-03-22, 7:31 am

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.
>
>
> --------------------
> <evx987ITGHA.960@TK2MSFTNGXA03.phx.gbl>
> rights.
> file
> temporary
> messages
>
>

privatenews

2006-03-23, 3:31 am

Hi,

Thank you for sharing your experience on this issue though I was still not
able to get this to work unless I added 0x0D at the end of the row.

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: AcZNs4ZP66B4nsH1RLyq
Gh621wadAg==
>X-WBNR-Posting-Host: 65.205.165.30
>From: =?Utf-8?B?amltYm8=?= <crutch@newsgroups.nospam>
>References: <A89A326B-6E38-4592-A56A- 6FFB7879BE63@microso
ft.com>
<evx987ITGHA.960@TK2MSFTNGXA03.phx.gbl>
<33E43F86-4266-4F8E-9358- EB87184A268C@microso
ft.com>
<FjdvCnYTGHA.7884@TK2MSFTNGXA03.phx.gbl>
>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@microso
ft.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:
>
row[color=darkred]
this[color=darkred]
this.[color=darkred]
rights.[color=darkred]
the[color=darkred]
working.[color=darkred]
so[color=darkred]
BULK[color=darkred]
>

Sponsored Links