To move the data we created an FTP client that utilizes the
2.0 .NET Framework. It also uses FTP Client, a sample FTP application that I
downloaded a while back that was written in VB.NET. Unfortunately, I was not able
to find the link to where I found this, but there are many FTP client apps for
.NET 2.0 out there. Try checking here.
With this client, we created a console application. Since
we only run the report once a month, a console application, which can be
scheduled through the Windows Scheduler, makes sense.
Figure 1 Screen Shot of Console
application
The console application CAAutoTransferWorker uses the
applications settings properties to set the different properties for FTP and
the DTS packages. The XML file gets renamed on the FTP site and then is
downloaded to a local directory (code Listing 1). From the local directory the
DTS Package updates the table with the data.
Listing 1
static void Main(string[]args)
{
this.RenameFile();
this.CopyToImportDir();
}
#region Methods
public void CopyToImportDir()
{
FTPclient ftp = newFTPclient(Settings1.Default.FTPServer,
Settings1.Default.FTPUserID,Settings1.Default.FTPPWD);
String strDate = ReturnFullDate();
if(File.Exists(Settings1.Default.FTPFileToPath + Settings1.Default.FTPFile +
"." + Settings1.Default.FTPFileExt))
{
File.Move(Settings1.Default.FTPFileToPath +Settings1.Default.FTPFile + "."
+ Settings1.Default.FTPFileExt,Settings1.Default.FTPFileToPath +
Settings1.Default.FTPFile + strDate +"." + Settings1.Default.FTPFileExt);
}
// this method assumes that rename file has beenrun.
ftp.Download(Settings1.Default.FTPFile +"." + Settings1.Default.FTPFileExt,
Settings1.Default.FTPFileToPath +Settings1.Default.FTPFile + "." +
Settings1.Default.FTPFileExt, false);
}
public void RenameFile()
{
String strDate = ReturnFullDate();
String strFilePlusExt =Settings1.Default.FTPFile + strDate + "." +
Settings1.Default.FTPFileExt;
String strFileNew = Settings1.Default.FTPFile+ strDate + "." +
Settings1.Default.FTPFileExt;
FTPclient ftp = newFTPclient(Settings1.Default.FTPServer,
Settings1.Default.FTPUserID,Settings1.Default.FTPPWD);
Boolean bRenWork =ftp.FtpRename(strFilePlusExt, strFilePlusExt);
System.Threading.Thread.Sleep(1000);
ftp.Download(strFileNew, strFilePlusExt,false);
ftp.FtpRename("crystalViews" +strDate + ".log", "crystalViews.log");
}The code listed in Listing 1 takes care of renaming the file
then uses FTP to send the renamed file to the directory for data transfer
(using the file name the DTS Package expects). Next, we will show the code
used to transfer the data into SQL Server.
To transfer data using DTS, we are using SQL Server 2000.
We reference the Microsoft DTSPackage Object Library com object in our Visual
Studio project. In using Visual Studio 2005, when you specify the com object,
the managed code wrapper for the com object is automatically created. Once we
have added the reference to DTSPackage Object Library, we can now load the DTS
Package that imports the file and execute it. See Listing 2 for the code that
executes this.
Listing 2
public void RunDTSPackage()
{
DTS.PackageClass myDTS = new PackageClass();
object objTest = new object();
myDTS.LoadFromSQLServer(Settings1.Default.DTSServer,
Settings1.Default.DTSUser,Settings1.Default.DTSPwd,
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "",
Settings1.Default.DTSPackageGUID,Settings1.Default.DTSPackageVersionGUID,
Settings1.Default.DTSPackageName, refobjTest);
myDTS.Execute();
}Once this code is executed in our console application, the
data is prepared and the report can run.
