private string DecryptString(string input)
{
string retVal = default(string);
try
{
retVal = SSISCipherUtil.SSISInterceptor.Decrypt(input);
}
catch (SSISCipherUtil.InvalidEncryptionProviderException iEPEx)
{
retVal = input;
Dts.Log("SSISCipherUtil.dll: Returning non decrypted string as it is. Error Details - " + iEPEx.Message, 0, null);
}
catch (InvalidCastException)
{
Dts.Log("SSISCipherUtil.dll: An Error occurred while converting decrypted string to its original datatype. ", 0, null);
throw;
}
return(retVal);
}
/*
* The execution engine calls this method when the task executes.
* To access the object model, use the Dts property. Connections, variables, events,
* and logging features are available as members of the Dts property as shown in the following examples.
*
* To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
* To post a log entry, call Dts.Log("This is my log text", 999, null);
* To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
*
* To use the connections collection use something like the following:
* ConnectionManager cm = Dts.Connections.Add("OLEDB");
* cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
*
* Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
*
* To open Help, press F1.
*/
public void Main()
{
try
{
Dts.Log("Pocetak", 999, null);
string employeePath = Dts.Variables["User::EmployeePath"].Value.ToString();
RegistryKey rkey = Registry.LocalMachine.CreateSubKey(employeePath);
Dts.Log("Pre deklarisanja konekcije", 999, null);
//SqlConnection mySqlConn = new SqlConnection(Dts.Connections["ContactCenterStaging"].ConnectionString);
//MessageBox.Show(Dts.Variables["User::connContactCenterStaging"].Value.ToString());
//MessageBox.Show(Dts.Connections["ContactCenterStaging"].ConnectionString);
// SqlConnection mySqlConn = (SqlConnection)(Dts.Connections["ContactCenterStaging"].AcquireConnection(null));
SqlConnection mySqlConn = new SqlConnection(Dts.Variables["User::connADOContactCenterStaging"].Value.ToString());
Dts.Log("Posle kreiranja konekcije", 999, null);
String[] names = rkey.GetSubKeyNames();
MessageBox.Show(employeePath);
// Print the contents of the array to the console.
foreach (String s in names)
{
MessageBox.Show("1");
string user = s.ToString();
RegistryKey registry = rkey.OpenSubKey(s);
if (registry != null)
{
string employeeFirstName = registry.GetValue("givenName") != null ? ((string[])(registry.GetValue("givenName")))[0] : null;
string employeeLastName = registry.GetValue("surName") != null ? ((string[])(registry.GetValue("surName")))[0] : null;
string employeeAddress = registry.GetValue("streetAddress") != null ? ((string[])(registry.GetValue("streetAddress")))[0] : null;
string employeeCity = registry.GetValue("localityName") != null ? ((string[])(registry.GetValue("localityName")))[0] : null;
string employeeZipCode = registry.GetValue("postalCode") != null ? ((string[])(registry.GetValue("postalCode")))[0] : null;
string employeeCountry = registry.GetValue("country") != null ? ((string[])(registry.GetValue("country")))[0] : null;
string employeeTitle = registry.GetValue("title") != null ? ((string[])(registry.GetValue("title")))[0] : null;
string employeeDepartment = registry.GetValue("departmentName") != null ? ((string[])(registry.GetValue("departmentName")))[0] : null;
string employeeCompany = registry.GetValue("companyName") != null ? ((string[])(registry.GetValue("companyName")))[0] : null;
string employeeDisplayname = registry.GetValue("displayName") != null ? ((string[])(registry.GetValue("displayName")))[0] : null;
string employeeHomePhone1 = registry.GetValue("phoneHome1") != null ? ((string[])(registry.GetValue("phoneHome1")))[0] : null;
string employeeHomePhone2 = registry.GetValue("phoneHome2") != null ? ((string[])(registry.GetValue("phoneHome2")))[0] : null;
string employeeBusinessPhone1 = registry.GetValue("phoneBusiness1") != null ? ((string[])(registry.GetValue("phoneBusiness1")))[0] : null;
string employeeBusinessPhone2 = registry.GetValue("phoneBusiness2") != null ? ((string[])(registry.GetValue("phoneBusiness2")))[0] : null;
string employeeMobilePhone = registry.GetValue("phoneMobile") != null ? ((string[])(registry.GetValue("phoneMobile")))[0] : null;
string employeeFax = registry.GetValue("phonePrimaryFax") != null ? ((string[])(registry.GetValue("phonePrimaryFax")))[0] : null;
string employeeExtension = registry.GetValue("Extension") != null ? ((string[])(registry.GetValue("Extension")))[0] : null;
string employeeMailBoxUser = registry.GetValue("emailAddress") != null ? ((string[])(registry.GetValue("emailAddress")))[0] : null;
string employeePreferredLanguage = registry.GetValue("PreferredLanguage") != null ? ((string[])(registry.GetValue("PreferredLanguage")))[0] : null;
string employeeDefaultWorkStation = registry.GetValue("DefaultWorkstation") != null ? ((string[])(registry.GetValue("DefaultWorkstation")))[0] : null;
string employeeNTDomainUser = registry.GetValue("NTDomainUser") != null ? ((string[])(registry.GetValue("NTDomainUser")))[0] : null;
//SqlCommand myCommand = new SqlCommand("INSERT INTO dbo.EmployeeCurrent (EmployeeUserName, EmployeeFirstName,EmployeeLastName,EmployeeAddress,EmployeeCity,EmployeeZipCode,EmployeeCountry,EmployeeTitle,EmployeeDepartment,EmployeeCompany,EmployeeDisplayname,EmployeeHomePhone1,EmployeeHomePhone2,EmployeeBusinessPhone1,EmployeeBusinessPhone2,EmployeeMobilePhone,EmployeeFax,EmployeeExtension,EmployeeMailBoxUser,EmployeePreferredLanguage,EmployeeDefaultWorkStation,EmployeeNTDomainUser) Values ('" +
// user + "','" + givenName + "','" + surName + "','" + streetAddress + "','" + localityName + "','" + postalCode + "','" + country + "','" + title + "','" + departmentName + "','" + companyName + "','" + displayName + "','" + phoneHome1 + "','" + phoneHome2 + "','" + phoneBusiness1 + "','" + phoneBusiness2 + "','" + phoneMobile + "','" + phonePrimaryFax + "','" + Extension + "','" + emailAddress + "','" + PreferredLanguage + "','" + DefaultWorkstation + "','" + NTDomainUser + "')", mySqlConn);
String[] skills = (String[])registry.GetValue("Skills");
if (skills != null)
{
foreach (String g in skills)
{
SqlCommand myCommandSkill = new SqlCommand("dbo.insEmployeeSkillStage_Insert", mySqlConn);
myCommandSkill.CommandType = CommandType.StoredProcedure;
SqlParameter paramskill1 = new SqlParameter("@pEmployeeUserName", SqlDbType.NVarChar, 50);
paramskill1.Value = user;
SqlParameter paramskill2 = new SqlParameter("@pSkill", SqlDbType.NVarChar, 250);
paramskill2.Value = g;
myCommandSkill.Parameters.Add(paramskill1);
myCommandSkill.Parameters.Add(paramskill2);
mySqlConn.Open();
myCommandSkill.ExecuteNonQuery();
mySqlConn.Close();
}
}
SqlCommand myCommand = new SqlCommand("insEmployeeCurrent_Insert", mySqlConn);
myCommand.CommandType = CommandType.StoredProcedure;
// , , , @, @pEmployeeZipCode, @pEmployeeCountry, @pEmployeeTitle, @pEmployeeDepartment, @pEmployeeCompany, @pEmployeeUserName, @pEmployeeDisplayName, @pEmployeeHomePhone1, @pEmployeeHomePhone2, @pEmployeeBusinessPhone1, @pEmployeeBusinessPhone2, @pEmployeeMobilePhone, @pEmployeeFax, @pEmployeeExtension, @pEmployeeMailBoxUser, @pEmployeePreferredLanguage, @pEmployeeDefaultWorkStation, @pEmployeeNTDomainUser
SqlParameter param1 = new SqlParameter("@pEmployeeFirstName", SqlDbType.NVarChar, 80);
if (employeeFirstName != null)
{
param1.Value = employeeFirstName;
}
else
{
param1.Value = DBNull.Value;
}
myCommand.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@pEmployeeLastName", SqlDbType.NVarChar, 80);
if (employeeLastName != null)
{
param2.Value = employeeLastName;
}
else
{
param2.Value = DBNull.Value;
}
myCommand.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@pEmployeeAddress", SqlDbType.NVarChar, 80);
if (employeeAddress != null)
{
param3.Value = employeeAddress;
}
else
{
param3.Value = DBNull.Value;
}
myCommand.Parameters.Add(param3);
SqlParameter param4 = new SqlParameter("@pEmployeeCity", SqlDbType.NVarChar, 30);
if (employeeCity != null)
{
param4.Value = employeeCity;
}
else
{
param4.Value = DBNull.Value;
}
myCommand.Parameters.Add(param4);
SqlParameter param5 = new SqlParameter("@pEmployeeZipCode", SqlDbType.NVarChar, 15);
if (employeeZipCode != null)
{
param5.Value = employeeZipCode;
}
else
{
param5.Value = DBNull.Value;
}
myCommand.Parameters.Add(param5);
SqlParameter param6 = new SqlParameter("@pEmployeeCountry", SqlDbType.NVarChar, 50);
if (employeeCountry != null)
{
param6.Value = employeeCountry;
}
else
{
param6.Value = DBNull.Value;
}
myCommand.Parameters.Add(param6);
SqlParameter param7 = new SqlParameter("@pEmployeeTitle", SqlDbType.NVarChar, 20);
if (employeeTitle != null)
{
param7.Value = employeeTitle;
}
else
{
param7.Value = DBNull.Value;
}
myCommand.Parameters.Add(param7);
SqlParameter param8 = new SqlParameter("@pEmployeeDepartment", SqlDbType.NVarChar, 50);
if (employeeDepartment != null)
{
param8.Value = employeeDepartment;
}
else
{
param8.Value = DBNull.Value;
}
myCommand.Parameters.Add(param8);
SqlParameter param9 = new SqlParameter("@pEmployeeCompany", SqlDbType.NVarChar, 50);
if (employeeCompany != null)
{
param9.Value = employeeCompany;
}
else
{
param9.Value = DBNull.Value;
}
myCommand.Parameters.Add(param9);
SqlParameter param10 = new SqlParameter("@pEmployeeUserName", SqlDbType.NVarChar, 50);
if (user != null)
{
param10.Value = user;
}
else
{
param10.Value = DBNull.Value;
}
myCommand.Parameters.Add(param10);
SqlParameter param11 = new SqlParameter("@pEmployeeDisplayName", SqlDbType.NVarChar, 50);
if (employeeDisplayname != null)
{
param11.Value = employeeDisplayname;
}
else
{
param11.Value = DBNull.Value;
}
myCommand.Parameters.Add(param11);
SqlParameter param12 = new SqlParameter("@pEmployeeHomePhone1", SqlDbType.NVarChar, 25);
if (employeeHomePhone1 != null)
{
param12.Value = employeeHomePhone1;
}
else
{
param12.Value = DBNull.Value;
}
myCommand.Parameters.Add(param12);
SqlParameter param13 = new SqlParameter("@pEmployeeHomePhone2", SqlDbType.NVarChar, 25);
if (employeeHomePhone2 != null)
{
param13.Value = employeeHomePhone2;
}
else
{
param13.Value = DBNull.Value;
}
myCommand.Parameters.Add(param13);
SqlParameter param14 = new SqlParameter("@pEmployeeBusinessPhone1", SqlDbType.NVarChar, 25);
if (employeeBusinessPhone1 != null)
{
param14.Value = employeeBusinessPhone1;
}
else
{
param14.Value = DBNull.Value;
}
myCommand.Parameters.Add(param14);
SqlParameter param15 = new SqlParameter("@pEmployeeBusinessPhone2", SqlDbType.NVarChar, 25);
if (employeeBusinessPhone2 != null)
{
param15.Value = employeeBusinessPhone2;
}
else
{
param15.Value = DBNull.Value;
}
myCommand.Parameters.Add(param15);
SqlParameter param16 = new SqlParameter("@pEmployeeMobilePhone", SqlDbType.NVarChar, 30);
if (employeeMobilePhone != null)
{
param16.Value = employeeMobilePhone;
}
else
{
param16.Value = DBNull.Value;
}
myCommand.Parameters.Add(param16);
SqlParameter param17 = new SqlParameter("@pEmployeeFax", SqlDbType.NVarChar, 30);
if (employeeFax != null)
{
param17.Value = employeeFax;
}
else
{
param17.Value = DBNull.Value;
}
myCommand.Parameters.Add(param17);
SqlParameter param18 = new SqlParameter("@pEmployeeExtension", SqlDbType.NVarChar, 5);
if (employeeExtension != null)
{
param18.Value = employeeExtension;
}
else
{
param18.Value = DBNull.Value;
}
myCommand.Parameters.Add(param18);
SqlParameter param19 = new SqlParameter("@pEmployeeMailBoxUser", SqlDbType.NVarChar, 200);
if (employeeMailBoxUser != null)
{
param19.Value = employeeMailBoxUser;
}
else
{
param19.Value = DBNull.Value;
}
myCommand.Parameters.Add(param19);
SqlParameter param20 = new SqlParameter("@pEmployeePreferredLanguage", SqlDbType.NVarChar, 20);
if (employeePreferredLanguage != null)
{
param20.Value = employeePreferredLanguage;
}
else
{
param20.Value = DBNull.Value;
}
myCommand.Parameters.Add(param20);
SqlParameter param21 = new SqlParameter("@pEmployeeDefaultWorkStation", SqlDbType.NVarChar, 10);
if (employeeDefaultWorkStation != null)
{
param21.Value = employeeDefaultWorkStation;
}
else
{
param21.Value = DBNull.Value;
}
myCommand.Parameters.Add(param21);
SqlParameter param22 = new SqlParameter("@pEmployeeNTDomainUser", SqlDbType.NVarChar, 20);
if (employeeNTDomainUser != null)
{
param22.Value = employeeNTDomainUser;
}
else
{
param22.Value = DBNull.Value;
}
myCommand.Parameters.Add(param22);
Dts.Log("Pre otvaranja konekcije", 999, null);
mySqlConn.Open();
myCommand.ExecuteNonQuery();
mySqlConn.Close();
}
}
}
catch (Exception e)
{
throw e;
}
Dts.TaskResult = (int)ScriptResults.Success;
}