1

I have a very large Fixed length patient eligibility file that I would like to use SSIS to import into SQL. The problem is it's not a standard file, the first 15 columns made up of patient information (Name, Address etc). The next section (20 columns) are made up of the eligibility information for the above patient including history. This section repeats 100 times. I want to pull the first section into a patients table then import the next section (with up to 100 repeating rows for the same patient) into a child table.

I know it can be done in SQL, I'm doing that now but it's not very user friendly when it comes to someone else making changes in the future. The file is to large to do a bulk insert into one column.

Example File Format

FName: Bobby
LName: Smith
Addr1: 102 Elm St

Next section repeats 100 times

ElgStartdate: 01/01/2019
ElgEndDate: 02/01/2019
Type: SA

How do I tell SSIS that the next 20 columns in the are repeated 100 times.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Bobby Cruthirds
  • 13
  • 3
  • 1
    A mixed record format (header and detail - call it what you want) is generally a poor fit for SSIS as well. What people generally do in this instance is import the file as a single column, much as you'd do with bcp/bulk insert and split each row based on "knowledge" leading column is H, 100 rows is detail, etc – billinkc May 21 '19 at 20:43
  • I kinda had the feeling that may be the answer, I've googled it to death. – Bobby Cruthirds May 22 '19 at 13:57
  • The other approaches I've seen/done is 1) A script component with multiple output buffers for the different record types. A challenge here can be tying the streams back together but adding an artificial key column can usually overcome it. 2) Add N data flows with a flat file source in each that deals with the expected format and routes non-conforming rows to the bit bucket. – billinkc May 22 '19 at 14:11

0 Answers0