Solved

SSIS - flat file with rows differing in number of fields

Posted on 2009-07-08
4
1,728 Views
Last Modified: 2013-11-10
Experts,

I am having issues with a basic Flat File connection trying to pull records that differ in length. The first row has around 200 fields but is not a header row. Subsequent rows have around
10 columns. I included an abbreviated example below and I believe the differing lengths are affecting the outcome as I get a msg reading "The last row in the sampled data is incomplete. The column or the row delimiter may be missing or the text is qualified incorrectly".. So when I review the recs in connection manager I notice the next row starting in the last row instead of starting a new row. It shows the {CR}{LF} but doesn't start a new row. The first 3 rows in the example refer to one acct - the acct num is the 2nd field..The first 2 chars designate the type of row(ie 01=encounter info, 02= charges). {CR}{LF} is the row delim and ^ is the col delim.

01^234566^y^Free text^45000^20020912^fff^940343^9300000+
02^234566^567^35000+
04^234566^free text^43450-
01^869433^y^Free text^45000^20020912^fff^940343^9300000+
02^869433^567^35000+
04^869433^free text^43450-

Please let me know if more detail is necessary. Any help would be very much welcomed.

Thanks, Jim


0
Comment
Question by:healthcheckinc
  • 2
4 Comments
 

Author Comment

by:healthcheckinc
ID: 24806496
I think I may have made this sound a little too difficult. What I need to do is to be able to extract certain data from a flatfile. If youll notice from above the first two chars are gonna contain different IDs. With each different ID comes a different set of columns, few of which match. Is there a way to read the flatfile and determine the ID and send them to seperate tables via ODBC destination? Thanks in advance
0
 
LVL 5

Accepted Solution

by:
jbyers79 earned 500 total points
ID: 24817467
You can use a conditional split component to split the different types of rows to various destinations based on the first two characters of the row.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24867923
You can do that using a script component as a source.

Regards,
Pedro
www.pedrocgd.blogspot.com
www.BIResort.net
0
 

Author Closing Comment

by:healthcheckinc
ID: 31601193
thx
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

910 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

22 Experts available now in Live!

Get 1:1 Help Now