Fixed Width Flat File Bulk Insert Error "Invalid Column Number"
I'm trying to create an SSIS package that includes a bulk insert tasks that inserts data from a fixed width file. I have a file created that has the proper widths for each column. There are 19 columns in this specific file. I created a format file off of the table to reference with the bulk insert task. It comes up with the error message: Cannot bulk load. Invalid column number in the format file "C:\FileFormat01-n.fmt". I try it outside of SSIS and I get the same error. Here is the format file created off of the table: 9.0191 SQLNCHAR 2 102 "" 1 Recordnumber SQL_Latin1_General_CP1_CI_AS2 SQLNCHAR 2 20 "" 2 Patient Number SQL_Latin1_General_CP1_CI_AS3 SQLNCHAR 2 2 "" 3 Facility ID SQL_Latin1_General_CP1_CI_AS4 SQLNCHAR 2 6 "" 4 Patient Type SQL_Latin1_General_CP1_CI_AS5 SQLNCHAR 2 74 "" 5 Guarantor SQL_Latin1_General_CP1_CI_AS6 SQLNCHAR 2 74 "" 6 Patient Name SQL_Latin1_General_CP1_CI_AS7 SQLNCHAR 2 50 "" 7 Guarantor Address 1 SQL_Latin1_General_CP1_CI_AS8 SQLNCHAR 2 50 "" 8 Guarantor Address 2 SQL_Latin1_General_CP1_CI_AS9 SQLNCHAR 2 36 "" 9 Guarantor City SQL_Latin1_General_CP1_CI_AS10 SQLNCHAR 2 4 "" 10 Guarantor State SQL_Latin1_General_CP1_CI_AS11 SQLNCHAR 2 18 "" 11 Guarantor Zip Code SQL_Latin1_General_CP1_CI_AS12 SQLNCHAR 2 56 "" 12 Relative 1 Name SQL_Latin1_General_CP1_CI_AS13 SQLNCHAR 2 26 "" 13 Relative 1 Phone SQL_Latin1_General_CP1_CI_AS14 SQLNCHAR 2 12 "" 14 Patient Birthdate SQL_Latin1_General_CP1_CI_AS15 SQLNCHAR 2 2 "" 15 Patient Indicator SQL_Latin1_General_CP1_CI_AS16 SQLNCHAR 2 12 "" 16 Guarantor Birthdate SQL_Latin1_General_CP1_CI_AS17 SQLNCHAR 2 26 "" 17 Guarantor Work Phone SQL_Latin1_General_CP1_CI_AS18 SQLNCHAR 2 22 "" 18 Guarantor Soc Sec No SQL_Latin1_General_CP1_CI_AS19 SQLNCHAR 2 6 "" 19 Filler1 SQL_Latin1_General_CP1_CI_AS One thing I noticed is that it doubled the size of the field 102 should really only be 51. I've rebuilt the format file a hundred different ways, but it still does not come out right. Here is the table CREATE TABLE [dbo].[01]( [Recordnumber] [nchar](51) NULL, [Patient Number] [nchar](10) NULL, [Facility ID] [nchar](1) NULL, [Patient Type] [nchar](3) NULL, [Guarantor] [nchar](37) NULL, [Patient Name] [nchar](37) NULL, [Guarantor Address 1] [nchar](25) NULL, [Guarantor Address 2] [nchar](25) NULL, [Guarantor City] [nchar](18) NULL, [Guarantor State] [nchar](2) NULL, [Guarantor Zip Code] [nchar](9) NULL, [Relative 1 Name] [nchar](28) NULL, [Relative 1 Phone] [nchar](13) NULL, [Patient Birthdate] [nchar](6) NULL, [Patient Indicator] [nchar](1) NULL, [Guarantor Birthdate] [nchar](6) NULL, [Guarantor Work Phone] [nchar](13) NULL, [Guarantor Soc Sec No] [nchar](11) NULL, [Filler1] [nchar](3) NULL ) ON [PRIMARY] The data in the file is like this (all on one line): 2 9999992203MOUSEUSH,MICKEY;C DIPP,FIRSTS;C 9999 EEE LLLL LLL 12 GGGGGGA OH45103 999999E999999 999-99-9999 NN I know I could probably go through and just set up all of the columns manuallyin a connection manager for a dataflow, but I need to do this with dozens of different layouts and already have proper table formats set up the files. It would be nice to get it to work with the format files. What am I doing wrong? --Thanks--
February 29th, 2008 4:03am

I suspect it's because you have spaces in your column names, but am not sure. Build a format file via: http://technet.microsoft.com/en-us/library/ms191516.aspx
Free Windows Admin Tool Kit Click here and download it now
February 29th, 2008 5:02am

I used the BCP method directly from that article to generate my format files. I'll play with it some more to try and figure it out. Is there anything I need to do to denote fixed width? I thought the last time I used a format file, I had to tweak something.
February 29th, 2008 4:15pm

I tried removing the spaces from my field names, recreated the format file and it still gets the same error. I also tried changing the datatype to char instead of nchar. 9.0191 SQLCHAR 2 51 "" 1 Recordnumber SQL_Latin1_General_CP1_CI_AS2 SQLCHAR 2 10 "" 2 PatientNumber SQL_Latin1_General_CP1_CI_AS3 SQLCHAR 2 1 "" 3 FacilityID SQL_Latin1_General_CP1_CI_AS4 SQLCHAR 2 3 "" 4 PatientType SQL_Latin1_General_CP1_CI_AS5 SQLCHAR 2 37 "" 5 Guarantor SQL_Latin1_General_CP1_CI_AS6 SQLCHAR 2 37 "" 6 PatientName SQL_Latin1_General_CP1_CI_AS7 SQLCHAR 2 25 "" 7 GuarantorAddress 1 SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 25 "" 8 GuarantorAddress 2 SQL_Latin1_General_CP1_CI_AS9 SQLCHAR 2 18 "" 9 GuarantorCity SQL_Latin1_General_CP1_CI_AS10 SQLCHAR 2 2 "" 10 GuarantorState SQL_Latin1_General_CP1_CI_AS11 SQLCHAR 2 9 "" 11 GuarantorZipCode SQL_Latin1_General_CP1_CI_AS12 SQLCHAR 2 28 "" 12 Relative1Name SQL_Latin1_General_CP1_CI_AS13 SQLCHAR 2 13 "" 13 Relative1Phone SQL_Latin1_General_CP1_CI_AS14 SQLCHAR 2 6 "" 14 PatientBirthdate SQL_Latin1_General_CP1_CI_AS15 SQLCHAR 2 1 "" 15 PatientIndicator SQL_Latin1_General_CP1_CI_AS16 SQLCHAR 2 6 "" 16 GuarantorBirthdate SQL_Latin1_General_CP1_CI_AS17 SQLCHAR 2 13 "" 17 GuarantorWork Phone SQL_Latin1_General_CP1_CI_AS18 SQLCHAR 2 11 "" 18 GuarantorSocSecNo SQL_Latin1_General_CP1_CI_AS19 SQLCHAR 2 3 "" 19 Filler1 SQL_Latin1_General_CP1_CI_AS Msg 4823, Level 16, State 1, Line 1 Cannot bulk load. Invalid column number in the format file "C:\Department-n.fmt". BULK INSERT [dbFileManip].[dbo].[b01] FROM 'C:\Export\101.txt' WITH(FORMATFILE='C:\Department-n.fmt') CREATE TABLE [dbo].[b01]( [Recordnumber] [char](51) NULL, [PatientNumber] [char](10) NULL, [FacilityID] [char](1) NULL, [PatientType] [char](3) NULL, [Guarantor] [char](37) NULL, [PatientName] [char](37) NULL, [GuarantorAddress 1] [char](25) NULL, [GuarantorAddress 2] [char](25) NULL, [GuarantorCity] [char](18) NULL, [GuarantorState] [char](2) NULL, [GuarantorZipCode] [char](9) NULL, [Relative1Name] [char](28) NULL, [Relative1Phone] [char](13) NULL, [PatientBirthdate] [char](6) NULL, [PatientIndicator] [char](1) NULL, [GuarantorBirthdate] [char](6) NULL, [GuarantorWork Phone] [char](13) NULL, [GuarantorSocSecNo] [char](11) NULL, [Filler1] [char](3) NULL ) ON [PRIMARY]
Free Windows Admin Tool Kit Click here and download it now
February 29th, 2008 4:38pm

Try the following (1) insert 1 row into the table (2) bcp out using the format file (3) Bulk Insert it back in. this should work. Now use the data file and compare it with the one you generated in the SSIS package
March 6th, 2008 10:34pm

You still have spaces in two of the column names: 7 SQLCHAR 2 25 "" 7 GuarantorAddress 1 SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 25 "" 8 GuarantorAddress 2 SQL_Latin1_General_CP1_CI_AS You have two options: 1) Rename the columns in your table, removing the spaces and then regereating this file not my prefered option 2) Encapsulate the column names with double quotes: 7 SQLCHAR 2 25 "" 7 "GuarantorAddress 1" SQL_Latin1_General_CP1_CI_AS8 SQLCHAR 2 25 "" 8 "GuarantorAddress 2" SQL_Latin1_General_CP1_CI_AS It would be helpful if a future release of bcp would automatically encapsulate the coulmn names or have it as an option/flag Hopethis is helpful! Gary Stearn http://garystearn.co.uk
Free Windows Admin Tool Kit Click here and download it now
June 4th, 2008 2:42pm

Did you ever find a soultion to this problem? I am having the same issue.
June 10th, 2008 7:26pm

TracyK wrote: Did you ever find a soultion to this problem? I am having the same issue. I think the thread makes it quite clear what was the problem in this cass. Whether that is applicableto your situation, I have no idea. I suggest that you start a new thread and present your problem inmore detail. Beware that if you are not getting this problem with SQL 2008, you should probably use oneof the regular SQL Server forums.
Free Windows Admin Tool Kit Click here and download it now
June 11th, 2008 12:44am

9.0 19 1 SQLCHAR 2 51 "" 1 Recordnumber SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 2 10 "" 2 PatientNumber SQL_Latin1_General_CP1_CI_AS 3 SQLCHAR 2 1 "" 3 FacilityID SQL_Latin1_General_CP1_CI_AS 4 SQLCHAR 2 3 "" 4 PatientType SQL_Latin1_General_CP1_CI_AS 5 SQLCHAR 2 37 "" 5 Guarantor SQL_Latin1_General_CP1_CI_AS 6 SQLCHAR 2 37 "" 6 PatientName SQL_Latin1_General_CP1_CI_AS 7 SQLCHAR 2 25 "" 7 GuarantorAddress 1 SQL_Latin1_General_CP1_CI_AS 8 SQLCHAR 2 25 "" 8 GuarantorAddress 2 SQL_Latin1_General_CP1_CI_AS 9 SQLCHAR 2 18 "" 9 GuarantorCity SQL_Latin1_General_CP1_CI_AS 10 SQLCHAR 2 2 "" 10 GuarantorState SQL_Latin1_General_CP1_CI_AS 11 SQLCHAR 2 9 "" 11 GuarantorZipCode SQL_Latin1_General_CP1_CI_AS 12 SQLCHAR 2 28 "" 12 Relative1Name SQL_Latin1_General_CP1_CI_AS 13 SQLCHAR 2 13 "" 13 Relative1Phone SQL_Latin1_General_CP1_CI_AS 14 SQLCHAR 2 6 "" 14 PatientBirthdate SQL_Latin1_General_CP1_CI_AS 15 SQLCHAR 2 1 "" 15 PatientIndicator SQL_Latin1_General_CP1_CI_AS 16 SQLCHAR 2 6 "" 16 GuarantorBirthdate SQL_Latin1_General_CP1_CI_AS 17 SQLCHAR 2 13 "" 17 GuarantorWork Phone SQL_Latin1_General_CP1_CI_AS 18 SQLCHAR 2 11 "" 18 GuarantorSocSecNo SQL_Latin1_General_CP1_CI_AS 19 SQLCHAR 2 3 "" 19 Filler1 SQL_Latin1_General_CP1_CI_AS Try this. Change your prefix length from 2 to 0. Example: 1 SQLCHAR 0 51 "" 1 Recordnumber SQL_Latin1_General_CP1_CI_AS
April 20th, 2010 12:37pm

Hi Fanbladeus, I am facing similar kind of issue with the same error. Did you happen to find a solution for this BULK INSERT Error... please help.. Thanks!
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2010 4:14am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics