Basic SSIS Package

 

1.Open 'Business Intelligence Development Studio' comes with SQL 2005 or VS 2008 and create Integration Services project

2.Design .dtsx ( pakage file)

3.Our goal is to import .xlsx file data to SQL DB, log package status and if error occurs log error & send Email.

4.

Create connection managers like ADO.NET connection, OLEDB connection, and SMTP connection etc

OLEDB Connection



xlsx file-OLEDB connection

1.  
    



4. Drag & drop control flow items as shown here depends on the design. Create Variables as shown in the figure.

The SSIS package



 5. In the 'Data Flow' connect the source and destination connections through a data convertor.

6. Create an onError Event Handler, and create a 'Script Task'. Set the values like 'System::ErrorDescription' as   read only variable and 'User::errorMessages' as readwrite variable.

7. Click on Design Script & create code to capture the all errors into a Colloection.

8. Create 'Sequence Containers' as shown in the SSIS package and create 'Script Task' to capture all errors into a single string and insert to DB and send Email.

9. Click on Design Script
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
'Developed By : Rajesh Subbiah
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient
Imports System.Net
Imports System.Net.Mail
Public Class ScriptMain

Public Sub Main()
Dim errorDesc As String
Dim messages As Collections.ArrayList
Try
messages = CType(Dts.Variables("errorMessages").Value, Collections.ArrayList)
Catch ex As Exception
'If there is an exception – the object was never initialized, so there were no errors
Return
End Try
For Each errorDesc In messages
Dts.Variables("emailText").Value = Dts.Variables("emailText").Value.ToString + errorDesc + vbCrLf
Next

Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP_CONN_MAIL_SERVER").AcquireConnection(Dts.Transaction), String)
Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)
Dim htmlMessageFrom As String = Dts.Variables("emailFrom").Value.ToString()
Dim htmlMessageTo As String = Dts.Variables("emailTo").Value.ToString()
Dim msgErrors As String = Dts.Variables("emailText").Value.ToString()
Dim htmlMessageSubject As String = Dts.Variables("emailSubject").Value.ToString()
Dim htmlMessageBody As String = "
Warning ! : The following error occured while running the SSIS package
"
htmlMessageBody = htmlMessageBody + " " + msgErrors + "."
LogError(msgErrors) ' Log the error into DB
SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody, True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub

Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String,ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
If (SendTo.Trim() = "") Then SendTo = From
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim maSendTo As New MailAddress(SendTo)
Dim maFrom As New MailAddress(From)
htmlMessage = New MailMessage(maFrom, maSendTo)
htmlMessage.Subject = Subject
htmlMessage.Body = Body
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
htmlMessage.Dispose()
End Sub

Private Sub LogError(ByVal msgErrors As String)
Dim cm1 As ConnectionManager = Dts.Connections("ISTools_ADO.NET")
'Dim cm2 As ConnectionManager = Dts.Connections("ISTOOLS_OLEDB")

' For an ADO.Net ConnectionManager using a SqlClient providerDim sqlConn As System.Data.SqlClient.SqlConnection = DirectCast(cm1.AcquireConnection(Dts.Transaction), SqlConnection)
Dim oCmd As SqlCommand = New SqlCommand
oCmd.CommandType = Data.CommandType.Text
oCmd.CommandText = "INSERT INTO Log_SSIS_Status(PkgName,PkgStatus,IsSuccessful,Notes) VALUES('HScan-ImportFile','SSIS-Failure.Logged,Sent Email',0,'" + msgErrors + "')"
oCmd.Connection = sqlConn
'sqlConn.Open()
oCmd.ExecuteNonQuery()
'release the object finally
cm1.ReleaseConnection(sqlConn)
End Sub
End Class

10. The variables can be configured via a config file.

11. run in development environment.Set Break points to debug.

12.Deploy the package to the Server and view the job through 'Server Agent' in the management studio.







ASP.NET – JSON – Serialization and Deserialization

What is JSON?

JSON is another format of expressing data, just like XML. But, JSON is very simpler than XML, and tiny than XML. So, it is becoming popular in the web world to choose the JSON notation over XML since JSON notation are usually shorter, and less data to be transmitted if at all they were to be.

Article 1

Article 2  - using the DataContractJsonSerializer

ASP.NET -AJAX-JQUERY Video

Here

“Client-only Framework subset” in Visual Studio 2008

The .NET Framework Client Profile setup contains just those assemblies and files in the .NET Framework that are typically used for client application scenarios. For example: it includes Windows Forms, WPF, and WCF. It does not include ASP.NET and those libraries and components used primarily for server scenarios. Approx. 26MB in size, and it can be downloaded and installed much quicker than the full .NET Framework setup package. For more info Click here

AJAX WCF Services with JSON and XML

Sample given here
http://msdn.microsoft.com/en-us/library/bb472488.aspx

Ajax WCF Service using GET /POST Method

The WebGetAttribute attribute is applied to the Add operation to ensure that the service responds to HTTP GET requests. The code uses GET for simplicity (you can construct an HTTP GET request from any Web browser). You can also use GET to enable caching. We can also add the WebInvokeAttribute attribute to explicitly specify HTTP POST, or not specify an attribute, which defaults to HTTP POST.
[ServiceContract(Namespace = "MyAjaxService")]
public interface ICal
{ [OperationContract]
[WebGet]
double Add(double x1, double x2); }
You can create an AJAX endpoint on the service by using the webHTTPBinding standard binding and the enableWebScript behavior in the service configuration file.
The enableWebScript behavior sets the default data format for the service to JSON instead of XML as shown below.
to change the Serializer
[OperationContract]
[WebInvoke(ResponseFormat=WebMessageFormat.Xml)] //WebMessageFormat.JSon for Json Serializer
Public string HelloWCF()
{ Return “Hello WCF” }
For client follow any one of the technic
Add an ASP.NET AJAX Endpoint with out using Configuration
or
Use configuration to add an AJX Endpoint.

Hibernate and IBatis

Recently i have got a chance to work with these following DB middle-wares with .NET.
Hibernate is a powerful, high performance object/relational persistence and query service.It helps to develop persistent classes folowing object-oriented idiom including association, inheritance, polymorphism, composition and collections. Hibernate allows to express queries in it's own portable SQL extension (HOL) as well as in native SQL.
IBatis Data maper framework makes it easier to use a DB with .NET and Java applications.IBatis couples objects with stored procedures or SQL statements using XML descriptor.
IBatis
-simpler
-fast development time
-flexible
-much smaller in package size.
Hibernate
-generate SQL for us for means we no need to spend time on SQL
-provide much more advance cache
-scalable
For more details
http://ibatis.apache.org/
https://www.hibernate.org/

XslCompiledTransform and XslTransform

XslCompiledTransform and XslTransform
The .NET Framework 2.0 provides a new System.Xml.Xsl.XslCompiledTransform XSLT processor class, which is intended to replace the obsoleted XslTransform class. One of the major differences between the two is that while the latter is an XSLT interpreter, the former is a real XSLT compiler, allowing significantly faster execution times.
Sample code
xslt = new XslCompiledTransform();
xslt.Load(xslFile);
Does it mean XslCompiledTransform is always faster? Surprisingly, the answer is not that simple.
After doing some of my experiments, I came to the following conclusion
• If you are using one of the .NET Framework 2.0 XSLT processors (XslTransform or XslCompiledTransform), NGen'ing the System.Data.SqlXml assembly may improve the application start-up time.
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>ngen install "System.Data.SqlXml, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089" /nologo
Installing assembly System.Data.SqlXml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Compiling 1 assembly:
Compiling assembly System.Data.SqlXml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 ...
System.Data.SqlXml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
• Most of the times XslCompiledTransform.Load is slower than XslTransform.Load. But when you take concern about the over all performance XslCompiledTransform class is 4 times faster as MS says.
• While XslCompiledTransform is a best choice for the "one Load, many Transforms" scenario, it may be slow for the "one Load, one Transform" scenario, especially for very simple XML/XSLT files and when stylesheet templates are executed only few times. In those cases XslTransform may be faster.
• It is important, especially for server applications, to cache an XslCompiledTransform instance if the same stylesheet is likely to be executed again.

SubDomain in IIS

I assume that
A. you have a single IP Address to work with (say 1.2.3.4)
B. the default web site is www.yourdomain.com and is pointing to c:\websites\your.com\
C. DNS server setup.
1. Open IIS manager, and check the properties of the default web site.
2. in 'web site' pane, click 'advanced' button next to ipaddress.
3.Select the first item in the top box, click edit
4. set ipaddress = (for example) 1.2.3.4, port 80, and www.yourdomain.com as host header.
5. ok and close all property panes.
6. right click iis->computer->new->web site
7. click next, enter some description, next.
8. select ip address (eg) 1.2.3.4 (same as the default) port 80, host header name ="sub.mydomain.com". Use the default port 80. else you have to use www.yourdomain.com:portNO/
9. enter path (eg) C:\websites\yourdomain.com\sub\
10. finish and close property panes.
11. in your DNS server, add the hostname "sub.yourdomain.com" with ip the relevent address (eg) 1.2.3.4 (same as the default)once the dns propogates (depending on where you are, it may take up to a day or two) then you will find that http://sub.yourdomain.com will open your sub web site.

System.Net.Mail

The namespace
System.Net.Mail used to send email if you are using the 2.0 (or higher) .NET Framework.
Unlike System.Web.Mail, which was introduced in the 1.0 Framework, it is not built upon the CDO/CDOSYS libraries. Instead it is written from the ground up without any interop. Thus, it is not dependant upon other COM libraries.
What happens when we call the System.Net.Mail
First, and foremost, weneed the .NET Framework installed. Then you need to use the System.Net.Mail namespace to create and send email messages. Once you have programmatically set up your application, you will need a relay server to send email through. A relay server is a mail server, or a SMTP server/service, that can handle sending email. System.Net.Mail simply sends the mail to a relay server, and the relay server is responsible for delivering it to the final destination. System.Net.Mail can only send email. To read email you either need a Mime parsing component such as aspNetMime or a POP3 component such as aspNetPOP3.
Example code with GMail SMTP
System.Net.Mail.MailMessage mail = new System.Net.Mail.MailMessage();
System.Net.NetworkCredential cred = new System.Net.NetworkCredential("yourid@gmail.com", "yourpwd");
mail.To.Add(Rajesh@gmail.com);
mail.Subject = "subject";
mail.From = new System.Net.Mail.MailAddress(pillai.in@gmail.com);
mail.IsBodyHtml = true;mail.Body = "message";
System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("smtp.gmail.com");
smtp.UseDefaultCredentials = false;
smtp.Send(mail);

Working with large XML files in asp.net

When we work with larger XML files(2GB), if we use XMLDocument object which takes the whole document into the memore to traverse. It will create lot of performance issues in large environment.
So in such cases if we use XMLReader or XMLTextReader objects, for 2GB document will take only few KB's in memory.
Comparison from Microsoft

Load Balancing Web Applications

Some good articles on Load balancing
1.http://www.onjava.com/pub/a/onjava/2001/09/26/load.html
2.http://www.webtechniques.com/archives/1998/05/engelschall/

Visual Studio 2010 and .NET Framework 4.0 announced

Microsoft have provided the first look at the next version of its developer tools and platform, which will be named Visual Studio 2010 and the .NET Framework 4.0. Microsoft described the next release through the following five focus areas: riding the next-generation platform wave, inspiring developer delight, powering breakthrough departmental applications, enabling emerging trends such as cloud computing, and democratizing application life-cycle management (ALM).

Press Release

MVC-Model View Controller Pattern for ASP.NET

Model view controller is nothing but a design pattern used to achieve customizability in our application. Change is the only thing in the world, which will never change. All the products that we develop to our clients will undergo many changes. To accommodate these changes we should concentrate more on our design.

Good Layering Approach

MVC follows the most common approach of Layering. Layering is nothing but a logical split up of our code in to functions in different classes. This approach is well known and most accepted approach. The main advantage in this approach is re-usability of code. Good example of layering approach is UI, Business Logic, and Data Access layer. Now we need to think how we can extend this approach to give us another great advantage customizability. The answer to this is using Inheritance. Inheritance is one of the powerful concepts in oops. .Net supports this in a nice way.

View Layer

Our View /UI layer should only have UI related validations in it. We should not have any Business Logic in to it. This gives us the flexibility to change the UI at any time and we can also have different UI for different customers. We can even have web based UI for some of the clients.

Controller /Director

Controller is the layer, which responds to the events in the UI. For example Save button click on my employee master screen. This layer should act as an intermediate between our View and Model. Initially we may think that this layer is not necessary. I am also not convinced with this layer. But still we need to think. May be after some days we will get answer for this. This layer will act as just event redirector.

Model

This layer has all our business logic. This is the most important layer. This layer will have our core functionality. This layer should be designed in such a way that out core and complex logic should be designed as functions and these function should be marked as overridable so that the inheriting classes can re-use the logic or override the logic. We should not make all the functions in the layer as overridable this may raise some security threats. All the database operations should be done in the Model base class. All the inheriting classes should call this method to do database updates.

The Model-View-Controller and Model-View-Presenter Patterns

An example - Class view

Visual Studio 2008 -MVC- Template view

The ASP.NET MVC pattern interms of code is dramatically changed. Now, if we have Visual Studio.net 2008, the MVC project template is bundled with the software. For express editions we can download the templates [you must have Express editions with SP1] from Microsoft site. For more details http://www.asp.net/learn/mvc-videos/

Read large text files using C#

Read large text files using C#

Reading and manipulating all the individual lines of a text file in a For Each loop isn't difficult. For example you can load all the text lines in a string array, use the array in a loop, as following
using System.IO;
using System.Text.RegularExpressions;
StreamReader sr = new StreamReader(@"c:\test.txt");
string[] tempArr = Regex.Split(sr.ReadToEnd(), @"\r\n");
// don’t forger to do house keeping for stream object using try - finally
sr.Close();
foreach ( string str in tempArr)
Console.WriteLine(str);
This approach does not work in the case of large files. Because, the above code reads all the contents into the memory. For lager files (say 20MB or higher,) the program may crash or intensively slow depends on your processor / RAM speed. We can solve this memory problem and still use the For Each loop by creating our custom class TextFileReader class that implements the dotnet’s IEnumerable interface. The interface has only one method GetEnumerator, which is expected to return an instance of another class, which must implement the IEnumerator interface.
In dotnet 2.0 we can use IEnumerable interface
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
public class TextFileReader : IEnumerable, IDisposable
{ // The StreamReader object
StreamReader sr;
public TextFileReader(string path)
{ sr = new StreamReader(path); }
public void Dispose()
{ // close the file stream
if (sr != null)
{ sr.Close(); }
}
// the IEnumerable interface
public IEnumerator GetEnumerator()
{
while ( sr.Peek() != -1 )
{
yield return sr.ReadLine(); // The "yield" keyword is used to iterate through objects returned by a method.
It creates a state engine in IL so we can create methods that retain their state and we no need to
maintain the state in code.
}
Dispose();
}
IEnumerator IEnumerable.GetEnumerator()
{ return GetEnumerator(); }
}
We can use the TextFileReader object as followes.
TextFileReader TFR = new TextFileReader(@"c:\test.txt");
foreach (string str in TFR)
Console.WriteLine(str);
Happy coding.
-Rajesh Pillai