Bulk Insert - Ascii file from mainframe

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)

Bulk Insert - Ascii file from mainframe

Author

Topic

sunfun
Starting Member

5 Posts

Posted - 2007-11-15 : 10:01:39

Here is what I am trying to do. We are FTPing a file from the MVS mainframe using the -ascii option, then I need to load the fixed length file in SQL Server. I am using the table described below.

CREATE TABLE [dbo].[Status_CPE_Load_Table_Temp] (
[Success_Fail_Ind] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ES_Service_ID] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[H6_Cust_ID] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Component_ID] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Success_Fail_Desc] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[System_Date] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[System_Time] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[System_Time_Zone] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Charge_ID] [nvarchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Program_Cd] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Order_Nbr] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Action_Type] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Here is my format file.

8.0
12
1 SQLCHAR 0 1 "" 1 Success_Fail_Ind ""
2 SQLCHAR 0 9 "" 2 ES_Service_ID ""
3 SQLCHAR 0 9 "" 3 H6_Cust_ID ""
4 SQLCHAR 0 9 "" 4 Component_ID ""
5 SQLCHAR 0 256 "" 5 Success_Fail_Desc ""
6 SQLCHAR 0 10 "" 6 System_Date ""
7 SQLCHAR 0 8 "" 7 System_Time ""
8 SQLCHAR 0 3 "" 8 System_Time_Zone ""
9 SQLCHAR 0 9 "" 9 Charge_ID ""
10 SQLCHAR 0 1 "" 10 Program_Cd ""
11 SQLCHAR 0 8 "" 11 Order_Nbr ""
12 SQLCHAR 0 1 "\r\n" 12 Action_Type ""

Here is my insert statement.

Truncate TABLE status_CPE_Load_Table_temp
GO
BULK INSERT status_CPE_Load_Table_temp
FROM 'S:\Test_S\FKL\Data\CUD\Import\stus_cpe.txt'
WITH (FORMATFILE='S:\Test_S\FKL\Data\CUD\Export\Export_Scripts\cpe.fmt')
GO
select * from status_CPE_Load_Table_temp

Here is a sample of the data file. (Multiple spaces seem to be automatically removed here, but the lengths listed are correct.)

1416550003416550003006286272CHARGE ID 146952186 NOT FOUND IN COMPNT-ITM-CHG 11/14/200713:47:43CST146952186S01928108
1416550003416550003006286272CHARGE ID 146952187 NOT FOUND IN COMPNT-ITM-CHG 11/14/200713:47:43CST146952187S01928108

The terminating character in Hex is 0D 0A. The error message I recieve is:

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 2 (ES_Service_ID).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 2, column 2 (ES_Service_ID).
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 3, column 2 (ES_Service_ID).

etc. Until max errors is recieved

Anyone have any ideas why I am getting this error?

TIA,

Aaron

sanjnep
Posting Yak Master

191 Posts

Posted - 2007-11-15 : 10:32:13

I think you need to change data type to varchar instead of nvarchar. Try this first and let me know.
Thanks

sunfun
Starting Member

5 Posts

Posted - 2007-11-15 : 10:53:17

quote:
Originally posted by sanjnep

I think you need to change data type to varchar instead of nvarchar. Try this first and let me know.
Thanks

You rock!!! Gold stars and Kuddos to snjnep. Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources