Solved

SSIS - Bulk Insert Task Error - Invalid column number in the format file

Posted on 2009-06-30
5
1,829 Views
Last Modified: 2013-11-10
I am trying to use the Bulk Insert Task in SSIS. We have set up the format file as specified but are receiving this error:
[Bulk Insert Task] Error: An error occurred with the following error message: "Cannot bulk load. Invalid column number in the format file "F:\PID.fmt".".
 What are we missing??

Our format file looks like this:

9.0
40
1        SQLCHAR       0       3       "|"     2     Segment_Type_ID            SQL_Latin1_General_CP1_CI_AS
2        SQLCHAR       0       4       "|"     3     Sequence_Number            SQL_Latin1_General_CP1_CI_AS
3        SQLCHAR       0       20      "|"     4     External_Patient_ID        SQL_Latin1_General_CP1_CI_AS
4        SQLCHAR       0       11      "|"     5     Lab_Assigned_Patient_ID    SQL_Latin1_General_CP1_CI_AS
5        SQLCHAR       0       20      "|"     6     Alternate_Patient_ID       SQL_Latin1_General_CP1_CI_AS
6        SQLCHAR       0       25      "^"     7     Patient_Last_Name          SQL_Latin1_General_CP1_CI_AS
7        SQLCHAR       0       15      "^"     8     Patient_First_Name         SQL_Latin1_General_CP1_CI_AS
8        SQLCHAR       0       15      "|"     9     Patient_Middle_Name        SQL_Latin1_General_CP1_CI_AS
9        SQLCHAR       0       0       "|"     10     Mothers_Maiden_Name       SQL_Latin1_General_CP1_CI_AS
10       SQLCHAR       0       8       "^"     11    Patient_Date_Of_Birth      SQL_Latin1_General_CP1_CI_AS
11       SQLCHAR       0       3       "^"     12    Patient_Age_Years          SQL_Latin1_General_CP1_CI_AS
12       SQLCHAR       0       2       "^"     13    Patient_Age_Months         SQL_Latin1_General_CP1_CI_AS
13       SQLCHAR       0       7       "^"     14    Patient_Age_Days           SQL_Latin1_General_CP1_CI_AS
14       SQLCHAR       0       1       "|"     15    Patient_Gender             SQL_Latin1_General_CP1_CI_AS
15       SQLCHAR       0       1       "|"     16    Patient_Alias              SQL_Latin1_General_CP1_CI_AS
16       SQLCHAR       0       1       "|"     17    Patient_Race               SQL_Latin1_General_CP1_CI_AS
17       SQLCHAR       0       35      "^"     18    Patient_Address            SQL_Latin1_General_CP1_CI_AS
18       SQLCHAR       0       1       "^"     19    Patient_Other_Designation  SQL_Latin1_General_CP1_CI_AS
19       SQLCHAR       0       16      "^"     20    Patient_City               SQL_Latin1_General_CP1_CI_AS
20       SQLCHAR       0       2       "^"     21    Patient_State_of_Province  SQL_Latin1_General_CP1_CI_AS
21       SQLCHAR       0       10      "|"     22    Patient_Zip_or_Postal_Code SQL_Latin1_General_CP1_CI_AS
22       SQLCHAR       0       1       "|"     23    Patient_County_Code        SQL_Latin1_General_CP1_CI_AS
23       SQLCHAR       0       13      "|"     24    Patient_Home_Phone_Number  SQL_Latin1_General_CP1_CI_AS
24       SQLCHAR       0       1       "|"     25    Patient_Work_Phone_Number  SQL_Latin1_General_CP1_CI_AS
25       SQLCHAR       0       1       "|"     26    Language_Patient           SQL_Latin1_General_CP1_CI_AS
26       SQLCHAR       0       1       "|"     27    Patient_Marital_Status     SQL_Latin1_General_CP1_CI_AS
27       SQLCHAR       0       1       "|"     28    Patient_Religion           SQL_Latin1_General_CP1_CI_AS
28       SQLCHAR       0       8       "^"     29    Customer_ID        SQL_Latin1_General_CP1_CI_AS
29       SQLCHAR       0       1       "^"     30    Check_Digit_Scheme         SQL_Latin1_General_CP1_CI_AS
30       SQLCHAR       0       2       "^"     31    Bill_Code                  SQL_Latin1_General_CP1_CI_AS
31       SQLCHAR       0       1       "^"     32    ABN Flag                   SQL_Latin1_General_CP1_CI_AS
32       SQLCHAR       0       1       "^"     33    Status_of_Specimen         SQL_Latin1_General_CP1_CI_AS
33       SQLCHAR       0       1       "|"     34    Fasting                    SQL_Latin1_General_CP1_CI_AS
34       SQLCHAR       0       9       "|"     35    Patient_SSN_Number         SQL_Latin1_General_CP1_CI_AS
35       SQLCHAR       0       1       "|"     36    Filler1                    SQL_Latin1_General_CP1_CI_AS
36       SQLCHAR       0       1       "|"     37    Filler2                    SQL_Latin1_General_CP1_CI_AS
37       SQLCHAR       0       1       "|"     38    Ethnic_Group               SQL_Latin1_General_CP1_CI_AS
38       SQLCHAR       0       1       "^"     39    Sale_Price                 SQL_Latin1_General_CP1_CI_AS
39       SQLCHAR       0       1       "^"     40    Payment_Amount             SQL_Latin1_General_CP1_CI_AS
40       SQLCHAR       0       1       "\r\n"  41    Reciept_Number             SQL_Latin1_General_CP1_CI_AS



And our flat file looks like this:
PID|1|14455|17094785330|14455|TEST^TEST1||19571226|M||||||||||90020515^^^XR^^F^Y|123456789


0
Comment
Question by:kcmoore
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:mohan_sekar
Comment Utility
Do all the records in the flat file conform to the format laid out in the .fmt file? Use DTS/SSIS to check if there is a problem wih the flat file - it will pinpoint the error
0
 

Author Comment

by:kcmoore
Comment Utility
No they do not, actually there are several record types in the flat file. I am trying to isolate certain record types to be loaded into seperate tables. Below is an example of the flat file. Do I need to load all the record types first then break them apart. Not sure if my approach is correct.

Example:
HSH|^~\&|1100|TA|EHSC|TE012193|200906190955||ORU|0010|P|2.3
PID|1|14455|17094785330|14455|TEST^TEST1||19571226|M||||||||||90020515^^^XR^^F^Y|123456789
RRE|RE|17094785330^L12B|17094785330^L12B||||||200906190000|||^H123OCRAT^D
0
 
LVL 15

Expert Comment

by:mohan_sekar
Comment Utility
You will need to break them apart before loading as bcp will look for records that 'match' the format file. BCP cannot take a flat file that has records in heterogeneous format.
0
 

Author Comment

by:kcmoore
Comment Utility
OK, well then that is where i need help. How do I break apart the file based on the record type? There is always an indicator at the beginning of each row for the record type.
0
 
LVL 15

Accepted Solution

by:
mohan_sekar earned 500 total points
Comment Utility
If you know the record type, you can specify that in the SSIS data import wizard. In the 'select source table and views' step, click on 'Edit' and click on 'Edit SQL'. specify the rows that you want to import or exclude.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now