We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
2,476 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


Comment
Watch Question

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

Author

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
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.

Author

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.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.