Excute SSIS package from .NET application

Fowlloing code is used to excute SSIS package from web or windows applications.


First we need to add reference Microsoft.SqlServer.ManagedDTS to the project ( Right click on References. Click on 'Add Reference'. It will pop up Add reference window. select Microsoft.SqlServer.ManagedDTS  under .NET components and click on OK).


HTML Source:


<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">


         <title>Excute SSIS package from .NET application</title>
</head>
<body>
          <form id="form1" runat="server">
                  <div>
                         <asp:Button ID="btnExcute" runat="server" OnClick="btnPackage_Click" Text="Excute Package" />
                   </div>
          </form>
</body>
</html> 


C# Code:


protected void btnPackage_Click(object sender, EventArgs e)

{
          string filename = "C:/Documents/Projects/TestPackage/bin/Deployment/test.dtsx";
          Application app = new Application();
          Package pckg = null;
          try
          {
                  pckg = app.LoadPackage(filename, null);
                  DTSExecResult result = pckg.Execute();
            }
           catch (Exception ex)
          {
                  throw ex;
           }
           finally
           {
                    pckg.Dispose();
            }
  } 

Search tables by using column name in SQL Server

Following query is used to find all tables and views which contain specific column name.

SELECT [TABLE NAME]=SYSOBJECTS.NAME,

[COLUMN NAME]=SYSCOLUMNS.NAME,
[DATA TYPE]=SYSTYPES.NAME,
[COLUMN LENGTH] = SYSCOLUMNS.LENGTH
FROM SYSOBJECTS
JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
JOIN SYSTYPES ON SYSCOLUMNS.XTYPE=SYSTYPES.XTYPE
WHERE SYSCOLUMNS.NAME ='COLUMN NAME'
 
You need to give column name

List of all Tables, Views and their columns in selected SQl database

Following query gives complete list of tables and views in a SQL database.

USE [DATABASE NAME]

GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
 
Following is the sample query to get column names and data types for selected table:
 
SELECT
COLUMN_NAME 'COLUMN NAME',
DATA_TYPE 'DATA TYPE',
COULMN_DEFAULT 'COLUMN DEFAULT'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TABLE NAME' 
 
 

Duplicate rows in a table

Following code is used to find duplicate rows in a Table, which doesn't have primary key. You can use either GROUP clause or HAVING clause to get all duplicate rows.

HAVING Clause:

SELECT COLUMN_NAME
FROM TABLENAME
GROUP BY COLUMN_NAME
HAVING COUNT(*) >1

GROUP BY Clause:

SELECT * FROM (
SELECT COLUMN_NAME, COUNT(COLUMN_NAME) AS CNT
FROM TABLENAME
GROUP BY COLUMN_NAME) AS A
WHERE CNT>1

Enable version settings in a SharePoint list

You can enable Version Settings for SharePoint lists so that they track versions of the items they contain. You can specify the number of versions to track and also view prior versions of items in the list. When version tracking is enabled for the list, each time the data in a row changes, Windows SharePoint Services creates a new version of that row.

To enable version tracking, you need the Manage Lists permission on the Windows SharePoint Services server. If you are not able to change the version settings, Check with your SharePoint Site adminstrator about your permisions.

1. Go to the List and click on Settings
2. On the Settings menu , click List Settings
3. Under General Settings, click Versioning settings.
4. On the List Versioning Settings page, in the Item Version History section, under Create a version each time you edit an item in this list?, click Yes.
5. To limit the number of versions to retain, select Keep the following number of versions, and then type the number of versions to retain.(If you limits the number of versions that it stores, the oldest versions are permanently deleted when the limit is reached. They are not sent to the Recycle, So need to make sure do you need old versions or not)

Show/Hide "Create Folder" in Lists in SharePoint

This article explains how to Show or Hide New Folder options in lists or documents in SharePoint.

When created a List(Lists can be documents, project task, e.t.c)  in the Sharepoint, by default it will give the option Create Folder under New task under that list. For hiding the Create New Folder option, First, you should be owner to the site and then follow the following steps..

1. Go to the List
2. Click on Settings
3. Select List Settings
4. Click on Advanced Settings, Which is under genaral setting
5. Check "NO" option for - Display "New Folder" command on the New menu?

Maintain Scroll Position in ASP.NET 2.0

This article explains, how to maintain current page scroll position across page postback events.

When working with large data in gridview, Scroll down the page and doing some thing that causes a postback, Then page automatically set back to the top position rather than staying at the current scroll position.

Smartnavigation (Smartnavigation = true) feature was using in .NET 1.X frame work. But It had only worked in IE browser.

It has been resolved in .NET 2.0 by using MaintainScrollPositionOnPostback page directive attribute.
This attribute can add to the pages in three ways.

1) Set Globally ( Set it in Web.Config in root level)

<system.web>
<pages theme="DefaultSkin" maintainScrollPositionOnPostBack="true" >
</pages>
</system.web>

2) Page Level Scrolling Mainatanance
In ASP.NET Page:

<%@ Page Language="C#" MaintainScrollPositionOnPostback="true" %>

3) Set in Programatically:
Import the Name space System.Web.UI, like in C#:

using System.Web.UI;

Set MaintainScrollPositionOnPostback true in Page load

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Page.MaintainScrollPositionOnPostBack = true;
}
}