ASP.Net,SSIS,SSRS,SSAS,SQL Server 2005,SQL Server 2008 with my eyes

Clock

2011-10-14T17:26:00.000+05:30

For Loop Container in SSIS

2011-10-14T11:54:00.002+05:30

For Loop Container in SSIS allows to execute any other tool inside loop. for example we can download 10 files from FTP at one time. so we can include FTP task inside for loop container and can set loop to execute it 10 times.

Prepare SQL script of complete database in SQL Server

2011-10-14T11:22:00.002+05:30

Preparing script of database or prepare insert script for any table or all table is a common requirement in development.
SQL Server provide SQLPubWiz.exe for this purpose. it is generally available in C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\1.2 location.

it allows to prepare script for all objects of database which may include script for schema only, script for data only or both.

User can also enable drop for existing object of database.

Upload and Download files from Secure FTP using SSIS

2011-10-14T10:55:00.002+05:30

SSIS does not provide direct method of uploading and downloading of files using FTP task. So we need to do something different.
In my case i have used PSFTP.exe for same purpose. it is a third party tools which is freely available and allow to communicate with secure FTP.

Steps to achive solution.
1. download PSFTP.exe and save in your SSIS project folder like in resource folder.
2. use Execute process task to consume PSFTP.exe
3. in executable part select path of PSFTP.exe file like C:\SSIS\resource\psftp.exe
4. in argument part include SFTP settings with proper option like
domainname.ftp.com -P port number -l username -pw password -batch -b batchfilepath
5. in batch file path we can use any PSFTP command like get,put or mget for multiple file download.
6. in working directory assign path of download location of SFTP files.
7. click ok and execute package.

Files will be start to download on given working directory. we can customise pacakage with PSFTP command used in batch file.

Happy Coding.

Note: In case you still struggle with Secure FTP please mail me on ajitkumarthakur@hotmail.com

Steps to Schedule Job Packages

2010-07-05T19:57:00.001+05:30

Extracting DTS Packages:

1. Copy the folder ‘SSIS Packages’ to some physical location on the server (say C:).

Now, new jobs need to be created for the following DTS packages:

1. ScriptTask location like this
C:\ SSIS Packages\Energy_Metering_ScriptTask\Energy_Metering_ScriptTask_GetMeteredBuildingList.dtsx

Creating Jobs for each DTS package and scheduling

Job#1: ScriptTask

1. Open the Sql server management studio and connect to the OLAP database engine.
2. In the object explorer window, expand the ‘Sql Server Agent Node’. This shows some folders in the hierarchy (as shown in the following screen grab). Right click the Jobs node and select ‘New Job’ from the context menu.

This shows the New Job screen (as shown in the following screen grab). Create a new job with the defined owner name and category.

1. Now click ‘Steps’, this opens the following screen:

2. Click ‘New’ button to create a new step. This opens the step properties screen (as shown in the following screen). Enter the following information (as shown in the following screen grab):

Here, enter the following information
Step Name—Name of the step
Type --SQL Server Integration Services Packages
Run as -- SQL Agent Service Account
Package Source -- File System
Package – Select the path of dtsx package.

Now, click on the advanced tab and specify the following:

On Success Action--
Put Go To Next Step if more than one Job are scheduled.
Put Quit the Job reporting Success if only one Job is there.
On Failure Action--
Put Go To Next Step if more than one Job are scheduled.
Put Quit the Job reporting Failure if only one Job is there.

After making the above settings, click OK button

. Click on Schedules and selecting following option for the Daily scheduling -

Scheduled Type—Recurring
Frequency Occurs—Daily
Daily Frequency—Occurs every 5 minutes
This is shown in the following screen grab.

Click ‘Ok’ to complete scheduling.

Finally click ‘Ok’ in the new job screen to complete

Make Reporting easy using SSRS

2010-07-05T19:51:00.000+05:30

SSRS is BI tools which can be use to build report in .Net . One can use Report project of VS 2005 for developing same.

Try It!
To create a simple report using the Report Wizard, follow these steps:
1. Launch Business Intelligence Development Studio.
2. Select File -> New -> Project.
3. Select the Business Intelligence Projects project type.
4. Select the Report Server Project Wizard template.

5. Name the new report ProductReport1 and pick a convenient location to save it
in.
6. Click OK.
7. Read the first page of the Report Wizard and click Next.
8. Name the new data source AdventureWorksDS.
9. Click the Edit button.
10. Log on to your test server.
11. Select the AdventureWorks database.
12. Click OK.
13. Click the Credentials button.
14. Select Use Windows Authentication.
15. Click OK.
16. Check the Make This a Shared Data Source checkbox. This will make this
particular data source available to other Reporting Services applications in the
future.
17. Click Next.
18. Click the Query Builder button.
19. If the full query designer interface does not display by default, click the query
designer toolbar button at the far left end of the toolbar. Figure 18-2 shows the
full query designer interface.
20. Click the Add Table toolbar button.
21. Select the Product table and click Add.
22. Click Close.
23. Check the Name, ProductNumber, Color, and ListPrice columns.
24. Click OK.
25. Click Next.
26. Select the Tabular layout and click Next.
27. Move the Color column to the Group area, and the other three columns to the
Detail area
28. Click Next.
29. Select the Stepped layout and click Next.
30. Select the Ocean style and click Next.
31. Accept the default deployment location and click Next.
32. Name the report ProductReport1.
33. Check the Preview Report checkbox.
34. Click Finish

Why WCF ?

2010-07-05T19:47:00.000+05:30

WCF is playing wide role to enhance communication using .Net Technology.

use given URL http://www.programminghelp.com/programming/dotnet/wcf-creating-and-implementing-a-service-in-c/ to learn and making sample application using WCF.

Schedule SQL based Services

2010-07-05T19:38:00.000+05:30

To perform some Schedule based database activity you can use SQL Server Integration Services. Which comes with VS 2005 and SQL Server 2005.

It provides several tasks by which it enable to schedule any jobs based on SQL ,File System, Process,FTP etc.

One can also use Script Task to make dynamic Scheduling Using SSIS.

Some common task which i have been already implemented are
1. Script Task
2. File System Task
3. FTP Task
4. Send Mail Task
5. Transfer Database Task
6. Execute SQL Task
7. Backup Database Task
8. Transfer jobs Task
9. Web Service Task
10.Activex Script task
11.Bulk Insert Task
12.For Each Loop Container Task
13.For Loop Container Task

One can use www.sqlauthority.com to learn many more task list of SSIS.
Once these task is prepared as per requirement we can schedule it on database server easily.

How SQL Joins works

2010-07-05T19:36:00.000+05:30

All SQL join works on principle of venn digram. so have clear all diagram of venn diagram will have good catch up on SQL join.

to view diagram you can also use google images by typing venn diagram.

Best Random Number Generator using C#

2010-07-05T19:28:00.000+05:30

using System;

using System.Collections.Generic;

using System.Text;

namespace RandomNumberGeneration

{

///

/// Based on subtractive generalized Fibonacci sequence

///

/// see: http://www.shadlen.org/ichbin/random/generators.htm#knuth

///

public sealed class Knuth : RandomBase

{

#region Constructors

public Knuth() : this(Convert.ToInt32(DateTime.Now.Ticks & 0x000000007FFFFFFF)) { }

public Knuth(int seed)

: base(seed)

{

#region Declarations

int i, j, v, w;

#endregion

#region Initialization

m_x = new int[N];

v = seed;

w = 1;

#endregion

#region Execution

for (i = 0; i <>

{

j = (21 * i) % N; // randomize order a bit

m_x[j] = w;

w = v - w;

if (w <>

w = w + (m - 1);

v = m_x[j];

}

// set the pointers

m_p = N - 1;

m_q = N - M - 1;

// prime the pump

for (i = 0; i <>

{

v = Next();

}

#endregion

}

#endregion

#region Member Variables

private static readonly int N = 55;

private static readonly int M = 24;

private static readonly int m = 2147483647; // 2^31 - 1

private static readonly double mi = 1.0 / m;

private int[] m_x;

private int m_p, m_q;

#endregion

#region Methods

public override int Next()

{

#region Declarations

int y;

#endregion

#region Execution

if (m_p == N - 1)

{

m_p = 0;

}

else

{

(m_p)++;

}

if (m_q == N - 1)

{

m_q = 0;

}

else

{

(m_q)++;

}

y = m_x[m_p] - m_x[m_q];

if (y <>

{

y = y + (m - 1);

}

m_x[m_p] = y;

return y;

#endregion

}

#endregion

}

Remotely Redirect to any URL using .Net

2010-07-05T19:18:00.000+05:30

Use this class to remotely redirect to any URL. simply make instance of same and use post method for same it will redirect you on given URL.

namespace RemotePost

{

public class RemoteLogin

{

private System.Collections.Specialized.NameValueCollection Inputs = new System.Collections.Specialized.NameValueCollection();

public string Url = string.Empty;

public string Method = "post";

public string FormName = "form1";

public void Add(string name, string value)

{

Inputs.Add(name, value);

}

public void Post()

{

System.Web.HttpContext.Current.Response.Clear();

// System.Web.HttpContext.Current.Response.ContentType = "application/x-shockwave-flash";

// System.Web.HttpContext.Current.Response.Headers.Add("Accept-Encoding", "gzip");

System.Web.HttpContext.Current.Response.Write("");

System.Web.HttpContext.Current.Response.Write(string.Format("", FormName));

System.Web.HttpContext.Current.Response.Write(string.Format("

for (int i = 0; i <>

{

System.Web.HttpContext.Current.Response.Write(string.Format("", Inputs.Keys[i], Inputs[Inputs.Keys[i]]));

}

System.Web.HttpContext.Current.Response.Write("");

System.Web.HttpContext.Current.Response.End();

}

Javascript code to make AlwaysVisible Control

2009-04-15T00:17:00.001+05:30

Download file from server using c#

2009-04-15T00:00:00.000+05:30

public void DownloadFile()
{
Response.ContentType="application/ms-word";Response.AddHeader( "content-disposition","attachment; filename=download.doc");
FileStream sourceFile = new FileStream(@"F:downloadexample.doc", FileMode.Open);
long FileSize;
FileSize = sourceFile.Length;byte[] getContent = new byte[(int)FileSize];
sourceFile.Read(getContent, 0, (int)sourceFile.Length);
sourceFile.Close();
Response.BinaryWrite(getContent);
}

C# code to Read gmail inbox

2008-05-05T09:35:00.000+05:30

using System.Drawing;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Text;
using System.Threading;

public partial class EmailControl_inbox : System.Web.UI.Page
{
NetworkStream ns;
protected void Page_Load(object sender, EventArgs e)
{
TcpClient mailclient;
StreamReader sr;
StreamWriter sw;
try
{
mailclient = new TcpClient("mail.gmail.com", 110); //hostname,portname
}
catch (SocketException)
{
status.Text = "Unable to connect to server";
return;
}
ns = mailclient.GetStream();
sr = new StreamReader(ns);
sw = new StreamWriter(ns);
RequestForRead(ns,sr,sw);
}
public void Logout(NetworkStream ns, StreamReader sr, StreamWriter sw)
{
if (ns != null)
{
sw.Close();
sr.Close();
ns.Close();
//mailclient.Close();
}

}
public void ShowMessage(NetworkStream ns, string messnumber)
{
StreamReader Ssr = new StreamReader(ns);
StreamWriter Ssw = new StreamWriter(ns);
string response;
Ssw.WriteLine("retr " + messnumber);
Ssw.Flush();
response = Ssr.ReadLine();
while (true)
{
response = Ssr.ReadLine();
if (response == ".")
break;
display.Text += response + "\r\n";
}
}
public void RequestForRead(NetworkStream ns, StreamReader sr, StreamWriter sw)
{
string response;
string from = null;
string subject = null;
int totmessages;
response = sr.ReadLine();
sw.WriteLine("User " + Session["username"]);
sw.Flush();
response = sr.ReadLine();
if (response.Substring(0, 3) == "-ER")
{
status.Text = "Unable to log into server";
return;
}
sw.WriteLine("Pass " + Session["password"]);
sw.Flush();
try
{
response = sr.ReadLine();
}
catch (IOException)
{
status.Text = "Unable to log into server";
return;
}
if (response.Substring(0, 4) == "-ERR")
{
status.Text = "Unable to log into server";
return;
}
sw.WriteLine("stat");
sw.Flush();
response = sr.ReadLine();
string[] nummess = response.Split(' ');
totmessages = Convert.ToInt16(nummess[1]);
if (totmessages > 0)
{
status.Text = "you have " + totmessages + " messages";
}
else
{
status.Text = "You have no messages";
}
for (int i = 1; i <= totmessages; i++)
{
sw.WriteLine("top " + i + " 0");
sw.Flush();
response = sr.ReadLine();
while (true)
{
response = sr.ReadLine();
if (response == ".")
break;
if (response.Length > 4)
{
if (response.Substring(0, 5) == "From:")
from = response;
if (response.Substring(0, 8) == "Subject:")
subject = response;
}
}
messages.Items.Add(i + " " + from + " " + subject);
}

}
protected void selectmessage(object sender, EventArgs e)
{
string text =messages.SelectedItem.ToString();
string[] textarray = text.Split(' ');
ShowMessage(ns, textarray[0]);

}

}

c# code to handle dynamic command button

2008-05-05T09:22:00.000+05:30

protected void Page_Load(object sender, EventArgs e)
{
protected System.Web.UI.WebControls.Button btnTest;
string str;
str="A";
btnTest = new Button();
btnTest.ID = str;
btnTest.Text = str;
btnTest.Width=40;
btnTest.Height = 30;
Form.Controls.Add(btnTest);
btnTest.Click += new EventHandler(A_Click);
}
protected void A_Click(object sender,EventArgs e)
{
//handle operation
}

message box created in vb.net

2008-05-05T09:20:00.000+05:30

Public Sub ASP_MsgBox(ByVal Message As String)
System.Web.HttpContext.Current.Response.Write("")
End Sub

Export multiple gridview data in a excel file

2008-05-02T09:07:00.000+05:30

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
If GridView1.Rows.Count.ToString + 1 < 65536 Then
Dim tw As New StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim frm As HtmlForm = New HtmlForm()
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=" & "report.xls")
Response.Charset = ""
EnableViewState = False
Controls.Add(frm)
frm.Controls.Add(GridView1)
frm.Controls.Add(GridView2)
frm.Controls.Add(GridView3)
frm.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()
End If
End Sub

Reading pdf File

2008-05-01T18:23:00.000+05:30

FileStream ReadPdf = new FileStream(Server.MapPath("test.pdf"), FileMode.Open);
long FileSize;
FileSize = ReadPdf.Length;
byte[] Buffer = new byte[(int)FileSize];
ReadPdf.Read(Buffer, 0, (int)ReadPdf.Length);
for (int i = 0; i < buffer.count ; i++)
TextBox1.Text = TextBox1.Text + (char)Buffer[i];
ReadPdf.Close();