Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-06-30
5
Medium Priority
?
1,995 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
ID: 24745170
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
ID: 24745219
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
ID: 24745440
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
ID: 24746566
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 1500 total points
ID: 24747079
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

782 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