I recently re-wrote a database application that gets daily data feeds via text
files that are uploaded to an ftp site. My client's customers generate the text
files from their mainframe systems and upload them to the ftp site for access.
The files are:
- Downloaded from the ftp site
- Loaded into the database
- Renamed, and
- Copied to a new location so they can be archived
Automating the Process with DTS
I do not like data loading processes that are dependent on human intervention,
so during the re-write I wanted to replace this process with one that could be
scheduled and run automatically. I remembered that DTS (Data Transformation
Services) has a task that supports ftp, so I decided to investigate this
approach. It could not have been easier to implement.
DTS: A Brief Introduction
If you have never worked with DTS, you have missed using one of the most useful
tools that come with SQL Server. DTS makes it so easy to get data in/out of SQL
Server, that it almost makes up for all the years developers had to put up with
the limited functionality of bcp (bulk copy program). If you have never used
bcp, consider yourself lucky.
The main purpose of DTS is to allow you to define connections to various data
sources and destinations and pump the data from one to the other. For example,
you can define a text file as a data source, an instance of SQL Server as the
destination, and then pump the data from one to the other. DTS supports numerous
data sources, all of which can be seen by opening a new package and clicking the
Connection option. To open a new package, expand the Server tree of the target
SQL Server and click the Data Transformation Services folder. You can now
right-click on Local Packages and select New Package. At this point you define
the connections and add any other tasks that need to be completed during the
DTS allows you to include several other tasks that can be executed before and
after the data transfer process. For example, the Process task will execute an
.exe or .bat file, and the ActiveX Script Task allows you to execute either
JScript or VBScript. And of course there is the File Transfer Protocol Task,
which is the main focus of this article. The other tasks supported can be seen
by clicking Task on the menu.
Creating the Pieces
I needed a DTS package that would do the following:
- Connect to an ftp site and download a specific file
- Transfer the data from the file into a SQL Server table
- Rename and move the file to an archive directory
- Scrub/load the data into the destination table
Before we can create a package that does this, we need some source data, two
destination tables, and a stored procedure. We will use a fixed-width text file
that conforms to the following layout for the data source. You can use your
favorite text editor to create the file.
My sample data looks like this:
Kelly Smith 8885551212
Paul Jones 8885551213
Mike Andrews 8885551214
Creating the tables is the next step. You may be wondering why I want to create
two destination tables. The first one is a temporary destination that holds the
data in the exact same format as the text file, and the second one is the data's
final destination--how it appears after being scrubbed. The tables can be
created with the following DDL.
CREATE TABLE ContactsImport
CI_Name char(40) NOT NULL,
CI_Phone char(10) NOT NULL,
CI_Processed char(1) DEFAULT 'N'
CREATE TABLE Contacts
CN_ID int IDENTITY NOT NULL PRIMARY KEY,
CN_FName varchar(20) NOT NULL,
CN_LName varchar(20) NOT NULL,
CN_Phone varchar(10) NOT NULL
I learned to use the two table approach during my first experience with DTS in
SQL Server 7. You can actually scrub the data while it is in transit using
VBScript or JScript, but that slowed down the import significantly when large
files were processed. The faster approach (in my experience) is to import the
data as-is, and process it with a T-SQL inside the database. Of course, when
large files are processed you do need to make sure that you have the hard drive
space to accommodate the extra copy of the data.
The stored procedure used to rename/move the source file and scrub the data is
CREATE PROCEDURE pr_ContactsImport
-- Part I: Move/rename source file
DECLARE @fn char(6),
SET @cmd ='Move c:\articles\contacts.txt c:\articles\archive\'+@fn+'.txt'
EXEC master..xp_cmdshell @cmd
-- End of Part I: Move/rename source file
-- Part II: Scrub the data
INSERT Contacts (CN_FName,CN_LName,CN_Phone)
IF @@Error <> 0
UPDATE ContactsImport SET CI_Processed='Y'
IF @@Error <> 0
IF @@TRANCOUNT > 0
-- End of Part II: Scrub the data
To facilitate the explanation I have separated the code into two Parts. The code
in Part I moves/renames the file using xp_cmdshell. xp_cmdshell is an extended
stored procedure that is used to execute an O/S command. I use the CONVERT and
REPLACE functions to create the new file name that conforms to a mmddyy.txt
convention and then execute the Move command. The source directory is
C:\Articles and the archiving directory is: C:\Articles\Archive.
NOTE: A user must be granted Execute permission on xp_cmdshell unless they are a
member of the Sysadmin fixed server role.
In Part II I scrub the data. The contact name is stored in one column in the
data source (text file), but I want to store it in two in the Contacts table.
The SUBSTRING and CHARINDEX functions allow me to find the space between the
first and last name and parse accordingly.
For my testing I executed the above DDL in tempdb.
Creating the Package
Now that we have all the pieces in place we can create the package. Start a new
package using the instructions in the DTS: A Brief Introduction section, and
complete the following:
- Click Task on the menu and select File Transfer Protocol Task
- Make sure Internet Site is selected as the source and then specify the
connection information for the ftp site
- Input the Destination directory
- Click the Files tab and a list of files on the site will be displayed
- Double-click the source file and click OK
At this point you can test the task by right-clicking on it and selecting
Execute Step. The file will be moved to the destination directory.
The next step is to create the Transform Data Task that transfers the data from
the text file into the ContactsImport table. Before you can create this task,
you must define the source/destination connections. Create the source connection
by completing the following:
- Click Connection on the menu and select Text File (Source).
- Specify the File name (and location) and click OK.
- In the Text File Properties dialog ensure that the Fixed Field is selected
and click Next.
- If the data is aligned per the file format specified above, the column marker
(black line with arrow) will automatically be placed at the 40 position and the
end-of-file marker (red line) will be at 50. Click Finish and OK to complete the
Now we need to create the connection for the destination. Complete the following
to do this.
- Click Connection on the menu and select Microsoft OLE DB Provider for SQL
- Specify the server name and connection info and ensure tempdb (or what ever
database you are using) is selected as the target database
The source/destination connections are defined, so let's create the Transform
Data Task with the following:
- Click Tasks on the menu and select Transform Data Task.
- Click the source connection and then the destination connection. An arrow is
created that links the two connections.
- Double-click the arrow and select the Destination tab. On my server, the
Contacts table is selected by default because it is the first in the alpha sort
of the table list for tempdb. Select the ContactsImport table and click the
Transformations tab. Notice the arrows correctly map the source and destination
- Click OK to close the dialog.
When you have multiple tasks within a package they will execute in parallel
unless you define the order of execution. In this case we want the File Transfer
Protocol Task to execute first, and if that is successful we want to execute the
Transform Data Task. We can ensure this happens by completing the following:
- Click on the File Transfer Protocol Task
- Press the Control key and select the Text File (Source) connection
- Click Workflow from the menu and select On Success
The last steps in this process are to archive the source file and scrub the
data. We do this by executing the pr_ContactsImport procedure with an Execute
SQL Task. Create this task by completing the following:
- Click Task on the menu and select Execute SQL Task
- Select Microsoft OLE DB Provider for SQL Server in the Existing Connection
- In the SQL Statement input type: EXEC pr_ContactsImport
- Click OK to close the dialog
Make sure that it executes after the Transform Data Task by creating an On
Success Workflow like we have done twice before.
The package is complete and you can execute it by clicking Package from the menu
and selecting Execute. You should also be aware that you can schedule the
package to execute automatically. Simply right-click on the package in the Local
Packages view and select Schedule Package. For my project I knew that the source files would be uploaded no later than 10 AM, so I scheduled accordingly. After the data is loaded an email is automatically sent that informs the relevant personnel that the process completed successfully.
DTS is a great tool. If you have never worked with it I strongly encourage you
start experimenting with different methods of transferring/transforming data. I
am sure having an understanding of the functionality it supports will benefit
you in the long run.