项目中也经常使用到时间参数的验证,例如根据某一时间范围来从Source中过滤一些数据. 在运行Package之前需要配置这些时间格式的参数,因此需要验证这些参数是否是正确的时间格式. 并且通常还有时间大小的验证,例如起始时间要小于结束时间.
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Globalization;
namespace ST_TEST.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
String sPackageName = Dts.Variables["System::PackageName"].Value.ToString();
string sSelectFromDate = Dts.Variables["User::SelectFromDate"].Value.ToString();
string sSelectThruDate = Dts.Variables["User::SelectThruDate"].Value.ToString();
DateTime dtSelectDateFrom = new DateTime();
DateTime dtSelectDateThru = new DateTime();
CultureInfo provider = CultureInfo.InvariantCulture;
bool bParametersValid = true;
bParametersValid &= ValidateRuntimeParameterFilled(sPackageName, sSelectFromDate, "SelectFromDate");
bParametersValid &= ValidateRuntimeParameterFilled(sPackageName, sSelectThruDate, "SelectThruDate");
bParametersValid &= FormatDateTimeParameter(sPackageName, sSelectFromDate, "SelectFromDate", ref dtSelectDateFrom, provider);
bParametersValid &= FormatDateTimeParameter(sPackageName, sSelectThruDate, "SelectThruDate", ref dtSelectDateThru, provider);
if (!bParametersValid)
{
return;
}
if (dtSelectDateFrom > dtSelectDateThru)
{
Dts.Events.FireError(0,
sPackageName,
"SelectFromDate: " + sSelectFromDate + " is greater than SelectThruDate: " + sSelectThruDate,
"",
0);
return;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
// Validate current run date was specified
private bool ValidateRuntimeParameterFilled(string sPackageName,
string sParameterValue,
string sParameterName)
{
if (sParameterValue.Length == 0)
{
Dts.Events.FireError(0,
sPackageName,
string.Format("{0} was not specified", sParameterName),
"",
0);
return false;
}
return true;
}
// Formatting datetime input parameter
private bool FormatDateTimeParameter(string sPackageName,
string sParameterValue,
string sParameterName,
ref DateTime dtFormattedDateTime,
CultureInfo Culture)
{
if (sParameterValue.Length != 0)
{
try
{
dtFormattedDateTime = DateTime.ParseExact(sParameterValue, "yyyy-MM-dd HH:mm:ss", Culture);
}
catch (System.Exception e)
{
Dts.Events.FireError(0,
sPackageName,
string.Format("Exception occurred validating {0}: {1}, error: {2}",
sParameterName,
sParameterValue,
e.Message.ToString()),
"",
0);
return false;
}
}
return true;
}
}
}
