JG Vimalan's Blog

It is all about coding!

Connect to Sharepoint list via SSIS

I got a task where I need to pull data from a sharepoint list via SSIS package. By default, there is no provision to connect to sharepoint list. But, you can download the adapters from codeplex: http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

I have installed SSIS Sharepoint list adapters for SQL Server 2005. After you install the adapters, launch the SSIS BIDS. Create a new package. Open the Package window. Select the ‘Data Flow’ tab. In the left navigation, select the ‘Data Flow Sources’, right click and choose items. You will see a dialog window as shown below, where you can select the Sharepoint list source and destination adapters.

Now, after selecting the sharepoint list source and destination adapters, click on the Ok button. The adpaters will now appear on the toolbox as shown below,

thats all! now you can connect to sharepoint list via SSIS.

March 29, 2011 Posted by | SharePoint, SSIS 2005 | 1 Comment

Programmatically access the custom document library template

The following method finds the custom document library template in a sharepoint site.

 private Result GetSPDocLibTemplate(SPWeb spWeb, out SPListTemplate docLibTemplate)
            Result result = new Result();
            result.IsSuccess = false;
            docLibTemplate = null;

                SPListTemplateCollection listTemplateCollection = spWeb.Site.GetCustomListTemplates(spWeb);

                foreach (SPListTemplate template in listTemplateCollection)
                    if (template.Name.ToLower().Equals(“Expected Template Name”))
                        docLibTemplate = template;

                result.IsSuccess = true;
                return result;
            catch(Exception error)
                Log.ErrorFormat(“Error while searching for doc lib template: {0}”, error.Message);
                result.ErrorMessage = error.Message;
            return result;

September 27, 2010 Posted by | SharePoint | Leave a comment

Create a document library programmatically

This method creates a document library programmatically. The SPWeb object and
the name of the document library to be created are passed as parameter.

 private SPList CreateDocumentLibrary(SPWeb spWeb, string docLibName)
            SPListTemplate template = //Get template object you want to use.           

            Guid listId = spWeb.Lists.Add(

            SPList newDocLib = spWeb.Lists[listId];
            newDocLib.OnQuickLaunch = true; //show in quick launch

            return newDocLib;  // returns the newly created document library object       

September 27, 2010 Posted by | SharePoint | Leave a comment

Programmatically move items from one document library to another

The following code snippet explains the way to move items in one document library to another in a simple way.

I assume that, you got a SPListItem object.


SPListItem spItem = itemCollection[0];

SPFile srcFile = spWeb.GetFile(spItem.Url);

SPList destDocLib = spWeb.Lists[“MyDestDocLibName”];

srcFile.MoveTo(destDocLib+ “/” + srcFile.Name, true); 

The above code will move the document (item) from source document library to the destination.

September 27, 2010 Posted by | SharePoint | 1 Comment

Customize login in WSS 3.0/MOSS 2007 to redirect to a subsite site after login.

In this article we are going to see how to customize the login in WSS3.0/MOSS 2007 so that, when the user login in the parent site, they will be redirect to their respective sub site.

It is assumed that, you have forms authenication for your site and have multiple subsites.Earlier, I have noted the user login name and thier subsite urls in a database. So the concept is, a user can login only to his subsite. So, the basic idea is, a common url (parent url) provides a way to the users to login into their subsite.

See the image below for better understanding,



Browse to the path: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE. Locate the login.aspx file and open it in a development environment. I used VS.NET 2005.  

Step 2:   Add <%@ ImportNamespace = “System.Data,SqlClient”%> in the login.aspx  (This requires only if you are going to connect to a SQL Server database)

Step 3:   In the login control, include the OnLoggedIn event as shown below,  

<asp:login id=”login” OnLoggedIn=OnLoggedIn FailureText=………./>

Step 4:   Add the <script> tag between the namespace registration and the Content place holder and put some code as shown below,    

<script runat=server>

protected override void OnLoad(EventArgs e) 


base.OnLoad(e);   Page.Response.Write(this.Context.User.Identity.Name);  


void OnLoggedIn(object sender, EventArgs e) 


string connectionString = @”server=abc\SQLEXPRESS;database =         myDatabase;Integrated Security = SSPI”;     

using (SqlConnection conn = new SqlConnection(connectionString))  



SqlCommand cmd = new SqlCommand();

cmd.CommandText = “someProcedure”; 

cmd.CommandType = System.Data.CommandType.StoredProcedure;         cmd.Connection = conn;

SqlParameter param = new SqlParameter(“@UserName”, System.Data.SqlDbType.NChar, 256);        

param.Value = login.UserName; //Name in the login control text box                 


string userPortalName = Convert.ToString(cmd.ExecuteScalar());                 


if (!string.IsNullOrEmpty(userPortalName)) {            

string URL = “http://site/&#8221; + userPortalName.Trim() + “/default.aspx”;              Response.Redirect(URL);        




<asp:content ControlPlaceHolder………………/>

Step 5:  Key in the user name (who has access to a particular subsite) and password and login. 


Now, you will find that, the user is redirected to his subsite.   For example, if main url is, http://site/ then, he will be redirected to his subsite http://site/user10 . Now you have made a simple change in the login.aspx so that it behaves the way you like!!!

October 24, 2009 Posted by | SharePoint | 4 Comments

InputFormTextBox control in Custom WebPart

The following code snippet will give you an idea of using InputFormTextBox in custom webparts in SharePoint.

private InputFormTextBox _DescriptionTxtBox = null;

protected override void CreateChildControls()
            _DescriptionTxtBox = new InputFormTextBox();
            _DescriptionTxtBox.ID = “descriptionTxtBox”;
            _DescriptionTxtBox.TextMode = TextBoxMode.MultiLine;
            _DescriptionTxtBox.RichText = true;
            _DescriptionTxtBox.RichTextMode = SPRichTextMode.Compatible;
            _DescriptionTxtBox.Rows = 10;


//Add: Render the ‘_DescriptionTxtBox’

The output will be,


October 10, 2009 Posted by | SharePoint | 1 Comment