Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2009-06-30
5
1,860 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 500 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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

790 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