source files with different col names, order... one mapping for all? fuzzy mapping?

Question

source files with different col names, order... one mapping for all? fuzzy mapping?

Greg J

SSCarpal Tunnel

Points: 4288
More actions
July 1, 2009 at 9:46 am

#131760

I've got a smattering of files, each containing the "same" data (ie. values are like). They're all tab delimited .txt files in the same folder. I'd like to stick a ForEach loop container on the directory and move them into the landing pad SQL table using just 1 DataFlow task and 1 mapping for all of them.
The issue is that even though they contain the same data, the column names that appear on row 1 for all of them potentially differ... slightly. Also, the columns might not all be in the same order.
As an example...
SourceFile1
----------------
LoanNumFirstName
004010101Edgar
871818188Paul
910910203Ned
SourceFile2
----------------
L_numberfname
654873215Donnie
212121212Mike
898989898Steve
SourceFile3
----------------
fstNameLoNo
Greg111111111
Tim222222222
Karim333333333
Is there some way to map 1 "master" source file, and then just create some kind of synonym in the DataFlow to tell SSIS that it should map "fname" and "fstName" columns as I've mapped "FirstName"
?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

NaffMonk Old Hand Points: 325 More actions · Answer 1

It seems that you will need the ability to change the input fields metadata at runtime, I'm not sure this is possible, as these are set at design time and then set to read only at run time (someone correct me if I'm wrong).

Assuming it is possible, this will need to be done programatically using the dts runtime libraries. A good place to start is the msdn section on building a custom data source, as this gives the basics of what's going on in the background when you design and run a data flow.

Keep us up to date of your findings if you look into this, I would be very interested to know if you were successful or not.

cheers

Laurence

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Setup your file source to skip the first row and don't use the header info. As long as each file has the same format there should not be any problem.

TangoVictor SSChampion Points: 10975 More actions · Answer 3

scratch that, sorry I didn't read carefully.

I don't believe there's a way to change mappings on the fly.

TheFoo SSChasing Mays Points: 636 More actions · Answer 4

You could use the Bulk Insert task for this.

- Point the Destination Connection properties at your SQL landing pad.

- Create a new file connection manager for your source. These do not specify the meta data, just the filename and location.

- Under the Format settings, select format file. You can these specify your meta data for each file in the format files.

If you're using a Foreach Loop, you can pass the name of the source file and format file using the expressions screen in the bulk insert task.

I've not actually done this myself but have been looking into doing something similar...just haven't got around to it yet.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Just checking on something. In all of these files, is the loan number column guaranteed to have only non-alpha characters such as numeric digits and dashes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg J SSCarpal Tunnel Points: 4288 More actions · Answer 6

thanks for the input gang. I ended up going with flat file source connections all with their own 'auto' schema into a UNION ALL object. Each source still has it's own mapping, but the UNION ALL makes it easy to indicate what is what by providing a common output column name for the disparate sources.