DTS in Stored Procedures

Question

DTS in Stored Procedures

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

You don't need to do that. If the file is not delimited and is in fixed width format, you use BCP in conjunction with a format file to specify the field byte offsets. No need for all those messy substring()'s.

And actually, since you're calling this from within a stored proc, why use xp_cmdshell & bcp ?

Just use BULK INSERT. See Books Online for details

BULK INSERT manatron.dbo.pt61_actor

FROM 'C:\pt61_actor.TXT'

WITH

(

FORMATFILE='C:\pt61_actor_format.txt'

)

M-301385 SSC Veteran Points: 248 More actions · Answer 2

Thanks, I gave this a try but it still didn't work. The format file is putting "\t" as the terminator, and I can't find anything online that tells me what to put if there is no delimiter.

Here is the code I run:

-----------------------

BULK INSERT manatron.dbo.pt61_actor

FROM 'C:\pt61_actor.TXT'

WITH

(

FORMATFILE='C:\formatfile-f-c.fmt'

)

------------------------

and this is the error I get:

------------------------

OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].

Msg 7399, Level 16, State 1, Line 1

OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.

The statement has been terminated.

Msg 4866, Level 17, State 66, Line 1

Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.

------------------------

Here is a copy of the format file :

----------------------------------

8.0

15

1 SQLCHAR 0 1 "\t" 1 LINE_TYPE SQL_Latin1_General_CP1_CI_AS

2 SQLCHAR 0 20 "\t" 2 FILING_ID SQL_Latin1_General_CP1_CI_AS

3 SQLCHAR 0 2 "\t" 3 ACTOR_ROLE SQL_Latin1_General_CP1_CI_AS

4 SQLCHAR 0 1 "\t" 4 BUSI_FLG SQL_Latin1_General_CP1_CI_AS

5 SQLCHAR 0 100 "\t" 5 LAST_NAME SQL_Latin1_General_CP1_CI_AS

6 SQLCHAR 0 70 "\t" 6 FIRST_NAME SQL_Latin1_General_CP1_CI_AS

7 SQLCHAR 0 70 "\t" 7 MIDDLE SQL_Latin1_General_CP1_CI_AS

8 SQLCHAR 0 200 "\t" 8 ADDRESS_1 SQL_Latin1_General_CP1_CI_AS

9 SQLCHAR 0 200 "\t" 9 ADDRESS_2 SQL_Latin1_General_CP1_CI_AS

10 SQLCHAR 0 70 "\t" 10 CITY SQL_Latin1_General_CP1_CI_AS

11 SQLCHAR 0 100 "\t" 11 STATE SQL_Latin1_General_CP1_CI_AS

12 SQLCHAR 0 50 "\t" 12 ZIP SQL_Latin1_General_CP1_CI_AS

13 SQLCHAR 0 3 "\t" 13 CNTRY_ABV SQL_Latin1_General_CP1_CI_AS

14 SQLCHAR 0 50 "\t" 14 COUNTRY SQL_Latin1_General_CP1_CI_AS

15 SQLCHAR 0 1 "\r\n" 15 ADDR_TYPE SQL_Latin1_General_CP1_CI_AS

---------------------------------------

Thanks again for the input everyone. It is greatly appreciated!

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

Just use an empty string:

1 SQLCHAR 0 1 "" 1 LINE_TYPE SQL_Latin1_General_CP1_CI_AS

M-301385 SSC Veteran Points: 248 More actions · Answer 4

Got it. I tried that and it works.

But now I'm back to the original problem. I need to store the format file on a mapped drive. For instance:

BULK INSERT manatron.dbo.pt61_actor

FROM 'C:\pt61_actor.TXT'

WITH

(

FORMATFILE='S:\DATA\PT_61_DOWNLOADS\formatfile-f-c.fmt'

)

The query analyzer throws back an error that says:

Server: Msg 4861, Level 16, State 1, Line 1

Could not bulk insert because file 'S:\DATA\PT_61_DOWNLOADS\formatfile.fmt' could not be opened. Operating system error code 3(The system cannot find the path specified.).

I am assuming this is because it is a mapped drive/permissions issue.

Grrrr... *frustration sets in*

Thanks again everybody...

Michael E. Baker Say Hey Kid Points: 676 More actions · Answer 5

Use UNC format instead \\{server}\{share}\DATA\PT_61_DOWNLOADS\formatfile-f-c.fmt

M-301385 SSC Veteran Points: 248 More actions · Answer 6

Eureka!!! That got it!

Thank you everyone for your help!