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

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


kcmooreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mohan_sekarCommented:
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
kcmooreAuthor Commented:
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
mohan_sekarCommented:
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
kcmooreAuthor Commented:
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
mohan_sekarCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.