In Progress
Thursday, May 23, 2013
Monday, May 20, 2013
Script to send e-mail by tracking the package status using the Script Task in SSIS
{
string fromID = Dts.Variables["User::MailTo"].Value.ToString();
string toID = Dts.Variables["User::MailTo"].Value.ToString();
string subject = Dts.Variables["User::MailSubject"].Value.ToString();
string messageText = Dts.Variables["User::MailMessage"].Value.ToString();
string exchangeServerName = Dts.Variables["User::ExchangeServerName"].Value.ToString();
string packagestatus = Dts.Variables["User::PackageStatus"].Value.ToString();
string restoredata = Dts.Variables["User::RestoreDatabaseStatus"].Value.ToString();
string sPackageName = Dts.Variables["User::sPackageName"].Value.ToString();
string sPackageRunStartDate = Dts.Variables["User::sPackageRunStartDate"].Value.ToString();
string sPackageRunEndDate = Dts.Variables["User::sPackageRunEndDate"].Value.ToString();
string sPackageStatus = Dts.Variables["User::sPackageStatus"].Value.ToString();
string sJobName = Dts.Variables["User::JobName"].Value.ToString();
string sJobLastRunDate = Dts.Variables["User::JobLastRunDate"].Value.ToString();
string sJobLastRunTime = Dts.Variables["User::JobLastRunTime"].Value.ToString();
string sJobLastRunDuration = Dts.Variables["User::JobLastRunDuration"].Value.ToString();
string senderMailID = fromID;
MailAddress from = new MailAddress(senderMailID);
string receiverMailID = toID;
MailAddress to = new MailAddress(receiverMailID);
MailMessage msg = new MailMessage(from, to);
msg.Subject = subject;
StringBuilder mailString = new StringBuilder();
// mailString.AppendLine(messageText);
try
{
if ((sJobName == null && sPackageName == null) || (sJobName == "" && sPackageName == "" && sPackageStatus != "Completed") )
{
mailString.Append("<html><body><H3>" + messageText + "</h3>" + "</br>" + "LSDM20BackupRestore Status" + "</br>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>PackageName</td><td>LastRunStartDate</td><td>LastRunEndDate</td><td>PackageStaus</td></tr> <tr> <td border=2>" + sPackageName + "</td><td>" + sPackageRunStartDate + "</td><td>" + sPackageRunEndDate + "</td><td>" + sPackageStatus + "</td> </tr> </table></body></html>" + "</br>" + "LoadCultivationTrack Job Status" + "</br>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>PackageName</td><td>JobLastRunDate</td><td>JobLastRunTime</td><td>JobLastRunDuration</td></tr> <tr> <td border=2>" + sJobName + "</td><td>" + sJobLastRunDate + "</td><td>" + sJobLastRunTime + "</td><td>" + sJobLastRunDuration + "</td> </tr> </table></body></html>");
msg.IsBodyHtml = true;
msg.Body = mailString.ToString();
SmtpClient smtpClient = new SmtpClient(exchangeServerName);
smtpClient.Send(msg);
}
else if ((sJobName != null && sPackageName == null) || (sJobName != "" && sPackageName == "" ))
{
mailString.Append("<html><body><H3>" + messageText + "</h3>" + "</br>" + "LoadCultivationTrack Job Status" + "</br>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>PackageName</td><td>JobLastRunDate</td><td>JobLastRunTime</td><td>JobLastRunDuration</td></tr> <tr> <td border=2>" + sJobName + "</td><td>" + sJobLastRunDate + "</td><td>" + sJobLastRunTime + "</td><td>" + sJobLastRunDuration + "</td> </tr> </table></body></html>");
msg.IsBodyHtml = true;
msg.Body = mailString.ToString();
SmtpClient smtpClient = new SmtpClient(exchangeServerName);
smtpClient.Send(msg);
}
else if ((sJobName == null && sPackageName != null) || (sJobName == "" && sPackageName != "" ))
{
mailString.Append("<html><body><H3>" + messageText + "</h3>" + "</br>" + "LSDM20BackupRestore Status" + "</br>" + "<table border='1' width='100%'> <tr border=2 bgcolor='gray'><td border=2>PackageName</td><td>LastRunStartDate</td><td>LastRunEndDate</td><td>PackageStaus</td></tr> <tr> <td border=2>" + sPackageName + "</td><td>" + sPackageRunStartDate + "</td><td>" + sPackageRunEndDate + "</td><td>" + sPackageStatus + "</td> </tr> </table></body></html>");
msg.IsBodyHtml = true;
msg.Body = mailString.ToString();
SmtpClient smtpClient = new SmtpClient(exchangeServerName);
smtpClient.Send(msg);
}
}
catch (Exception)
{
//Log.Error(String.Format("Resetpassword.ascx.cs: There was an error during sending mail. Details: {0}.\n{1}", ex.Message, ex.StackTrace), new object());
}
finally
{
msg.Dispose();
}
}
Thursday, May 16, 2013
Entity Relationship Model (Diagram)
An entity-relationship diagram is a data modeling technique that creates a graphical representation of the entities, and the relationships between entities, within an information system.
The three main components of an ERD are:
1. One-to-One
One instance of an entity (A) is associated with one other instance of another entity (B). For example, in a database of employees, each employee name (A) is associated with only one social security number (B).
2. One-to-Many
One instance of an entity (A) is associated with zero, one or many instances of another entity (B), but for one instance of entity B there is only one instance of entity A. For example, for a company with all employees working in one building, the building name (A) is associated with many different employees (B), but those employees all share the same singular association with entity A.
3. Many-to-Many
One instance of an entity (A) is associated with one, zero or many instances of another entity (B), and one instance of entity B is associated with one, zero or many instances of entity A. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.
ERD-Visio Reference Site
http://msdn.microsoft.com/en-us/library/office/aa140264(v=office.10).aspx
http://techpanacea.blogspot.in/2008/07/how-to-reverse-engineer-er-diagram-from.html
The three main components of an ERD are:
1. One-to-One
One instance of an entity (A) is associated with one other instance of another entity (B). For example, in a database of employees, each employee name (A) is associated with only one social security number (B).
2. One-to-Many
One instance of an entity (A) is associated with zero, one or many instances of another entity (B), but for one instance of entity B there is only one instance of entity A. For example, for a company with all employees working in one building, the building name (A) is associated with many different employees (B), but those employees all share the same singular association with entity A.
3. Many-to-Many
One instance of an entity (A) is associated with one, zero or many instances of another entity (B), and one instance of entity B is associated with one, zero or many instances of entity A. For example, for a company in which all of its employees work on multiple projects, each instance of an employee (A) is associated with many instances of a project (B), and at the same time, each instance of a project (B) has multiple employees (A) associated with it.
ERD-Visio Reference Site
http://msdn.microsoft.com/en-us/library/office/aa140264(v=office.10).aspx
http://techpanacea.blogspot.in/2008/07/how-to-reverse-engineer-er-diagram-from.html
Script to calculate number sequence and insert into a table
Calculating number sequence
DECLARE @idt bigINT
SET @idt = 0
WHILE (@idt < 1000)
BEGIN
SELECT @idt = @idt + 1
insert into dbo.NumberSequence (number)
values (@idt)
END
Inserting into a table
DECLARE @idt bigINT
SET @idt = 0
WHILE (@idt < 1000)
BEGIN
SELECT @idt = @idt + 1
insert into dbo.NumberSequence (number)
values (@idt)
END
C# code to calculate check digit using DAMM Algorithm
C# code to calculate Damm Algorithm -- In Script transformation task (in DFT)
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string value = Row.sourcecodeid.ToString();
Int32 intValue;
if (Int32.TryParse(value, out intValue))
{
// mystring is an integer
int column = 0;
int[] intArray = new int[value.Length];
for (int i = 0; i < value.Length; i++)
{
intArray[i] = int.Parse(value[i].ToString());
}
for (int i = 0; i < intArray.Length; i++)
{
int row = intArray[i];
int newvalue = ReturnArrayValue(row, column);
column = newvalue;
if (i == intArray.Length - 1)
Row.checkdigit = column.ToString();
}
}
else
{
Row.checkdigit = value;
}
}
public int ReturnArrayValue(int column, int row)
{
int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 },
{1,7,5,0,9,8,3,4,2,6},
{6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
{9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
return myArray[row, column];
}
}
C# code to calculate Damm Algorithm -- In Script task (in control flow)
public void Main()
{
// TODO: Add your code here
string value = Dts.Variables["User::NumberSequence"].Value.ToString();
//checkDigit(val);
Int32 intValue;
if (Int32.TryParse(value, out intValue))
{
// mystring is an integer
int column = 0;
int[] intArray = new int[value.Length];
for (int i = 0; i < value.Length; i++)
{
intArray[i] = int.Parse(value[i].ToString());
}
for (int i = 0; i < intArray.Length; i++)
{
int row = intArray[i];
int newvalue = ReturnArrayValue(row, column);
column = newvalue;
if (i == intArray.Length - 1)
Dts.Variables["User::CheckDigitTest"].Value = column.ToString();
}
}
else
{
Dts.Variables["User::CheckDigitTest"].Value = value;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public int ReturnArrayValue(int column, int row)
{
int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 },
{1,7,5,0,9,8,3,4,2,6},
{6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
{9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
return myArray[row, column];
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string value = Row.sourcecodeid.ToString();
Int32 intValue;
if (Int32.TryParse(value, out intValue))
{
// mystring is an integer
int column = 0;
int[] intArray = new int[value.Length];
for (int i = 0; i < value.Length; i++)
{
intArray[i] = int.Parse(value[i].ToString());
}
for (int i = 0; i < intArray.Length; i++)
{
int row = intArray[i];
int newvalue = ReturnArrayValue(row, column);
column = newvalue;
if (i == intArray.Length - 1)
Row.checkdigit = column.ToString();
}
}
else
{
Row.checkdigit = value;
}
}
public int ReturnArrayValue(int column, int row)
{
int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 },
{1,7,5,0,9,8,3,4,2,6},
{6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
{9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
return myArray[row, column];
}
}
C# code to calculate Damm Algorithm -- In Script task (in control flow)
public void Main()
{
// TODO: Add your code here
string value = Dts.Variables["User::NumberSequence"].Value.ToString();
//checkDigit(val);
Int32 intValue;
if (Int32.TryParse(value, out intValue))
{
// mystring is an integer
int column = 0;
int[] intArray = new int[value.Length];
for (int i = 0; i < value.Length; i++)
{
intArray[i] = int.Parse(value[i].ToString());
}
for (int i = 0; i < intArray.Length; i++)
{
int row = intArray[i];
int newvalue = ReturnArrayValue(row, column);
column = newvalue;
if (i == intArray.Length - 1)
Dts.Variables["User::CheckDigitTest"].Value = column.ToString();
}
}
else
{
Dts.Variables["User::CheckDigitTest"].Value = value;
}
Dts.TaskResult = (int)ScriptResults.Success;
}
public int ReturnArrayValue(int column, int row)
{
int[,] myArray = new int[,] { { 0, 3, 1, 7 ,5, 9 ,8, 6, 4, 2 }, {7, 0, 9 ,2 ,1, 5, 4 ,8 ,6, 3 }, { 4,2,0,6,8,7,1,3,5,9 },
{1,7,5,0,9,8,3,4,2,6},
{6,1,2,3,0,4,5,9,7,8}, { 3,6,7,4,2,0,9,5,8,1 }, {5,8,6,9,7,2,0,1,3,4}, { 8,9,4,5,3,6,2,0,1,7},
{9,4,3,8,6,1,7,2,0,5 } ,{ 2,5,8,1,4,3,6,7,9,0} };
return myArray[row, column];
}
Check Digit Calculation
DECLARE @Temp1 INT
,@Temp2 INT
,@UPC VARCHAR(20)
,@CheckDigit INT;
SET @UPC = 547
SELECT @UPC = rtrim(ltrim(@UPC))
WHILE LEN(@UPC) < 11 SET @UPC = '0' + @UPC
SELECT @Temp1 = 0 +
SUBSTRING(@upc,1,1) +
SUBSTRING(@upc,3,1) +
SUBSTRING(@upc,5,1) +
SUBSTRING(@upc,7,1) +
SUBSTRING(@upc,9,1) +
SUBSTRING(@upc,11,1)
SELECT @Temp1 = @Temp1 * 3
SELECT @Temp2 = 0 +
SUBSTRING(@upc,2,1) +
SUBSTRING(@upc,4,1) +
SUBSTRING(@upc,6,1) +
SUBSTRING(@upc,8,1) +
SUBSTRING(@upc,10,1)
SELECT @Temp1 = @Temp1 + @Temp2
SELECT @Temp1 = (10- (@Temp1 % 10)) % 10
SELECT @CheckDigit = @Temp1
SELECT @CheckDigit
To pad zeros in left with the numerical value
SELECT EmpID
,REPLICATE('0',11 - LEN(EmpID))+ CAST(EmpID AS varchar ) AS EmpKey
FROM dbo.DimEmployee
Script to count rows in all tables in a database
, SCHEMA_NAME(t.schema_id) AS schema_name
, t.name AS table_name
, i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i
ON t.object_id = i.id AND i.indid < 2
Subscribe to:
Posts (Atom)