Write File Transformation

By Allan Mitchell
Version 2000
Level Beginner

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.

Source Table

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.

Fig 1

After we choose Write File then OK and then Properties on the next screen. We should be looking at this screen.

Fig 2

This screen is pretty self explanatory. After specifying your values choose OK followed by OK. Fig 3 should be the screen you see

Fig 3

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.