I have a little problem with a small C# Applikation that executes DTS. I
don't know if this is the right Newsgroup. First of all here is what the
Applikation should do.
A FileWatcherObject is monitoring a Directory for new CSV files. If there
are new Files, the files will be added to an hash-table with key and
filename. A timer objekt looks every 10 Seconds for Files in the hash-table
and starts a DTS-Package for the first 20 Files of the hash-table. The
applikation works fine, but after 2300 files i get an error from the DataPump
task 80040428. This seems to be an connection problem. all other files after
that error will fail too. Is it possible to change the applikation to avoid
this error ? Here is the source (windows service)

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.ServiceProcess;
using System.Configuration;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.DTSPkg80;




namespace xMonitoringService
{

public class xMonitoringService : System.ServiceProcess.ServiceBase
{
/// <summary>
/// Erforderliche Designervariable.
/// </summary>
private System.ComponentModel.Container components = null;
public PackageClass package;
public int count;
public int gsmtcount;
public Hashtable myHT;
private System.Timers.Timer timer1;
public string serverName;
public string userName;
public string password;
public string packageName1;
public string packageName2;
public string packageName3;
public string filter1;
public string filter2;
public string filter3;
string directoryToMonitor;

FileSystemWatcher watcher;

public xMonitoringService()
{
// Dieser Aufruf ist für den Windows Komponenten-Designer erforderlich.
InitializeComponent();

// TODO: Initialisierungen nach dem Aufruf von InitComponent hinzufügen
}

// Der Haupteinstiegspunkt für den Vorgang
static void Main()
{
System.ServiceProcess.ServiceBase[] ServicesToRun;

// Innerhalb eines Prozesses können mehrere Dienste ausgeführt werden.
Sie können einen
// Dienst zu diesem Prozess hinzufügen, indem Sie die folgende Zeile
ändern,
// um ein zweites Dienstobjekt zu erstellen. Beispiel:
//
// ServicesToRun = new System.ServiceProcess.ServiceBase[] {new
Service1(), new MySecondUserService()};
//
ServicesToRun = new System.ServiceProcess.ServiceBase[] { new
xMonitoringService() };

System.ServiceProcess.ServiceBase.Run(ServicesToRu n);
}

/// <summary>
/// Erforderliche Methode für die Designerunterstützung.
/// Der Inhalt der Methode darf nicht mit dem Code-Editor geändert werden.
/// </summary>
private void InitializeComponent()
{
this.timer1 = new System.Timers.Timer();
components = new System.ComponentModel.Container();
((System.ComponentModel.ISupportInitialize)(this.t imer1)).BeginInit();


this.timer1.Enabled = true;
this.timer1.Interval = 10000;
this.timer1.Elapsed += new
System.Timers.ElapsedEventHandler(this.timer1_Elap sed);

this.ServiceName = "xMonitoringService";
count=0;
gsmtcount=0;
myHT = new Hashtable();

serverName =
System.Configuration.ConfigurationSettings.AppSett ings["serverName"];
userName =
System.Configuration.ConfigurationSettings.AppSett ings["userName"];
password =
System.Configuration.ConfigurationSettings.AppSett ings["password"];
packageName1 =
System.Configuration.ConfigurationSettings.AppSett ings["packageName1"];
packageName2 =
System.Configuration.ConfigurationSettings.AppSett ings["packageName2"];
packageName3 =
System.Configuration.ConfigurationSettings.AppSett ings["packageName3"];
directoryToMonitor =
System.Configuration.ConfigurationSettings.AppSett ings["directoryToMonitor"];
filter1 =
System.Configuration.ConfigurationSettings.AppSett ings["filter1"];
filter2 =
System.Configuration.ConfigurationSettings.AppSett ings["filter2"];
filter3 =
System.Configuration.ConfigurationSettings.AppSett ings["filter3"];
((System.ComponentModel.ISupportInitialize)(this.t imer1)).EndInit();






}

/// <summary>
/// Die verwendeten Ressourcen bereinigen.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}

/// <summary>
/// Führen Sie die Vorgänge aus, um den Dienst zu starten.
/// </summary>
protected override void OnStart(string[] args)
{

EventLog.WriteEntry("xMonitoringService","On Start");
SetupWatcher();
this.timer1.Enabled = true;
this.timer1.Start();


// TODO: Fügen Sie hier Code hinzu, um Ihren Dienst zu starten.
}

/// <summary>
/// Beenden Sie den Dienst.
/// </summary>
protected override void OnStop()
{
//Disable the watcher

watcher.EnableRaisingEvents = false;
this.timer1.Enabled = false;
this.timer1.Stop();
myHT.Clear();
myHT = null;
serverName= null;
userName= null;
password= null;
packageName1= null;
packageName2= null;
packageName3= null;
filter1= null;
filter2= null;
filter3= null;
directoryToMonitor= null;

EventLog.WriteEntry("xMonitoringService","On Stop");



// TODO: Hier Code zum Ausführen erforderlicher Löschvorgänge zum
Anhalten des Dienstes einfügen.
}

private void SetupWatcher()
{

// Create a new FileSystemWatcher and set its properties.

watcher = new System.IO.FileSystemWatcher();

watcher.Path = directoryToMonitor;

//Watch for changes in FileName
watcher.NotifyFilter = NotifyFilters.FileName;

//Watch for all files.
watcher.Filter = "";

//Add event handlers.

watcher.Created += new FileSystemEventHandler(OnCreated); //Begin watching
watcher.EnableRaisingEvents = true;

}

// Define the event handlers.

public void OnCreated(object source, FileSystemEventArgs e)
{

// Specify what is done when a file is created.

//Pass in the path of the file to the DTS Package
myHT.Add(e.Name.ToString(),e.FullPath.ToString());



//ExecutePackage(e.Name,e.FullPath);




}
private void timer1_Elapsed(object sender,System.Timers.ElapsedEventArgs e)
{
IDictionaryEnumerator myEnumerator = myHT.GetEnumerator();
try
{

while ( myEnumerator.MoveNext() && count < 20)
{
if(File.Exists(myEnumerator.Value.ToString()))
{

ExecutePackage(myEnumerator.Key.ToString(),myEnume rator.Value.ToString());
count++;
myHT.Remove(myEnumerator.Key);
gsmtcount++;


}
}

if(myHT.Count == 0)
{
myHT.Clear();
myHT = new Hashtable();
}

count=0;
myEnumerator = null;
}
catch(System.Exception ex)
{
EventLog.WriteEntry("xMonitoringService","Error Message :" + ex.Message
+ " Stack Trace : " + ex.StackTrace + ex.Source);

}
finally
{
if(myHT.Count == 0)
{
myHT.Clear();
myHT = null;
myHT = new Hashtable();
}
myEnumerator = null;


}



}


public void ExecutePackage(string gv_fileName, string gvFullFileName)
{

try
{
package = new PackageClass();
UCOMIConnectionPointContainer CnnctPtCont =
(UCOMIConnectionPointContainer) package;
UCOMIConnectionPoint CnnctPt;
PackageEventsSink PES = new PackageEventsSink ();

Guid guid = new Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5"); // UUID of
PackageEvents Interface
CnnctPtCont.FindConnectionPoint(ref guid, out CnnctPt);
int iCookie;
CnnctPt.Advise(PES, out iCookie);
object pVarPersistStgOfHost = null;

//Retrieve global settings from the Configuration File


string packageName="";

if (gv_fileName.IndexOf(filter1)!= -1)
{
packageName = packageName1;

}

if (gv_fileName.IndexOf(filter2)!= -1)
{
packageName = packageName2;

}

if (gv_fileName.IndexOf(filter3)!= -1)
{
packageName = packageName3;

}



if (packageName != "")
{
package.LoadFromSQLServer(serverName,userName,
password,DTSSQLServerStorageFlags.DTSSQLStgFlag_De fault, null, null, null,
packageName, ref pVarPersistStgOfHost);

//Loop through all the Global variables and remove the
//variables that are of type string

foreach(GlobalVariable global in package.GlobalVariables)
{

if (global.Name == "gv_FileFullName")
{

package.GlobalVariables.Remove(global.Name);

}
if (global.Name == "gv_fileName")
{

package.GlobalVariables.Remove(global.Name);

}




}



//Read all the global variables that are of type string

package.GlobalVariables.AddGlobalVariable("gv_File FullName",gvFullFileName);
package.GlobalVariables.AddGlobalVariable("gv_file Name",gv_fileName);

package.Execute();
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie);




}

}




catch(System.Runtime.InteropServices.COMException ex)
{

EventLog.WriteEntry("xMonitoringService","Error Code : " + ex.ErrorCode
+ "Error Message :" + ex.Message + " Stack Trace : " + ex.StackTrace +
ex.Source);

}

catch(System.Exception ex)
{

EventLog.WriteEntry("xMonitoringService", "Error Message :" + ex.Message
+ " Stack Trace : " + ex.StackTrace + ex.InnerException +" "+ex.TargetSite);

}
finally
{
package.UnInitialize();
package = null;


}



}

public class PackageEventsSink : DTS.PackageEvents
{
public void OnQueryCancel(string EventSource, ref bool pbCancel)
{
EventLog.WriteEntry("xMonitoringService"," In OnQueryCancel: Event
Source: " + EventSource);
pbCancel = false;

}



public void OnStart(string EventSource)
{

EventLog.WriteEntry("xMonitoringService"," In OnStart: Event Source: "
+ EventSource);

}



public void OnProgress(string EventSource, string ProgressDescription,
int PercentComplete, int ProgressCountLow, int ProgressCountHigh)
{

EventLog.WriteEntry("xMonitoringService"," In OnProgress: Event Source:
" + EventSource + " Progress Description : " + ProgressDescription +
" PercentComplete : " + PercentComplete +
" ProgressCountLow : " + ProgressCountLow +
" ProgressCountHigh : " + ProgressCountHigh);

}



public void OnError(string EventSource, int ErrorCode, string Source,
string Description, string HelpFile, int HelpContext, string
IDofInterfaceWithError, ref bool pbCancel)
{
EventLog.WriteEntry("xMonitoringService"," In OnError: Event Source: "
+ EventSource + " Error Code : " + ErrorCode.ToString() + " Source : " +
Source.ToString()+
" Description : " + Description + " HelpFile : " +
HelpFile + " HelpContext : " + HelpContext +
" InterfaceError " + IDofInterfaceWithError);
pbCancel = false;

}



public void OnFinish(string EventSource)
{

EventLog.WriteEntry("xMonitoringService", "In OnFinish");



}

}







}
}

immer dieselbe Datei?
Always the same file?

Vielleicht koennten Sie diese Option aushärten.
Perhaps you could set this option


"close connection on completion" in die "Workflow properties"


Gibt's fuer uns mehr fehler text oder?
Is there for us more error text?





--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Karpo" <Karpo (AT) discussions (DOT) microsoft.com> wrote

Hi Allan,

thanks for your response. no it is not always the same file. I've checked
the files and they are ok. Well, i've build this DTS Package without a
windows service application and move the files to archive and error location
with a activeX script (just like the sample app). That DTS Package works
fine, but it is to slow for such a lot files. My intention to build up the
windows service applikation is to make the import much faster. The
applikation is monitoring the folder, starts the dts and only the task wich
moves the files to the archive location is still an activex script within the
dts package. The error is alway at the first DataPumpTask and appears after a
lot of files have been already transformed. The "close connection on
completion" property is set on each transformation workflow (i've two of it
in the dts package). The DTS Package includes some select statements to get
the last primary key.
The error messages coming in this order. I guess that the othe messages
depending on th first. Here they are (try to translate):

First:

Fehler bei der Ausführung des folgenden DTS-Pakets: //error while executing
dts-package
Fehlerquelle: Microsoft Data Transformation Services (DTS)-Paket // error
source:
Fehlerbeschreibung:Fehler bei Paket wegen Fehler bei //error discription:
Schritt 'DTSStep_DTSDataPumpTask_2'.
Fehlercode: 80040428 // error code
\Hilfedatei für Fehler:sqldts80.hlp
Hilfekontext-ID für Fehler:700

Second:

Error in applicationname(my windowsservice), Version 1.0.1971.27653,
fehlgeschlagenes Modul dtspkg.dll, Version 2000.80.760.0, Fehleradresse
0x00104107.

Other errors follow with error source on the first error message. The
application tryed to process other files after that error, but no
transformation is successfull after that error. It always begins again with
the first error. I guess this ar the points to make sure:

Is it possible that the windowsservice starts the DTS-Package with a file
that perhaps is used by another instance of the dts-package (wrong
programming with timer and hash-table) ? How can i make sure that eyery file
will be processed only once? Is there a way to check if the server is busy
before executing dts ? Are there perhaps memory leaks in the programming or
dts ? Is there a way to get more control over the dts within the application
? Or is there perhaps another solution to build up this application ?

Thanks...hope you can help me ;-)










"Allan Mitchell" wrote:

The error message in German or English is generic and does not really help
us here. It could be that you are getting a timeout. It could be a number
of things. Is the number of files imported before crapping out constant?

Do resources get drained?

Do you see the file that is being requested being locked by another process,
perhaps the previous DTS invocation? (www.sysinternals - filemon)

it could be that the service gets ahead of the DTS package as mutiple
invocations can happen at the same time.


How can you make sure every file os only picked up once. You could simply
read them from the direectory and enter them into a table. Every so often
you take that table and process the files from within. Similar to this.


How to loop through a global variable Rowset
(http://www.sqldts.com/default.aspx?298)



You would therefore have the service that enters the rows in the table
containing the file names and the package would wake up every now and then
and import the files.


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Karpo" <Karpo (AT) discussions (DOT) microsoft.com> wrote

Hello Allan,

thanks a lot. Looping through a global variable Rowset is the best way. I
guess that the timer was the main problem. The files were locked by another
DTS process.
It took a little time to find a select-statement that returns if the dts
package is still running. The performance is very good. Looping with an
ActiveX script within the dts-package takes about 2 hours for 2600 files
(nearly 70 MB). Looping through a global variable Rowset takes only 11
minutes. The service add the files to a table and start every 60 seconds the
dts package if the package doesen't run.

Thanks again....good support ;-)

Carsten



"Allan Mitchell" wrote: