Have 1 file each day with 4 or 5 record types. Have 5 DataPumps, one for
each record type - 10 columns, 15 columns, 15 columns, 18 columns, 50
columns. Each maps 1 to 1 to a table. The last DataPump is only sent 2 days
a week, the others are sent everyday.

Because the DataPump with 50 columns is not sent everyday, DTS encounters an
error ...

DTSRun OnError: DTSStep_DTSDataPumpTask_7, Error = -2147213304 (80042008)
Error string: Column name 'Col050' was not found.
Error source: Microsoft Data Transformation Services (DTS) Data Pump
Help file: sqldts80.hlp
Help context: 0

Any hints or tips on how to make this error go away? or an explanation
about why it is occuring?

I have seen other posts that indicate upon startup the first 200 rows
determine the number of columns for the whole file. Is this true and can it
be configured to accept max columns - e.g. 50?

You have 5 datapumps to handle each type of file right.

It looks as though you are passing one of the non 50 column wide files
to the 50 column wide DataPump.


When you view a file it shows the first 200 rows.

Excel uses by default the first 8 rows to determine datatype

AFAIK a text file definition is derived from the first non header row
definition.


In your instance you can do a number of things but I would look at

1. Test the file to see which version of the datapump to call and
disable the others
2. Drop each filetype into its own directory and have the datapumps
look only to that directory




"Mauna Kea" <Mauna Kea (AT) discussions (DOT) microsoft.com> wrote

Thanks for the quick response. This led me to a couple questions.

1. could you elaborate on your first solution? The daily file could have
every type of record so I am not sure what I am testing & disabling.

2. unfortunately all record types are sent in 1 file. Thus each datapump
reads the same file and looks for its specific record type.

3. you hint at a number of things to try, any additional items you could
provide?

Thanks!

"Allan Mitchell" wrote:

Well if this was me I would tell the feed provider to stop providing all
files in 1 file. Seems nuts to me.

You also mention 2 * 15 column files. How does that work then? How do
you know which of the two to send the data to?

OK so maybe you do the splitting of the file then

You get a file in you pick it up. You read the data.

You Split() the datarows and say

"How many columns?"
Keep going until that changes.

You now have your first file.

Write out to a file.

Now you need to Enable disable the corresponding DataPump tasks


So you would have an Active Script task that parsed the file
It would then do the enabling / disabling

All your datapump tasks stem from the active Script task using On
Success precedence constraints.

We do a bit of that here


Looping, Importing and Archiving
(http://www.sqldts.com/default.aspx?246)


Allan


"Mauna Kea" <MaunaKea (AT) discussions (DOT) microsoft.com> wrote