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

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

Flat File source to Oracle OLE DB Target (ETL)

What could be simpler: map a flat file record structure, extract the data, and populate essentially the same flat file record struc in an Oracle table. Let the fun begin.

Specifically: the flat file record struc is fixed length 196 bytes. A particular field consists of 4 bytes of Integer data; IS deals very nicely with the definition, does not appear to be any issue with that. The issue is trying to get the 4 bytes of integer to map and load into the Oracle table. The data type in the flat file def is DT_UI4. The data type in the Oracle target is DT_NUMERIC. One would think that perhaps a simple transform and Viola?! I've defined the transform but does not seem to matter - whatever I try yeilds the same results.

I 've tried many different src/trg data type defs., but all yeild the same results.

Execution Results from debug:

Everything validates and then...

[kcd [8671]] Error: Data conversion failed. The data conversion for column "load_time_min" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

[kcd [8671]] Error: The "output column "load_time_min" (11050)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "load_time_min" (11050)" specifies failure on error. An error occurred on the specified object of the specified component.

Any ideas appreciated!

Thanks.

What precision and scale are you specifying for the DT_Numeric? Since the Integer column is defined as an unsigned integer, you need to allow enough room in the numeric to support it. Or you could use a derived column transform to explicity cast the integer to the numeric, if you know there will not be an overflow.

(DT_NUMERIC, 10, 0) [Your_Column]