Just lately I've been breaking the cardinal rule of blogging: "Thou shalt always blog". Well the reason is simply that I've been heads-down on my current project and:
a) Haven't had anything to blog about and
b) Haven't got time to anyway!
Well b) is still true but a) isn't so I'm back blogging! This is all to do with using the Raw File Destination adapter in a data-flow. Our requirement was to be able to loop through a series of data sources with identical metadata, appending the data to a raw file on each iteration. This would then enable us to process all of the data from the multiple sources all at the same time. UNION ALL was not an option here because we don't know how many sources exist.
The behaviour of a raw file at runtime depends on the WriteOption property. BOL states:
|
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. |
Well that sounded like it did exactly what we wanted! Well unfortunately not - it turns out that this is a documentation bug (which I notified Microsoft of via the "Send Feedback" link in BOL) and WriteOption="Create Once" doesn't do what we want at all.
Thanks to expert SSIS tester Ranjeeta Nanda however I was able to get this working. If you want to create a raw file on the first iteration of a loop and then append to it thereafter here are the steps you need to follow:
1) To use the Append option you first need to create the file at design time so that the package knows about the metadata of the raw file. To do this set WriteOption="Create Always" and run your package. Barring any mishaps your raw file should be created!
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.
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
You should now be able to run a data-flow inside a loop, appending to a raw file as on each iteration.
Thanks very very much Ranjeeta - this was becoming, nay had already become, a huge problem for us!
-Jamie