|
Home > Archive > MS SQL Server DTS > July 2005 > Executing DTS C#
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
|
| 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(ServicesToRun);
}
/// <summary>
/// Erforderliche Methode für die Designerunterstützu
ng.
/// 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.timer1)).BeginInit();
this.timer1.Enabled = true;
this.timer1.Interval = 10000;
this.timer1.Elapsed += new
System.Timers. ElapsedEventHandler(
this.timer1_Elapsed);
this.ServiceName = "xMonitoringService";
count=0;
gsmtcount=0;
myHT = new Hashtable();
serverName =
System.Configuration. ConfigurationSetting
s.AppSettings["serverName"];
userName =
System.Configuration. ConfigurationSetting
s.AppSettings["userName"];
password =
System.Configuration. ConfigurationSetting
s.AppSettings["password"];
packageName1 =
System.Configuration. ConfigurationSetting
s.AppSettings["packageName1"];
packageName2 =
System.Configuration. ConfigurationSetting
s.AppSettings["packageName2"];
packageName3 =
System.Configuration. ConfigurationSetting
s.AppSettings["packageName3"];
directoryToMonitor =
System.Configuration. ConfigurationSetting
s.AppSettings["directoryToMonitor"];
filter1 =
System.Configuration. ConfigurationSetting
s.AppSettings["filter1"];
filter2 =
System.Configuration. ConfigurationSetting
s.AppSettings["filter2"];
filter3 =
System.Configuration. ConfigurationSetting
s.AppSettings["filter3"];
((System.ComponentModel. ISupportInitialize)(
this.timer1)).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 FileSystemEventHandl
er(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(objec
t sender,System.Timers.ElapsedEventArgs e)
{
IDictionaryEnumerato
r myEnumerator = myHT.GetEnumerator();
try
{
while ( myEnumerator.MoveNext() && count < 20)
{
if(File.Exists(myEnumerator.Value.ToString()))
{
ExecutePackage(myEnu
merator.Key. ToString(),myEnumera
tor.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(strin
g gv_fileName, string gvFullFileName)
{
try
{
package = new PackageClass();
UCOMIConnectionPoint
Container CnnctPtCont =
(UCOMIConnectionPoin
tContainer) 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(se
rverName,userName,
password,DTSSQLServe
rStorageFlags. DTSSQLStgFlag_Defaul
t, null, null, null,
packageName, ref pVarPersistStgOfHost
);
//Loop through all the Global variables and remove the
//variables that are of type string
foreach(GlobalVariab
le 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_FileFullName",gvFullFileName);
package.GlobalVariables.AddGlobalVariable("gv_fileName",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
IDofInterfaceWithErr
or, 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 " + IDofInterfaceWithErr
or);
pbCancel = false;
}
public void OnFinish(string EventSource)
{
EventLog.WriteEntry("xMonitoringService", "In OnFinish");
}
}
}
}
| |
| Allan Mitchell 2005-07-25, 8:29 pm |
| 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@discussions.microsoft.com> wrote in message
news:7728F537-E0DA-4DCF-8A49- A1CE061A7A6C@microso
ft.com...
>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(ServicesToRun);
> }
>
> /// <summary>
> /// Erforderliche Methode für die Designerunterstützun
g.
> /// 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.timer1)).BeginInit();
>
>
> this.timer1.Enabled = true;
> this.timer1.Interval = 10000;
> this.timer1.Elapsed += new
> System.Timers. ElapsedEventHandler(
this.timer1_Elapsed);
>
> this.ServiceName = "xMonitoringService";
> count=0;
> gsmtcount=0;
> myHT = new Hashtable();
>
> serverName =
> System.Configuration. ConfigurationSetting
s.AppSettings["serverName"];
> userName =
> System.Configuration. ConfigurationSetting
s.AppSettings["userName"];
> password =
> System.Configuration. ConfigurationSetting
s.AppSettings["password"];
> packageName1 =
> System.Configuration. ConfigurationSetting
s.AppSettings["packageName1"];
> packageName2 =
> System.Configuration. ConfigurationSetting
s.AppSettings["packageName2"];
> packageName3 =
> System.Configuration. ConfigurationSetting
s.AppSettings["packageName3"];
> directoryToMonitor =
> System.Configuration. ConfigurationSetting
s.AppSettings["directoryToMonitor"];
> filter1 =
> System.Configuration. ConfigurationSetting
s.AppSettings["filter1"];
> filter2 =
> System.Configuration. ConfigurationSetting
s.AppSettings["filter2"];
> filter3 =
> System.Configuration. ConfigurationSetting
s.AppSettings["filter3"];
> ((System.ComponentModel. ISupportInitialize)(
this.timer1)).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 FileSystemEventHandl
er(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(objec
t sender,System.Timers.ElapsedEventArgs
> e)
> {
> IDictionaryEnumerato
r myEnumerator = myHT.GetEnumerator();
> try
> {
>
> while ( myEnumerator.MoveNext() && count < 20)
> {
> if(File.Exists(myEnumerator.Value.ToString()))
> {
>
> ExecutePackage(myEnu
merator.Key. ToString(),myEnumera
tor.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(strin
g gv_fileName, string gvFullFileName)
> {
>
> try
> {
> package = new PackageClass();
> UCOMIConnectionPoint
Container CnnctPtCont =
> (UCOMIConnectionPoin
tContainer) 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(se
rverName,userName,
> password,DTSSQLServe
rStorageFlags. DTSSQLStgFlag_Defaul
t, null, null, null,
> packageName, ref pVarPersistStgOfHost
);
>
> //Loop through all the Global variables and remove the
> //variables that are of type string
>
> foreach(GlobalVariab
le 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_FileFullName",gvFullFileName);
> package.GlobalVariables.AddGlobalVariable("gv_fileName",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
> IDofInterfaceWithErr
or, 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 " + IDofInterfaceWithErr
or);
> pbCancel = false;
>
> }
>
>
>
> public void OnFinish(string EventSource)
> {
>
> EventLog.WriteEntry("xMonitoringService", "In OnFinish");
>
>
>
> }
>
> }
>
>
>
>
>
>
>
> }
> }
>
>
| |
|
| 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:F
ehler bei Paket wegen Fehler bei //error discription:
Schritt 'DTSStep_DTSDataPump
Task_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:
[color=darkred]
> 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@discussions.microsoft.com> wrote in message
> news:7728F537-E0DA-4DCF-8A49- A1CE061A7A6C@microso
ft.com...
| |
| Allan Mitchell 2005-07-26, 8:24 pm |
| 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@discussions.microsoft.com> wrote in message
news:2F14BB50-870E-49E1-931B- 4D10BCA7CF6D@microso
ft.com...[color=darkred]
> 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:F
ehler bei Paket wegen Fehler bei //error discription:
> Schritt 'DTSStep_DTSDataPump
Task_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:
>
| |
| Karpo 2005-07-27, 11:37 am |
| 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:
[color=darkred]
> 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@discussions.microsoft.com> wrote in message
> news:2F14BB50-870E-49E1-931B- 4D10BCA7CF6D@microso
ft.com...
|
|
|
|
|