?>

 03 Mar 2010 @ 3:00 PM 

For those Zen Cart fans out there that have previously set up a Zen Cart store and want to save a load of time for when you need to configure a new one, you can try following some of the steps outlined in this post.  I know it has saved me personally hours and hours of configuring a new store.

Duplicating the store and database in a few easy steps: -There was an instance where we needed to quickly create another instance of the exact same store with a separate database, and separate folder structure for the store.

  • Duplicate the database–The easiest way to do this is to open the PHPMyAdmin interface and create a blank database you intend to use for the new store.  For simplicity’s sake of this example we’re going to keep the same naming convention of the new folder we need  to  create,  and the database name.  So, with that in mind we’ll call the new database “new_store”.There are two primary ways I’ve found to duplicate the original database.  Obviously, you can export the existing database to a flat file using the PHPMyAdmin tools, and then import the file into the new database, which is probably least amount of work.  The new way I discovered in Windows is to copy the contents of the old database folder to the newly created database folder.  There will be one file in the new database folder (new_store) which you can move somewhere else or just overwrite.
  • Create a new folder- Within the wwwroot folder (sometimes /var/www if hosting on Linux or c:\xampp\htdocs if running on Windows, create a new folder along side of the existing folder called “new_store” (or what ever you choose to name the new folder).  In our case the original store was in the default folder called “store”, which is in the root of the web folder.
  • Copy the contents – Now from the original store folder, copy the contents and place them into the new folder.  On Windows platforms this is a simple copy and paste if you have full control of the server, or in the case of a hosted solution or Linux server, you can download the content via FTP and then upload to the new folder.
  • Set the permissions– On Windows, there should be no changes needed, however you will need to make 2 configuration files writeable to alter the database path and store path in a later step.  The locations of the 2 files are: \new_store\includes\configure.php\new_store\admin\includes\configure.php

    On Linux servers, you can connect via FTP and set the permissions for both files to be 777.  Also make the \new_store\images folder 777 as well.  In a windows environment, you will right click on the configure.php files and uncheck the “read-only” attribute.
  • Modify the configuration files– Open each of the configure.php files in a suitable editor.  I prefer to use something like Dreamweaver or any other editor capable of recognizing php code.  If using Windows, notepad will work, however it will not be formatted nicely and make it harder to find some of the tags needed for editing.  Once the files are open for editing you will want to modify the following lines of code to show the new path to the store and the database:In the “\new_store\includes\configure.php” file:

define(’HTTP_SERVER’, ‘http://<your_domain_here>’);
define(’HTTPS_SERVER’, ‘http://<your_domain_here>’);

define(’DIR_WS_CATALOG’, ‘/new_store/’);
define(’DIR_WS_HTTPS_CATALOG’, ‘/new_store/’);

define(’DIR_FS_CATALOG’, ‘/var/www/new_store/’);

define(’DB_SERVER_USERNAME’, ‘<Your DB_User>’);
define(’DB_SERVER_PASSWORD’, ‘<Your_Password>’);
define(’DB_DATABASE’, ‘new_store’);

In the “\new_store\admin\includes\configure.php” file:

define(’HTTP_SERVER’, ‘http://<your_domain_here>’);
define(’HTTPS_SERVER’, ‘http://<your_domain_here>’);
define(’HTTP_CATALOG_SERVER’, ‘http://<your_domain_here>’);
define(’HTTPS_CATALOG_SERVER’, ”http://<your_domain_here>’);

define(’DIR_WS_ADMIN’, ‘/new_store/admin/’);
define(’DIR_WS_CATALOG’, ‘/new_store/’);
define(’DIR_WS_HTTPS_ADMIN’, ‘/new_store/admin/’);
define(’DIR_WS_HTTPS_CATALOG’, ‘/new_store/’);

define(’DIR_FS_ADMIN’, ‘/var/www/new_store/admin/’);
define(’DIR_FS_CATALOG’, ‘/var/www/new_store/’);

define(’DB_SERVER_USERNAME’, ‘<Your DB_User>’);
define(’DB_SERVER_PASSWORD’, ‘<Your_Password>’);
define(’DB_DATABASE’, ‘new_store’);

Once the changes are saved to these 2 files, you will want to either copy them up to the server if on Windows, or FTP them if on Linux to their respective directories.

  • Set Security – Now that the changes have been made and saved, the files need to be made un-writable (read-only).  On Linux, using an FTP client set the permissions on these 2 files to 444.  In Windows, right click on the files and then place a check in the “read-only” box and apply.
  • Ready to go! – After having done all these changes, you should now be able to run your new store by going to http://yourdomain.com/new_store.  If everything worked properly, you should see the store as it was in the old URL path, however it is a distinct new database that is separate and disjoined from the original.  You should now make a small test to confirm you are indeed working with the new database.  Login to the Admin interface at http://yourdomain.com/new_store/admin .  From there, make a small tweak to a test user account and verify that this change did not affect the original store.  This will indeed confirm if you are running a separate and unique store that is a duplicate or the original. One semi-critical thing to keep in mind after the new store has been set up, is to double check any side boxes , banners or footers for links that lead back to the original site.  You can make these changes in the Admin area (typically under  Tools > Banner Manager).
Tags Categories: Zen-Cart Posted By: Jose Negron
Last Edit: 03 Mar 2010 @ 03 00 PM

E-mailPermalinkComments (1)

This post applies to PerformancePoint 2007

PeformancePoint 2007 is Microsoft’s Business Intelligence Environment that tightly integrates with Microsoft Office SharePoint Server 2007 (MOSS). This product is very difficult to get a stable installation working, so I decided to add a couple of comments here that helped me as I attempted to dodge all the road blocks involved in the install process. First of all, I guess the good news is that Microsoft has decided to integrate this product with their MOSS 2010 product so a separate install will no longer be needed. I’m not sure if this is a good thing or a bad thing yet.

Here are some things that will help:

  • Use MS SQL Server 2005 SP 2 or MS SQL Server 2008 as your database install
  • I recommend you use an NT Service account with elevated privileges to the database server. In fact, I recommend you do the entire installation under a service account and setup everything with the service account as the primary administrator of all services.
  • Before installing any of the PerformancePoint 2007 services, make sure you have ASP.NET 2.0 AJAX extensions 1.0 found here:
    Click Here
  • Before installing any of the PerformancePoint 2007 services, make sure you have ADOMD.NET 9.0 (SP 2) installed found here: Click Here
  • Make sure you upgrade your installed services to PerformancePoint 2007 SP2 (as of the time of this post – this is my best recommendation)
  • If you do not install PerformancePoint 2007 SP2 you will notice that certain services will be unavailable for install during the installation wizards. Example: Planning Process Service and Planning Web Service will not be available during the installation of PerformancePoint Planning.

Once you are able to successfully connect to a data source with PerformancePoint Designer, you will be able to develop dashboards and post them to MOSS 2007 Document libraries or Reporting repositories. I recommend you using the Enterprise Reporting site template.

Also, if you are unable to connect to a SharePoint List with PerformancePoint Dashboard, you will most likely need to give the PerformancePoint Service Account elevated privileges to the SharePoint Site and List. This service account will be the account used by IIS Application Pool that was created by PerformancePoint during the Install (found in the Identity tab).

DO NOT:

Install .NET Framework 3.5 before installing PeformancePoint Monitoring. After PeformancePoint Monitoring is installed, then install .NET Framework 3.5

Hope this post helps someone. This is a very frustrating product to get working well. More posts to follow with details on the usage of PerformancePoint.

Tags Categories: Uncategorized Posted By: Clinton Daniel
Last Edit: 22 Dec 2009 @ 09 03 AM

E-mailPermalinkComments (0)

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Over the past 2 years I have implemented numerous SharePoint solutions to automate business processes. I’ve learned to improve upon certain design strategies each time. It seems as though most of the time, business owners have learned that SharePoint can be used to track information as it flows from one location within the organization to another. In most cases, the information is going through some type of approval process. With the birth of SharePoint 2007/WSS 3.0, we have been able to leverage Workflows within SharePoint to pass information through some type of simple or complicated business process. After some time, I just threw away most of the out of the box SharePoint workflows and task lists. As your organization begins to demand your services using SharePoint, your requirements will get extremely complicated.

Dashboards in my world are usually associated with analysis systems which render results from some sort of aggregated data or cube. This type of dashboard usage is usually associated with business intelligence. However, in my experience dashboards are also useful for tracking the state of data as it goes through a transactional process.

There are a couple of things which have helped me build nice dashboards using SharePoint to track where the state of information is at in a business process. Most of my work is done in SharePoint Designer when creating the Dashboard.

  • I usually grab some of the images from the 12 hive that are commonly used by SharePoint services to build KPI lists. Take a look at the 12 hive and you will find all kinds of useful stop lights and other dashboard-like images.
  • I create a custom .aspx page with SharePoint designer, then insert a Data view. Once I have inserted the Data view I view the Source of a local list. Then I insert the fields I want as a Multi Item View.
  • Now that I have a Multi Item View on my custom .aspx page, I can add images and apply conditional formatting to “Show” the images based on the specific state of the data.

More on how I work with the “state” of the data:

SharePoint can present quite a challenge when you are trying to track the state of data. What I usually do in situations where the solution is difficult to solve is I create additional fields in the list that will serve as switches. Basically, when a specific state has been met in the business process I will design a workflow that changes the data in the “switch” field to another value. For instance, if “Business Owner X” has approved some type of information then change the “Switch X” field from 0 to 1. Then the next time a transaction is made on the system, you can test “Switch X” to see if it has already been executed and prevent a repeat of the process. You can also use this technique to avoid the workflow from executing an infinite loop. Now that you have a switch field that has data within a specific state, you can test your conditional formatting of the image on the dashboard for this specific state.

I know that this blog post is not very visual, but it may help someone.

Tags Categories: Uncategorized Posted By: Clinton Daniel
Last Edit: 31 Oct 2009 @ 07 08 PM

E-mailPermalinkComments (0)

The Post applies to WSS 3.0 and MOSS 2007.

The fundamental building block of data in SharePoint is the List. Once you begin to develop an application in SharePoint, you will quickly begin to realize that almost all objects at some point are built off of or touch a SharePoint list. Although its data types are limited, it can be engineered to work somewhat like a table within SharePoint. The SharePoint List has its limitations, however I think it is important to explain how it can be used “similar” to a SQL table. Let’s examine a common task that you would typically perform with a SQL Table vs. SharePoint List.

How about a JOIN?

Hmmm.. Well, in a SQL Table you can easily establish a Foreign key relationship with one table against another so that you can query across the two of them. But what about a SharePoint List? Lets take a look at this.

Scenario: You have two SharePoint lists and both of them have a similar unique field in common. You want to be able to update data in one list based upon a match from another.

Solution: This can easily be solved by Building a Custom Workflow in SharePoint Designer. In SharePoint Designer, create a new workflow on List “A” that starts whenever an item is changed. In the workflow choose an Activity called “Update List Item”. In the “Update List Item” dialog choose the List “B” from the drop-down that you want to update data. Now choose a field that you want to update some information on when the item is updated. Finally, notice the “Find the List Item” section below. This is where you will do the join on the common unique field between the two lists. In the Field section – choose the common unique field from List “A”. In the Value section – choose the common unique field from List “B”. Now click “OK”. You will probably get a warning stating that there is a risk that the fields are not unique. So you want to make sure that the fields are absolutely unique. Just agree to the warning. Now install (Finish) the workflow. You when you change an item in List “A”, you will see another field update in List “B” where the common unique match is met between the lists.

Although this works, I’m not entirely sure how it will begin to perform when the list grows to a high volume of data. When performing updates on a JOIN using SQL tables, you would typically implement optimization strategies using various index techniques. I’m not entirely sure if SharePoint considers optimization when using this technique.

Tags Categories: SharePoint Posted By: Clinton Daniel
Last Edit: 01 Sep 2009 @ 05 20 AM

E-mailPermalinkComments (2)

This post applies to Microsoft Office SharePoint Server 2007, WSS 3.0, and JavaScript

Scenario: You are tasked to build a custom List for your project that will need a Custom .aspx page to display the data by a user. You will build the Custom .aspx page using SharePoint Designer to create a Custom List Form that is set to use the “Display item form” option. This option creates a WebPart that utilizes the default Rendering Templates found in SharePoint’s 12 Hive. When viewing the custom .aspx page the WebPart displays a “Close” button above and below the form. Your design requires that you remove these close buttons from the view. For whatever reason, you are also not given access to the SharePoint server’s 12 Hive to build a custom defaulttemplates.ascx file which could be used to fix your button situation. So.. how do you fix this problem.

Solution: In most cases, you will find the solution to this problem by creating a custom defaulttemplates.ascx page in the 12 Hive as mentioned above. All rendering templates for the lists are defined in the defaulttemplates.ascx file located in the 12 Hive: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\CONTROLTEMPLATES. There are many blogs referencing this technique. However, I had to solve this problem and did not find anything about it which described how it could be accomplished using JavaScript. I’m sure there is a blog out there somewhere telling you how to do this, but I couldn’t find it.

Here is my answer:

First, you need to create the custom .aspx page in SharePoint Designer:

  • Using SharePoint Designer – File –> New –> ASPX
  • NEXT – Insert –> SharePoint Controls –> Custom List Form.
  • In the “List or document Library Form dialog”
  • Choose from the drop-down which list you would like to display your data from. Make sure “Item” is listed (If you choose a List) in the “Content type to use for form” section.
  • Choose “Display item form (used to view list items). Uncheck the “Show standard toolbar” checkbox.
  • This inserts a DataFormWebPart with the default “Close” button above and below the WebPart.
  • Now Save the custom ASPX page so that you can view it in a browser. You will need to get some information as the Webpart is displayed in the browser.

While the new ASPX page is displayed in the browser, use the “View Source” option supplied by your to view the code rendered to the browser. Do a “Find” for the word “Close” to locate the “Close” button in the source. The default SharePoint Display Form will create two close buttons that have two different IDs. Copy the ID of both buttons to a text editor to be used later.

Now go back to SharePoint Designer and edit the newly created ASPX page again. Change SharePoint Designer to “Code” view so that you can edit the code generated behind the page.

Locate the following tags at the end of the WebPart:

</WebPart>
</WebPartPages:ListFormWebPart>
</ZoneTemplate></WebPartPages:WebPartZone>

Now paste the following code after the above tags and before the </asp:Content> tag:

<!– Custom JavaScript to hide the Close Button –>

<script language=”javascript” type=”text/javascript”>
document.getElementById(’ctl00_m_g_70071543_6580_408e_9e3e_d1891effd863_ctl00_toolBarTbl_RightRptControls_ctl01_ctl00_diidIOGoBack’).style.display = ‘none’;
document.getElementById(’ctl00_m_g_70071543_6580_408e_9e3e_d1891effd863_ctl00_toolBarTbltop_RightRptControls_ctl02_ctl00_diidIOGoBack’).style.display = ‘none’;
</script>

In the above JavaScript, you will need to substitute your Button IDs with the getElementByID definitions. You will have 2.

Now Save your .aspx page in SharePoint Designer. Navigate back to the custom .aspx page in your Browser and Refresh the page. You should now see the “Close” buttons Disappear!

You should be able to use the above technique with other form elements (”Save” or “Cancel”) if needed.

Tags Categories: JavaScript, SharePoint Posted By: Clinton Daniel
Last Edit: 03 Jul 2009 @ 05 46 PM

E-mailPermalinkComments (0)

This post applies to Microsoft Office SharePoint Server 2007, WSS 3.0, Adobe LiveCycle, and PDF Forms

Scenario: Many organizations use Adobe PDF forms for employees to submit organizational forms for various business processes. It’s easy to configure these forms to submit the data in an e-mail message or send it to a SQL Server database. Submitting data is usually not the only thing required in the business process of the form. Once the data gets submitted via e-mail or database, there is usually some type of workflow involved in the business process. Adobe has a product called Adobe LiveCylcle ES that has a workflow engine that can be used to process information within an organization via Adobe Forms. Adobe LiveCycle ES is an incredible product but it is very expensive and you may not have it available within your organization. As an alternative, it is possible to post data from an Adobe PDF form and submit it to a SharePoint List. It is actually a very easy process. So….. How do you solve this?

Solution: First of all, I’m not sure why – but there is not much to find out there on this problem. I’m not sure exactly why. If you learn this technique, you could easily create very dynamic forms using Adobe LiveCycle Designer that can meet difficult requirements and leverage the use of SharePoint for data storage and Workflow processes. This Adobe blog post helped me with this process: Click Here to Read an Adobe Blog Post

You could easily read the post from the link above, so I’m going to explain it to you from my perspective.

Basically, to get Adobe PDF forms to communicate with a SharePoint List you need a 3 layer architecture.

LAYER 1: Adobe Form with a Submit button that is configured to submit data as XML and send the XML data to the http path of an .ashx file that is stored in the 12 hive (or another location) on a SharePoint server

LAYER 2: An .ashx file is created using Visual Studio  (or any text editor) and stored in the 12 hive (C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Whatever_You_Want_To_Call_This_Subdirectory) of the SharePoint Server.

LAYER 3: A Custom SharePoint List is created that has fields that match the fields of the Adobe Form.

Now I will explain each of these 3 Layers in more detail.

LAYER 1 (The Adobe Form):

First, you will want to use Adobe LiveCycle Designer to design the fields for your form. Just create a simple Text field called “Title” and a “Submit” button on the form. Then configure the Submit button to submit the data as XML. When configuring the submit button for XML, it will ask you for a desination URL to post your data. This is where you will paste the http: path from SharePoint to locate the .ashx file.  If you stored the file in a subdirectory of the LAYOUTS directory of the 12 hive, it would be something like:  http://servername/sites/sitecollectionname/_layouts/whatever_subdirectory/whatever.ashx

Now your form is prepared for data submission to a SharePoint List using an HttpHandler. Now lets keep going here.

LAYER 2 (The HTTPHandler – .ashx file)

You will need to create an HTTPHandler (.ashx) file with Visual Studio to handle accepting XML data from the Adobe PDF form and send it to the SharePoint List. The HTTPHandler will basically be used as the “middle man” to handle the exchange of the data. The file can be stored in the 12 hive of the SharePoint server so that it can be visible by all site collections within the SharePoint Farm. To use the 12 hive, you will need to create a subdirectory in the following path:  C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\Whatever_Subdirectory

You could easily just store .ashx file in the LAYOUTS directory, but its best practice not to do this so that you don’t risk replacing a file with the same name which could cause your SharePoint farm to become unstable.

The easiest way to create an .ashx file in Visual Studio is just to create a Web Application project using C#/ASP.NET. Then add a “New Item” as a basical HTML page. Then just rename the HTML Page with a .ashx file extension. There are other ways but this is easy enough.

Now let’s take a look at the anatomy of this .ashx file. First of all, you will be programming against SharePoint Services to get the data to the SharePoint list. So let’s look at a sample ASP.NET/C# script:

CODE ::
<%@ Assembly Name=”Microsoft.SharePoint,Version=12.0.0.0,Culture=neutral,PublicKeyToken=71e9bce111e9429c” %>
<%@ WebHandler Language=”C#” Class=”put_data_in_sp_list” %>

using System;
using System.Web;
using Microsoft.SharePoint;
using System.Xml;

public class put_data_in_sp_list : IHttpHandler {

public void ProcessRequest (HttpContext context) {
SPSite site = SPContext.Current.Site;
SPWeb web = site.OpenWeb();

try
{
string rawXML = “”;
XmlTextReader reader = new XmlTextReader(context.Request.InputStream);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(reader);
string _xmlString = xmlDoc.InnerXml;
System.Text.ASCIIEncoding encoding = new System.Text.ASCIIEncoding();
string _fileTime = DateTime.Now.ToFileTime().ToString();

byte[] docAsBytes = encoding.GetBytes(_xmlString);

//Insert Document
web.AllowUnsafeUpdates = true;
// Here is the name of your Custom SharePoint List
SPList list = web.Lists["TestList"];
SPListItem item = list.Items.Add();

// Here is the name of your SharePoint Field that you will be sending the data from the first field of your Adobe Form
item["Title"] = xmlDoc.GetElementsByTagName(”Title”).Item(0).InnerText;
// Now Update the SharePoint List with the Data
item.Update();

}
catch (Exception ex)
{
context.Response.Write(ex.Message);
}

}
// The HTTPHandler must have these lines
public bool IsReusable {
get {
return false;
}
}

}

:: END OF CODE

Notice that this script is using a single text field in an Adobe form then sending the XML data to a Custom SharePoint list called “TestList” in the “Title” field using SharePoint Services. If you have ever programmed C# scripts against SharePoint Services, you can see how this technique is EXTREMELY powerful. Since the referenced class above is “put_data_in_sp_list”, then obviously your .ashx file will be called put_data_in_sp_list.ashx .

LAYER 3 (Custom SharePoint List):

The final piece of this architecture is the Custom SharePoint List. If you are working off of the example above, obviously you will need to create a Custom SharePoint List called “TestList” and just use the “Title” field.

Conclusion:

Now that you have all 3 layers in place, you can now Preview your Adobe PDF form in Adobe LiveCycle Designer and enter some text in the text field. Then hit the Submit button. Your data will now be sent to the HTTPHandler, then added as a new item in the SharePoint “TestList”.

Once you have successfully tested data going from the Adobe PDF form to the custom SharePoint List, you are now ready to create your custom workflow on the SharePoint list. Just use SharePoint Designer to create the workflow to meet your business process needs and set it to kick off when a new item is added to the “TestList”. Install the worklfow on the “TestList” and now you have an end-to-end business solution using an Adobe PDF form to submit data to a SharePoint list while leveraging the Workflow capabilities of SharePoint.

Tags Categories: SharePoint, WSS Services Development Posted By: Clinton Daniel
Last Edit: 20 Jun 2009 @ 10 54 PM

E-mailPermalinkComments (18)

This post applies to Microsoft Office SharePoint Server 2007, WSS 3.0, SQL Server 2008 Integration Services, and MySQL.

Scenario: Many times as SharePoint lists grow to the 1000s of rows, it is more applicable to build your reporting system external of the SharePoint system. Data Views begin to be difficult to deal with and you may need to build a custom solution for optimal performance. In my case, I have a mixed shop of developers that have a variety of skills. When I lead a project with my team, I try to distribute the workload as best as I can so that all can contribute to the quality and completion of the project. One of my developers is extremely talented in the world of Linux, Apache, MySQL, and PHP (aka LAMP). I still believe that the LAMP stack is the best environment for web applications but lately the requirements of projects have been dominated by the world of SharePoint. SharePoint has become Microsoft’s hostile takeover environment. It seems they are wrapping all of their services underneath it. All stack examples I’ve seen lately seem to include SharePoint somewhere in the pipe.

So, knowing that IT Shops are buying into SharePoint (hardcore) and executives are getting used to its look and feel.  SO… what do we do with our highly skilled LAMP developers? They cannot be left out of the project. In my opinion, we need to at least give them the reporting portion of the project. However, according to this technique that I’ll describe in the solution below it is possible to build a LAMP solution that can make a transaction to a SharePoint list. I have not fully tested this solution yet – but in theory it should work. So…….. how do we do this?

Solution: In summary: What you will be doing is using Microsoft SQL Server 2008 Integration Services to build a package that will take data from a SharePoint List to MySQL. This solution is possible using Microsoft SQL Server 2005 but it is much easier using the 2008 version. SSIS 2008 comes with an ADO.NET destination component for the ETL engine Data Flow. The ADO.NET component made it much easier to set up the connection and send the data to MySQL.

First you need to setup an ODBC connection on your development box that uses an account that can successfully connect to your MySQL database. Go to the MySQL Site and download/install the ODBC Drivers for Windows here: Click Here

Once you have downloaded the drivers, you can setup your connection and test it. To do this. Go to Start –> Control Panel –> Administrative Tools –> Data Sources (ODBC). This will open the “ODBC Data Source Administrator” dialog box. Click on the “System DSN” tab and click the “Add” button. Locate the entry titled “MySQL ODBC 5.1 Driver” and SELECT it. This will load the MySQL Connector/ODBC Data Source Configuration. Fill out fields in the form and make sure you click the “Test” button to confirm your connection is Successful. Then Click “OK” to Save it. You should now see your connection listed in the System Data Sources. Click “OK” to close the ODBC Data Source Administrator Dialog.

Now that you have your ODBC Connection defined, you are ready to build your Integration Services Package.

BUT, before you begin make sure you follow the instructions in this blog entry: Click Here

This blog entry will give you instructions on how to install an adaptor that you will need to pull data from a list and send it to a database server. Once this adaptor is setup you will be able to add the “SharePoint List Source” tool to your SSIS Package.

One final thing needs to be done in MySQL before you develop the package. When SharePoint sends data via SSIS, there will be quotes associated with text. This will cause a problem when attempting to import the data into MySQL because the database will not know what to do with the quotes around the text data. In other words MySQL is expecting:

HERE IS TEXT                      (Instead of)                    “HERE IS TEXT”

So to fix this problem, you have to go into MySQL and globally configure the database to set “sql_mode” to ANSI_QUOTES. MySQL Describes setting ANSI_QUOTES as:  Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

Here is the MySQL reference that describes MySQL SQL MODES: Click Here

With all the above accomplished, you will now be able to build your package.

Some steps to help you build the package:

Add a Data Flow Task to your Control Flow in the package. Go inside the Data Flow Task and add a “SharePoint List Source” control and an “ADO.NET Destination” control. Configure the “SharePoint List Source” control with a SharePoint “SiteListName”, “SiteListViewName”, and “SiteUrl” (Use your All Items view fo rthe “SiteListViewName” for testing).  Check your “Column Mappings” to verify the control is seeing the List Data. Click “OK” to close the control.

Now drag the Green Arrow from the “SharePoint List Source” to the the “ADO NET Destination” to connect the flow. Now configure the ADO NET Destination Control. In the “Connection Manager” section, click the “New…” button. This will open the “Configure ADO.NET Connection Manager”. Click on the “New…” Button inside here.  This will take you to the “Connection Manager” dialog. From the “Provider” drop-down, choose “Odbc Data Provider” fromt the choices under “.Net Providers”. Now, under the “Data Source specification” section (make sure “Use user or system data source name:” is selected) click the drop down and find your previously defined ODBC connection you created above.  Under the “Login information” section, enter the User name and Password that has access to the MySQL Database. Now click the “Test Connection” button to confirm that your connection is successful. It the connection is successfull, click the “OK” button and you will see your MySQL connection listed in the “Configure ADO.NET Connection Manager” dialog. Now click “OK” to get out of this. You should now be back in the “ADO.NET Destination Editor” dialog. In the “Use a table or view” drop down, TYPE the name of a table in your MySQL database (many time the drop down will not populate properly – must be a bug – so you have to type the name of the table). Once you type the name of the table, click the “Preview” button to confirm the connection to the table.  Now click the “Mappings” link on the left side of the Dialog. This will show you what fields you have available from the SharePoint list on the left and what receiving fields you have available in the MySQL database/table on the right. If the fields are matched exactly, SSIS will attempt to predict a match with lines added between the two. If a prediction is not made, just click a field from the left and drag it to a field on the right. It will create a line between the two. Once you are finished mapping the fields, click the “OK” button.

Now you are ready to Run your package. Hopefully, you see “Greenage”. If the package is successful, you’ll see the final step turn green. Check your MySQL Database/table for data.

Now your LAMP developer can develop a reporting system off of the MySQL database.

Tags Categories: SharePoint Posted By: Clinton Daniel
Last Edit: 19 Jun 2009 @ 05 44 AM

E-mailPermalinkComments (2)

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: In many cases SharePoint Lists can grow to be 1000s of rows. Often, you are required to build a report of some kind based upon the data being collected in the SharePoint list. It is an easy solution to build custom views with SharePoint services or SharePoint Designer to render the data in the form of a report. However, I have noticed that once the data grows to a large number, a more advanced approach is needed to aggregate the data with a method that is optimal for performance. When the list grows to 1000s of rows, views tend to be more difficult and perform poorly (depending on what you are trying to display).

Solution: Initially, I started to experiment with getting data out of a SharePoint List via MS Access 2007. There is a great tool that is available via MS Access that allows you to do this and may be optimal for your solution. However, in my experience the performance of MS Access begins to decline greatly once you get data from a list in the numbers of 1000s. It also seems difficult for Access 2007 to accomplish this task across a network when retrieving 1000s of rows of data.

There are also some custom solutions that can be achieved by building a web application that pulls data from a list by programming against SharePoint services. However, in most cases I found this to be just too much trouble for such a simple task.

What I have successfully used and recommend is SQL Server Integration Services. This solution will work for both SQL Server 2005 (SP 2) and SQL Server 2008. Microsoft has released a tool that can be installed and used with the Business Intelligence Studio that comes with SQL Server 2005/2008. This tool installs two additional options that can be used when building an Integration Services Package. It allows you to pull data from a list and send it to SQL server – OR – take data from SQL server and send it to a SharePoint List. It is very effective.

Read this post from MSDN for instructions: http://msdn.microsoft.com/en-us/library/dd365137.aspx

Go here to download the .msi files (You will see the 2005 adapter available for download): http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=17652

Additionally, using this package I was successful in pulling data from a SharePoint List and Importing it into a MySQL database running on Mac OSX server. To do this, you will need to install the MySQL ODBC or ADO.net drivers onto the SQL server box. I may create a separate post specifically on how to do this.

Tags Categories: SharePoint, Uncategorized Posted By: Clinton Daniel
Last Edit: 26 May 2009 @ 03 30 PM

E-mailPermalinkComments (1)

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: You have created a custom .aspx page with SharePoint Designer that has a custom data view that shows data from a SharePoint list. You Save the custom site with its custom .aspx pages and move it to another SharePoint site. When opening up the custom .aspx page in the new location, you get an error on the page that says, “Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Windows SharePoint Services-compatible HTML editor such as Microsoft Office SharePoint Designer. If the problem persists, contact your Web server administrator.” How do you fix this?

Solution: Open the custom .aspx page in SharePoint Designer and look for the tag that encloses the DataFormWebPart that is being used to render the data view from the list. It should have a tag that has:

<WebPartPages:DataFormWebPart runat=”server” ………….  ><ParameterBindings>
Now look for:

<ParameterBinding Name=”ListID” Location=”None” DefaultValue=”########-####-####-####-############” />
This ParameterBinding tag that has the ListID defined will be using the ListID of the Original List (the old site). You will need to change this definition to the ListID of the new site.

Now, you will need to go to the List that is located on the new site that the custom .aspx page is looking for to render the data to the view. Once you locate the list, click on Settings  List Settings.  Copy the URL that is rendered in the Address Bar to a text editor. The URL will look something like:

http://vhamoss/sites/whateversite/_layouts/listedit.aspx?List=%7B11111111%2D1111%2D1111%2D1111%2D111111111111%7D

In your text editor, delete everything in the URL up to the % symbol, just after the “List=”.

Now change the “%7B” to “{“.

Change all instances of “%2D” to “-“.

Finally, change the “%7D” to “}”.

You should now have the ListID:  {11111111-1111-1111-1111-111111111111} (Obviously you number will be different than a bunch of ones).

Now that you have the new ListID, go back to SharePoint Designer, open the new custom.aspx page, and locate that tag again as described above and substitute the old ListID with the new one that you have just created in your text editor. You will not need to past the “{“ “}” symbols. You will just need the numbers with the dashes (same format as the old one).
Save the edited custom .aspx page in SharePoint Designer and refresh the page in your Browser.

You will now see the custom .aspx page in the new location with the correct data and no errors.

ALSO NOTE: It is easier to just do a FIND/REPLACE of the old List ID with the new LIst ID across the entire Custom page. It will most likely be a case where an instance of the List ID will need to be replaced in more locations than the above referenced location (depending on how you setup your data view).

Tags Categories: SharePoint Posted By: Clinton Daniel
Last Edit: 13 May 2009 @ 04 57 PM

E-mailPermalinkComments (0)

This post applies to Microsoft Office SharePoint Server 2007 and WSS 3.0

Scenario: You have a SharePoint List that is being used to track some kind of business information to automate a business process. The business process requires that once a user adds a new item to a list a workflow executes that sends an e-mail to an approver (or some other person involved in the process). The e-mail will include a link inside it that will take the user to a Custom .aspx page stored in a document library. Once the Custom .aspx page opens, it should display the specific List Item needed for review/edit. HOW DO YOU DO THIS?

Solution: The first thing you would need to do is set some things up on the Custom .aspx page. The Custom .aspx page should include a “Single Item Form”. This allows you to edit the data inside the list with a “Save” and “Cancel” button already created. In the upper right hand corner of the single item form (Data Form Webpart), you will see the drop-down for the “Common Data View Tasks”.

First, you will need to create a “Parameter” by clicking the “Parameters” link – then the “New Parameter” button. Choose “Query String” from the drop-down under the “Parameter Source” option. Now give it a name and enter something in the “Query String Variable” text box. The Query String Variable is what will be used in the URL later to identify the item (so make note of it). For web programmers, this is basically a GET variable used in the URL to supply a value later.  

Now you will need to define the Filter to use the parameter – so click the “Filter” link under the Common Data View Tasks. Once inside the “Filter Criteria” dialog, choose “ID” as the Field Name. Now under the “Value” drop-down – choose the parameter name that you just defined. This now associates a filter with the ID of the List item that will be defined by using the parameter variable. Save the Custom .aspx page.

Finally, you will need to modify the workflow that sends the e-mail when a new item is added. The workflow activity will obviously be an e-mail. Inside the body of the e-mail you will need to add something similar to the following:

<a href=”http://servername/sites/organizationsitecollection/SharedDocuments/Custom.aspx?parametername=[ID]“>Click Here</a>

Where parametername is the name of the parameter that you defined above. The [ID] portion of the URL tagged above should be added by using the “Add Lookup to Body” button. You should then lookup the ID field to be added. It will not work if you simple just type [ID]. Now save your and re-upload your workflow.

Now when you add a new item to a list an e-mail will be sent to the required user with a link in it that will take you directly to the Custom.aspx page that will be loaded with a Specific Item.

Tags Categories: SharePoint Posted By: Clinton Daniel
Last Edit: 14 Apr 2009 @ 06 33 PM

E-mailPermalinkComments (0)

\/ More Options ...
Change Theme...
  • Users » 2
  • Posts/Pages » 16
  • Comments » 41
Change Theme...
  • VoidVoid
  • LifeLife « Default
  • EarthEarth
  • WindWind
  • WaterWater
  • FireFire
  • LiteLight
  • No Child Pages.