Automating Reports with diverse data sources using Business Objects Enterprise Server - Part 1
page 3 of 4
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19502/ 54

Moving Data

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.


View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-03-05 2:32:03 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search