0

I am currently working on creating an SSIS package for a new import files we will received. The company changed how it was before - 1 flat file for 1 table. Now they have there data scatted across multiple files for 1 table.

I have 2 data flow tasks of the 12 that I am fiddling with trying to get to work correctly. --1 has 5 files to 1 db destionation --1 has 3 files to 1 db destination.

Each file does have a consistent key (Property_ID) that I am using for sorting. Current setup of a flat file data flow task is:

  • FlatFile Source1
  • Data Conversion
  • Merge Join1(on sorting ID for other file)
  • FlatFile Source2
  • Data Conversion
  • Merge Join1
  • Sort(sort on propertyID)

  • Merge Join2

  • Flat File Source

  • Data Conversion

  • Merge Join2

  • Merge Join2 -> OLE DB Destination

I have tried all join types for the merge join and am still having problems getting the data into the table correctly.

Each file has different columns for the specified destination table aside from the "Property_ID"

The current results I am receiving are Just rows of data with data from only 1 flat source file and not the others.

Any assistance is greatly appreciated.

UPDATE

I did solve this problem.

What I did was combine 2 files into a merge after each was sorted on a key.

After that, the merge was put into a merge join as the right table where the left join table was the other solo flat source file that would actually have the main sorting key to go into the tables.

The merge join was then sorted on the same sortID all files have been used on directly to the ole db destination.

Brody
  • 1
  • 4
  • Are you sure you should be using a merge join? Are the _fields_ scattered accross the three files with matching `Property_ID`, or do all the files have different `Property_ID`'s? If they are all different you should be using Union All or even better, a For Each File Iterator to load them into the table one by one. – Nick.Mc Sep 12 '13 at 02:45
  • Each file does have the same Property_ID, but each file has different columns aside from that solo shared column. I resolved this by doing sorts on each file, then a 'MERGE' inputing the 'MERGE' into a 'MERGE JOIN' – Brody Sep 12 '13 at 14:15
  • OK, now we've established that you are after a join, not a union, then yes, you must ensure data is sorted before it goes into a merge. Glad you got it sorted. You should post that as an answer and accept it. – Nick.Mc Sep 12 '13 at 23:57

1 Answers1

0

What I did was combine 2 files into a merge after each was sorted on a key.

Flat Source DataConversion Sort Merge Join(The left table is the table that has the main id's I want to match)

This goes into a Merge Join that is from the solo Flat Source File; Flat Source DataConversion Sort ToAboveMerge Join

The merge join was then sorted on the same sortID all files have been used on directly to the ole db destination.

Brody
  • 1
  • 4