private void ExecuteDTS(SqlConnection connection, string excelFilePath, string absoluteDTSFilePath)
{
string logFilePath = Path.Combine(_context.ExecutedPackage.PackagePath, Guid.NewGuid().ToString("N") + ".log");
_context.Log.AddLogInformation("Готовимся выполнить DTS-пакет '" + absoluteDTSFilePath + "'");
_context.Log.AddLogInformation("Лог выполнения будем сохранять в файл '" + logFilePath + "'");
Dictionary <string, string> connectionStringParts = SQLUtil.GetConnectionStringParts(connection.ConnectionString);
if (!connectionStringParts.ContainsKey("Password") || !connectionStringParts.ContainsKey("User ID"))
{
throw new ArgumentException("Строка подключения должна содержать логин и пароль пользователя БД");
}
_context.Log.AddLogInformation("Разбили строку подключения на части. Строка содержит параметры SQL-аутентификации.");
DTSExecutor executor = new DTSExecutor(absoluteDTSFilePath, logFilePath, false);
executor.SaveParameter("ServerName", connectionStringParts["Data Source"]);
executor.SaveParameter("DatabaseName", connectionStringParts["Initial Catalog"]);
executor.SaveParameter("UserLogin", connectionStringParts["User ID"]);
executor.SaveParameter("UserPassword", connectionStringParts["Password"]);
executor.SaveParameter("ExcelExtendedProperties", "Excel 8.0;HDR=1;");
executor.SaveParameter("ExcelFileName", excelFilePath);
_context.Log.AddLogInformation("Добавили DTS-параметры.");
executor.Execute();
_context.Log.AddLogInformation("DTS выполнен успешно");
}
private void ExecuteDTSPackages(SqlConnection connection, IExecutionContext context)
{
string dtsPath = CommonSP.GetDTSPath(connection);
_context.Log.AddLogInformation("DTSDirectoryPath = '" + dtsPath + "'");
bool absoluteLoadPick = _context.GetBoolFrom(LoadPick);
bool absoluteLoadHierarchy = _context.GetBoolFrom(LoadHierarchy);
bool absoluteUpdateDisplayCode = _context.GetBoolFrom(UpdateDisplayCode);
_context.Log.AddLogInformation("LoadPick, LoadHierarchy, UpdateDisplayCode = '" + absoluteLoadPick + ", " + absoluteLoadHierarchy + ", " + absoluteUpdateDisplayCode + "'");
string logFilePath = Path.Combine(_context.ExecutedPackage.PackagePath, Guid.NewGuid().ToString("N") + ".log");
_context.Log.AddLogInformation("logFilePath = '" + logFilePath + "'");
string absoluteEtalonConnectionCtring = _context.GetStringFrom(ConnectionStringEtalon);
_context.Log.AddLogInformation("EtalonConnectionCtring = '" + absoluteEtalonConnectionCtring + "'");
string absolutePackagePath = _context.GetStringFrom(PackagePath);
if (!absolutePackagePath.EndsWith(@"\"))
{
absolutePackagePath += @"\";
}
string[] _packList = SyncType == OSBBSynchronizationType.TextFile ? PackageList : PackageListNet;
foreach (string pack in _packList)
{
CheckForPendingCancel(context);
string packPath = Path.Combine(dtsPath, pack + ".dts");
_context.Log.AddLogInformation("Начинаем выполнение пакета " + packPath);
DTSExecutor executor = new DTSExecutor(packPath, logFilePath, false);
executor.OnLogger += new LoggerEvent(_context.Log.AddLogEvent);
executor.OnProgress += new ProgressEvent(executor_OnProgress);
_context.Log.AddLogInformation("Создали экземпляр DTSExecutor");
var connectionStringParts = SQLUtil.GetConnectionStringParts(connection.ConnectionString);
var etalonConnectionStringParts = SQLUtil.GetConnectionStringParts(absoluteEtalonConnectionCtring);
_context.Log.AddLogInformation("Разбили на части обе ConnectionStrings");
executor.SaveParameter("DataSource", connectionStringParts["Data Source"]);
executor.SaveParameter("Catalog", connectionStringParts["Initial Catalog"]);
executor.SaveParameter("UserID", connectionStringParts["User ID"]);
executor.SaveParameter("Password", connectionStringParts["Password"]);
executor.SaveParameter("DataSource_S", SyncType == OSBBSynchronizationType.EtalonDB ? etalonConnectionStringParts["DataSource"] : "");
executor.SaveParameter("Catalog_S", SyncType == OSBBSynchronizationType.EtalonDB ? etalonConnectionStringParts["Initial Catalog"] : "");
executor.SaveParameter("UserID_S", SyncType == OSBBSynchronizationType.EtalonDB ? etalonConnectionStringParts["UserID"] : "");
executor.SaveParameter("Password_S", SyncType == OSBBSynchronizationType.EtalonDB ? etalonConnectionStringParts["Password"] : "");
executor.SaveParameter("FilePath", SyncType == OSBBSynchronizationType.TextFile ? absolutePackagePath : "");
executor.SaveParameter("Pick", absoluteLoadPick ? 1 : 4);
executor.SaveParameter("Hierarchy", absoluteLoadHierarchy ? 1 : 4);
executor.SaveParameter("UpdateDysplayCodeOfCatalogs", absoluteUpdateDisplayCode ? 1 : 0);
_context.Log.AddLogInformation("Почти начали выполнять DTSExecutor.Execute()");
CommonSP.rp_SetDBStatus(connection, 1);
executor.Execute();
CommonSP.rp_SetDBStatus(connection, 0);
_context.Log.AddLogInformation("Выполнили DTSExecutor.Execute()");
}
}
public void TestGetConnectionStringParts()
{
const string connString =
"Data Source=SDB14;Initial Catalog=TEST_2008;Persist Security Info=True;User ID=sa;Password=osbb";
var result = SQLUtil.GetConnectionStringParts(connString);
Assert.IsNotNull(result);
Assert.AreEqual(result.Count, 5);
Assert.IsTrue(result.ContainsKey("Persist Security Info"));
Assert.AreEqual(result["Persist Security Info"], "True");
}