Raw File Destination Create Always error

Hello,
I'm having issues with the Raw File Destination and using Create Once/Create Always.

First problem we have is that if we have WriteOption="Create Once" when the file is already present then we get a design-time validation error: "Error occurred because the output file already exists and the WriteOption is set to Create Once". To me that doesn't seem right - why should the fact that the file already exists be a problem - it doesn't matter if WriteOption="Create"

So OK, we can deal with that by deleting the file prior to using it. That's rubbish but there you go.
Now the REAL problem. It seems that Create Once does not do what BOL (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/d311b458-aefc-4b4d-b1a1-4c0ebbb34214.htm) suggests it will.

BOL says:

Create once

Creates a new file. If the data flow that uses the Raw File destination is in a loop, the file is created once and data is appended to the file when the loop repeats. The data appended to the file must match the file format.

If we set WriteOption="Create Once" and run it, it fails on the second iteration of the loop. The error is: "Error occurred because the output file already exists and the WriteOption is set to Create Once"

That doesn't make sense. That is COMPLETELY at odds with what BOL says (above).

What is going on here? WriteOption="Create once" doesn't do what BOL says it does.

We have ValidateExternalMetadata=FALSE on our Raw File Destination and DelayValidation=TRUE on our data-flow.
Any help very much appreciated!!!

-Jamie

Hi, me again!

This is really quite annoying me because I'm convinced that does not work even though it should. I would appreciate someone, ANYONE, downloading a demo package I have built and having a go at the same. The package is here: http://blogs.conchango.com/Admin/ImageGallery/blogs.conchango.com/jamie.thomson/20051116AggregatorDemo.zip

The only prerequisite is that you have access to AdventureWorksDW. All you need to do is change the server name in the connection manager.

If it exhibits the same behaviour that it is for me then it will fail on the second iteration of the loop. And that completely negates what BOL says (see above).

I am running this on RTM.

PLEASE can someone try this and prove to me I'm not losing my marbles. I would really appreciate it.

-Jamie|||Please can someone try this. Its not hard and it won't take long!

-Jamie|||Jamie,
I got the same error as you when I tried it. I didn't use your package but created a new one. I'm not sure if it is a BOL error or a SSIS error.
Larry|||

Larry_Pope wrote:

Jamie,

I got the same error as you when I tried it. I didn't use your package but created a new one. I'm not sure if it is a BOL error or a SSIS error.

Larry

Thank you Larry.
I think its an SSIS bug because the functionality that BOL describes sounds legitamate.

Anyone from Microsoft reading this? Should I raise a bug?

-Jamie|||Hi,

The behavior is correct. This is a documentation bug. The WriteOption = CreateOnce means it will create a file if it doesn't exist. If a file already exists, then the task will fail. If you want to delete the existing file, and create a new file everytime, you should use option WriteOption = CreateAlways. If you want to append rows to the existing file, you should use WriteOption = Append.

To be able to create the file in the first iteration of the loop, and then keep appending rows in the subsequent iterations of the loop, here is what you need to do. To be able to use Append option in design time, the file should already exist, so that it can make sure that the metadata matches. So just run one iteration of the loop with WriteOption = CreateOnce or CreateAlways and run it, so that the file is created. Then go back to the Raw file Destination, and change WriteOption to Append and set ValidateExternalMetadata to False. Now you can go ahead and delete the raw file. Now if you execute the loop, it will create the file in the first iteration and will append rows in the subsequent ones. If you use TruncateAppend option instead of Append, it will truncate the rows from previous iteration, and will Append new rows, but the metadata should still match.

Please go ahead and file a documentation bug for the incorrect documentation of CreateOnce option.

Hope that helps.

Ranjeeta|||Ranjeeta,
Thank you very very much. This worked.

I haven't filed a bug for the documentation, I have used the "Send Feedback" link in BOL instead.

If I get time I I'll try and blog this because the steps required to make it work are definately not clear.

-Jamie