July 1, 2009 at 9:46 am
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
----------------
LoanNum FirstName
004010101 Edgar
871818188 Paul
910910203 Ned
SourceFile2
----------------
L_number fname
654873215 Donnie
212121212 Mike
898989898 Steve
SourceFile3
----------------
fstName LoNo
Greg 111111111
Tim 222222222
Karim 333333333
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"
?
July 2, 2009 at 2:39 am
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
July 2, 2009 at 8:52 am
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.
July 2, 2009 at 9:10 am
scratch that, sorry I didn't read carefully.
I don't believe there's a way to change mappings on the fly.
July 2, 2009 at 10:01 am
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.
July 2, 2009 at 10:44 am
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
July 2, 2009 at 3:04 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply