flat files without column names; how to map over 250 columns

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

nicolasdiogo wrote:

hi,

i am sure this question must have been anwsered some where, but after a lot of searching i still have not find the anwser.

i have flat files without column headers (267 columns in total).
since i have the file's description i have created a table to house these extracts with the columns in the same order as in the flat files.
additionally, i have an excel containing a list of the column names their data types and length as well as their position on the flat files.
in the old, DTS would map the columns without headers to those columns in the destination table using their order, in which case it works like a breeze for me. but i can not find a way of doing that in SSIS.

i would very much appreciate someone's assistance on this one since i am sure that there must be a better way than manually (and tediously & error prone) to map all those columns.

thanks in advance

I'm afraid its manual. I agree this is very tedious but I don't agree that its any more error prone than DTS. One of the big problems with DTS was that it would often do things for you but do things wrongly. One of the aims with SSIS was to put all responsibility in the hands of the package developer rather than letting DTS "guess".

I'm sure your answer will be "but it always works for me in DTS" and that's a valid point of course. I guess you just can't please all of the people all of the time. Personally I think the approach of putting all decisions in the hands of the developer is a good thing - but that's just me.

How long would it take to map 250 columns? I would guess at about 20 minutes? Yes its onerous but its not TOO much time out of your day is it? It must have taken you about 5 minutes to write the email above.

Another alternative might be to see if the import/export wizard does any auto-mapping for you. I'm not sure what it does to be honest but it might be worth checking out.

Sorry, I think you may be stuck doing it manually.

-Jamie