In this article we will show you how to export a file stored in your SQL
Server table out to disk. For this we will need two tables.
One will be the table that holds the file and its name and the other is
a dummy table that we need when using the WriteFile transformation.
This is the table that holds our source file and filename.
CREATE TABLE [WriteFileSource] (
[NameOfFileImported] [varchar] (255) NULL,
[ActualFileImported] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Dummy Table (Destination)
This is the dummy table that we need.
CREATE TABLE [WriteFileDummyDestination] (
[DummyCol] [varchar] (10) NULL
) ON [PRIMARY]
The NameOfFileImported column in the source should have a value in there which
is only the name of the file with extension. We will specify the directory in a moment.
Now go to the transformations tab and select only the two source columns
(you are not allowed destination columns in the transformation) and then hit
New. You should see the following screen.
After we choose Write File then OK and then Properties on the next screen.
We should be looking at this screen.
This screen is pretty self explanatory. After specifying your values choose OK followed
by OK. Fig 3 should be the screen you see
Don't be alarmed that the arrow goes nowhere in the destination, the
real destination is the files on disk. As mentioned above, the destination
table is just a dummy placeholder. All that is left is for
us to execute the DataPump task.