I had a project where a mail service will get mail status from MTA response, parse the data into a CSV files.
Each files may go up to 400 000 records. Those data need to be in my database to update a table.
To insert all of this row by row and wait for the next file is time consuming. Thus, i create a windows service and an SQL DTSX packages to perform this task. pretty fast and pretty nice.
1. Create the DTSX package in SQL manager studio
2. Create your win services
Here is the code .
public partial class DZ_DTSService : ServiceBase new ArrayList();
{
Timer _timer;
ArrayList _logFiles =
public DZ_DTSService()
{new Timer(new TimerCallback(OnNextMinute), null , Timeout.Infinite, Timeout.Infinite);
_timer =
InitializeComponent();
}
public void OnNextMinute(object state)
{
// Read config
Settings dzSetting;
if (!ReadSettings(out
dzSetting))
return;
FillStack(dzSetting);
}
void FillStack(Settings settings)
{
try
{
string[] files = Directory.GetFiles(settings.CsvFolder, @"*.csv"
, SearchOption.TopDirectoryOnly);
for (int
i = 0; i < files.Length; i++)
{_logFiles.Add(files[i]);
}
RunSSISPackageService(settings);
}
catch (Exception
ex)
{
WriteError(ex);
}
}
protected override void OnStart(string
[] args)
{ Settings dzSetting;
if (!ReadSettings(out
dzSetting))
return ;
AppLogger.WriteInformation(
"Service has Started "
);
_timer.Change(0, dzSetting.Sleep);
//sec + 000 Milisecond every 5 minute run service = 5*60 000
}
protected override void
OnStop()
{
AppLogger.WriteInformation(
"Service has Stopped "
);
}
//read setting for the application
//this will return all necessary variable stored in the config file for later use
bool ReadSettings(out
Settings settings)
{
bool success = true;
Settings setting = AppSettings.ReadSettings();
settings = (success)?(setting):(
null
);
return
success;
}
protected void
RunSSISPackageService(Settings setting)
{
Application app =
new
Application();
Package package =
null ;
string pkgLocation = setting.CsvPackage;
try
{
package = app.LoadPackage(pkgLocation,
null);
//load the package file
package.Connections[
"DestinationConnectionOLEDB"].ConnectionString = setting.Connection;
//The Database connection string get from app config
foreach (string logFile in
_logFiles)
{
// Log exist
if
(!File.Exists(logFile))
continue ;
try
{
// string fileName = logFile.ToString();
package.Connections[
"SourceConnectionFlatFile"].ConnectionString = logFile;
//change connection string
//for each files since you may have multiple file waiting
//Execute DTSX.
DTSExecResult results = package.Execute();
// great way to write error from the application
//log those errors in a log file
if
(results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in
package.Errors)
{
WriteError(setting,
"Error while Excecuting File: "
+ logFile +
" Code: "
+ local_DtsError.Description);
}
}
else
//delete if update DB //move file
{
//Move the file to an archive directory..
FileInfo fi =
new
FileInfo(logFile.ToString());
string
destination=setting.Archive;
fi.MoveTo(destination + fi.Name);
WriteSuccess(setting, fi.Name);
}
}
catch (Exception
ex)
{
WriteError(setting, ex.Message );
}
}
}
catch (Exception
ex)
{
WriteError(setting,ex.Message);
}
finally
{
if (package != null
)
{
package.Dispose();
package =
null;
}
}
}