sql server dts: [quoted text, click to view]

Thanx for the link. I have found it some minutes ago, too.

I modified my package exactly as described, but as soon as I do
step 2:

Edit your Raw File DestinationAdapter. Set WriteOption="Append" &
ValidateExternalMetadata="False". This second setting is important
because it means you can delete the raw file from file storage and
the package will not complain about it.

the raw file destination shows a yellow triangle with an exclamation
sign and running the package (from the command line) gives the error

Error: 2006-07-13 13:42:00.59
Code: 0xC004706B
Source: Chargenkopf Raw DTS.Pipeline
Description: "component "Raw File Destination" (1443)" failed
validation and returned validation status "VS_NEEDSNEWMETADATA".

What can I do?

thanx for your help and kind regars,

Hi,

I'm using a data flow task having a raw file destination
in a foreach loop. Basically it looks like this:

file system task: delete raw file
foreach ....
data flow task: import data to raw file

The raw file destination's write option is set to 'create
once'. According to the help the file will be created on
the first iteration. In subsequent iterations the date
will be appended to the raw file.

This is exactly what I want, but when I execute the task
a second time the package validation fails:

[Raw File Destination [3739]] Error: Error occurred because
the output file already exists and the WriteOption is set to
Create Once. Set the WriteOption property to Create Always,
or delete the file.

What can I do? The file will be deleted (see file system
task), but at validation time it is still present.

A collegue recommended set the write option to 'create
always' at design time and to use a script task to set
the write option to 'append' after the first iteration.

But I can't imagine that there's no better solution.

Thanx for any help and best regards,

Perhaps you can use the DelayValidation property (set it to TRUE). Otherwise,
read this post by Jamie Thomson, perhaps that sheds some light:

You also did steps 1 and 3? You needs step 1 the first time to get an initial
version of the meta data. Do not forget step 3 also:

3) Any data flows that source data from this raw file must have
DelayValidation=TRUE so that they don't fail validation on account of the raw
file not existing when the package starts up

because the error you get seems to imply that it STILL does this validation:

"component "Raw File Destination" (1443)" failed validation and returned
validation status "VS_NEEDSNEWMETADATA".

Set is on ALL data flows that use it.

Also read his follow-up:

I tried it from scratch and got the same error now after step 2. After some
fiddling around I got it to work. I think I did the following to resolve it:

Right-click raw file destination -> edit -> column mappings and set/click
the column
mappings in the graphical designer

I also opened tab "input and output properties" to make sure the external
columns branch and the input columns branch have the same columns in the same
order.

I want to test again if this is the solution, but now I cannot get it to
fail again! It always work now, even after closing and re-opening the dtsx
file.

This feels a little "buggy" to me. I have SP1 installed.

I got this message too:

The column "..." needs to be added to the external metadata
collection
....
Do you want the component to fix these errors automatically?

And confirmed with "yes".

and (see my post before this one) then did the same thing as you did. Guess
we both had exactly the same problem.

I don't know which prefix you mean? I also didn't change the column names

Your welcome. I also added this thread as a comment on Jamie's blog. Perhaps
other people find it useful.

Greetings,

[quoted text, click to view]

Of course. I started with step 1 and created the raw files. Then
I proceeded to step 2. Immediately after applying step 2 the raw
file destination shows an exclamation sign in a yellow triangle.
The tooltip says "this component has inconsistent metadata".

[quoted text, click to view]

I did exactly this, on subsequent tries also on the task writing
to the raw file. I have also disabled the metadata validation on
all components, but I always get the error message below:

[quoted text, click to view]

To me it looks like the validate metadata property is ignored.

[quoted text, click to view]

Some more explanations here:

If ValidateExternalMetadata=false the above warning will be shown.
Running the package fails with

[DTS.Pipeline] Error: "component "Raw File Destination" (1443)"
failed validation and returned validation status
"VS_NEEDSNEWMETADATA".

If ValidateExternalMetadata=true an x in a red circle is shown.
The tooltip reads "the file cannot be opened for reading the
metadata. If the file does not exist, and the component ...".
Running the package fails with (this is as expected)

[Raw File Destination [1443]] Error: The file cannot be opened
for reading the metadata. If the file does not exist, and the
component has already defined external metadata, you can set the
"ValidateExternalMetadata" property to "false" and the file will
be created at the runtime.

[DTS.Pipeline] Error: "component "Raw File Destination" (1443)"
failed validation and returned validation status "VS_ISBROKEN".

[quoted text, click to view]

Of course I did (see my other replies to this posting). The troubles
have been in step 2 indeed. This step has to be executed exactly as
follows to make it work (at least for me):

2a) set WriteOption = Append

The raw file destination component will immediately show an ex-
clamation mark ("this component has inconsistent metadata"). I
can't explan this - to me this looks like a bug?

2b) double-click the raw file destination component; a message like
the following will be shown:

The external metadata collection is out of synchronization with
the data source columns.
The column "..." needs to be added to the external metadata
collection
...
Do you want the component to fix these errors automatically?

After answering with "yes" I had to adjust some connections bet-
ween the input and output columns.

After closing the editor for the raw file destination the ex-
clamation mark disappiears.

2c) set ValidateExternalMetadata = False

The remaining steps stay exactly as Jamie has described.


I think that my troubles have been self made. The data flow looks
like this:

XML Data Source -> Data Conversion -> Raw File Destination

I didn't rename the converted columns in the data conversion com-
ponent because I preferred the names with the prefix. But this pre-
fix is not written into the raw file. Therefore the metadata got
inconsistent after I have switched WriteOption to Append. At least
this is the only explanation I can come up with.

[quoted text, click to view]

I have found out the same about half an hour ago (see my other post in
this thread).

Thanx for your help,
Gerald

PS: I'm working with SP1, too, but everything is x64: Win2K3, SQL Server

[quoted text, click to view]

Great idea, I planned to do it after work :-)

Greetings,

[quoted text, click to view]

My data source is an XML file which gives me all columns as unicode
strings. But my target table uses non-unicode strings. Therfore my
data flow looks like this (simplified, there are also some filters):

XML Data Source -> Data Conversion -> Raw File Destination

In the data conversion component I have checked all available input
columns and changed the output alias to the same name as the input
column name (I don't like the default prefix and I didn't want to
invent new names).

After this the available input columns in the raw file destination's
column mappings are shown as follows:

XML Data Source.Column1
XML Data Source.Column2
...
Data Conversion.Column1
Data Conversion.Column2
...

Of course my column names are more meaningful ;-)

The prefix is the part left of the colon and is the name of the
corresponding data flow component.

greetings,
Gerald