Running SSIS packages from a web page

I need to run a simple aspx page on a SQL server to run an SSIS package so that users could kick off the process without having access to the SQL admin tools.

The following C# class in an asp.net page runs brilliantly on the SQL 2008 server

static void CallSSIS()
{
string pkgLocation = @"E:\VS Projects\\Sandbox.dtsx";
Application ssisApp = new Application();
Package ssisPackage = ssisApp.LoadPackage(pkgLocation, null);
DTSExecResult ssisPackageResults = ssisPackage.Execute();
}

The only major problem I had was publishing the web to a virgin Windows 2008 with IIS7 machine. When I browsed to the page it gave the following error "This configuration section cannot be used at this path".

After hours of head scratching and missleading web articles, it turned out that the ASP.NET is not installed as a ROLE in by default.

Server Manager -> Roles -> (scroll to Web Server (IIS)) -> Add Role Services -> ASP.NET

Volia!

Symantec System Center - Cant unlock server group

I was asked to look at unlocking a Windows 2003 Server Symantec System centre whos documented name and password didnt work.

If you a) update the "user-admin-p" registry entry -
HKEY_LOCAL_MACHINE\SOFTWARE\Intel\LANDesk\VirusProtect6\CurrentVersion\Accounts (more details here)


and then b) run the iforgot.exe program in the Program Files\Symantec\Symantec System Center\Tools folder

then you can reset the user account's password.

Setting wss 3.0 site anonymous access

A couple of years ago I setup a secure WSS 3 web application which I now wanted to publish so that everyone (anonymous) could see it, however I couldnt find out how to do it.

Following the Microsoft guidance article it suggests Site Actions -> Users and Permissions -> Advanced Permisions -> Settings and then click on Anonymous access, however the option just wasnt there !

Further investigation reminded me I needed to make sure that my "authentication providers" needs to be set to support anonymous as otherwise its not an option to choose in menu above. To set this up go to Web Application Management -> Application Security -> Authentication Provider, choose the Zone you wish to edit and then make sure "enable anonymous access" is checked and save this setting.

Now go back to the first stage on the Advanced Permissions -> Settings drop down and voila, you can set anonymous for site, list level or none as you require.

Updating Windows 2008 Domain Account Policies

Whilst passwords meeting complexity requirements is all well and good in a production environment it is a pain in a development one. I have a Windows 2008 standard AD Domain Controller and and I needed a number of system accounts which I really didnt want to have to bother of recording complex passwords.

To change these settings is not intuative, however this is what I found:

To turn password complexity off in Windows 2008 Server or change other grayed out security options, follow these steps:
(1) Run gpmc.msc (Group Policy Management)
(2) Expand your Domain
(3) Go to Group and select Default
(4) Right click on Default Domain Policy or Default Domain Controllers Policy and choose Edit
(5) In the new window which opens expand computer Configuration->policies->windows settings->security settings->account policies->password policies
(6) Make your edits to the keys below:

There must be an easier way, which I am problaby missing !

C#3 anonymous types, parameters and LINQ for XML

Crash learning to work with LINQ XML (see sample code below to get a distinct list from an XML file written by a reporting services report) re-introduced me to a really usefull feature of C#3 called anonymous types where you can create an object on the fly. You can then use that object in the same method, however if you wish to pass it to another method then things start getting complicated. This blog note Anthony Steele's Blog : C#3 anonymous types as parameters points you in the right direction to whats possible.

(He also mentions a very good book on C#3 (by Bill Wagner) to hone your skills)


Note: as a learning LINQ is a lot of trial and error you can test your LINQ code in a very helpful utility called:http://www.linqpad.net/

Sample LINQ code
XDocument xDoc = XDocument.Load(@"c:\temp\RSD.xml");
var q = (from e1 in xDoc.Descendants("{SchemaName}Detail").Distinct()
let div = e1.Attribute("Division").Value
let cc = e1.Attribute("CostCode").Value
select new { div, cc }).Distinct();

q.Dump(); //this shows you the contents of q

Integrating External Document Repositories with SharePoint Server 2007

Whilist loading documents into Sharepoint from existing file shares is a good option it is often time consuming and here is an interesting article showing how you can link existing exteral document libraries with sharepoint Integrating External Document Repositories with SharePoint Server 2007:

Saving Reports to SharePoint using WebDav from Windows 2008

I was having difficulty getting Enterprise Reporting Services to run a data subscription (running on Windows 2008) to save reports into a Sharepoint Document library using a URL.

I found that Windows 2008 does not install the required WebDav Client (Not to be confused with the additional IIS7 WebDav Server addon) .

You can install this client by Adding the "Desktop Experience Feature" on Windows 2008, however this also installs lots of other things that you probably dont want and Ive not found a way to only install WebDav Client support.

After a server re-boot it works well and talks to Sharepoint Document Libraries, My thanks to the following article for assistance. Browsing SharePoint using WebDav from Windows 2008 « In The Mix

Chaks' Corner | WCF and Sharepoint - System.IO.FileNotFound Exception

Writing a WCF Sharepoint handler I had a great deal of trouble wondering why SPSite host = new SPSite(http://localhost..../) was giving a Web application could not be found error. The URL opened fine in a browser.

The answer appears to be security related where the WCF service doesnt seem to have enough permission to get to the local sharepoint server. One workaround which seems to work is to set the WCF Application Pool permissions to the same as the Sharepoint Application Pools. Im not sure its elegant but until I find a better way this seems to work. Thanks to the following blog: Chaks' Corner WCF and Sharepoint - System.IO.FileNotFound Exception

How to fix issue with Symantec Endpoint client not installing due to a required reboot. | Shnake's Tech Tips

I needed to remove my old Symantec AV and install Symantex Endpoint Protection, however the installer kept saying there it would not install due to pending reboot actions - even after four reboots !

My thanks to the following link who shows the relevant registry to sort this problem out.

How to fix issue with Symantec Endpoint client not installing due to a required reboot. Shnake's Tech Tips:

SharePoint Documents in "Checked Out" limbo mode after upload from explorer with mandatory fields not completed

We have been uploading documents into SharePoint from the file system by dragging and droping them from Explorer, and we could'nt work out why they were being shown as "Checked Out" even though the Version Control settings were set to not use "Check-in/Out".

The reason turns out to be related to mandatory fields being by passed by the Explorer upload method. If you load a document via the filing system and a mandatory field is not completed then Sharepoint leaves the document in a “Checked Out” state. However you cannot “Discard the Checked Out” state because no document has been “Checked In” in the first place !

So you have the situation where a document is in limbo – not checked in or checked out but only visible for the user who loaded the document to complete the mandatory fields.

BUT even if you do complete these fields – it would seem that Sharepoint keeps the document in the limbo state and won’t allow you to do anything with them unless you fully check them in.

AND the problem is that SharePoint doesn’t have a “bulk check-in” function!

Reference: Automatically checkin files after uploading « SharePoint, ASP.NET and more

Binding Excel 2007 values using (DIP) Server Document Properties to Sharepoint- Not Simple or Intuitive

We had a requirement to link values entered in Excel 2007 cells so that when they were saved to a Sharepoint Document library those values were seemlessly displayed Document library as fields.

This functionality is handled much more easily by Word 2007 - see these excellent articles (DIP Metadata and Word 2007 Custom Doc Properties) - but Excel 2007 doesnt seem to share this functionality out of the box.

It turns out that there are 3 types of Metadata Properties in Excel 2007 (where WB is the ThisWorkbook object in this example):

• WB.BuiltinDocumentProperties
• WB.CustomDocumentProperties
• WB.ContentTypeProperties

To access this Mata data visually use the Excel "Office button" -> Prepaire -> Properties menu. Choosing "advanced properties" gives you access to both add new custom properties and the built in properties. You can assign fixed text values to them or link them to named ranges within the sheet, however you cannot get to the Server Document Properties from this dialog.

ie the XL-> Custom Properties GUI only gives you access to the following Property sets (and of course these aren't what you need for Sharepoint)

• WB.BuiltinDocumentPropertiesSet
• WB.CustomDocumentPropertiesSet

To update the data which will feed back to Sharepoint fields you need to use code to get to the WB.ContentTypeProperties object which has a type of "MetaProperties".

The WB.ContentTypeProperties object is a collection which can be iterated through and each field has an ID and Name and a Value. The ID is an internal value and seems to Map to the same internal name as that of the Sharepoint field it is connected to.

{Note: the internal field names can lead to some confusion such as where a field with a Name of "col1" seems to create an internal ID name of "_x0063_ol1" and a field with a Name of "Project Lead Finance" has an internal ID name of "Project_x0020_Lead_x0020_Finance". This is normal Sharepoint practice!}

Using macro code to set the values of a MetaProperty in this way seems to trigger an event in the Server Document Properties DIP (Document information Panel) to update the field value to the new value you've set. {Note: The DIP is an InfoPath based piece of XML which displays fields in Office 2007 applications. It may be possible to code against it to achieve a more automated link to native Excel custom properties but I haven't had a chance to look at that yet}

Below is a very simple piece of code to set a Server Document Property to the value of cell A1:

Sub SetServerProperties()
Dim WB As Workbook
Set WB = ThisWorkbook
For Each Prop In WB.ContentTypeProperties
If Prop.Name = "Project Lead Finance" Then
Prop.Value = Range("A1").Value
End If
Next Prop
End Sub

Using macro code has the disadvantage of Excel documents having to be saved as XLSM documents and I am sure there must be a way to trap an update event of an standard Excel Custom property using something like VSTO, however that is for another post.

{Note: for Excel 2003 just updating the fields in WB.CustomDocumentPropertiesSet before a save does seem to pass them back to Sharepoint ok}

My thanks to an article published by Sadalit Van Buren who pointed me in the right direction with this article A Matter Of Degree: XML Mapping of Document Properties with Excel and SharePoint - Not Simple or Intuitive

Sharepoint Permission levels and permissions

Ive been working on setting Sharepoint permissions programmatically at a document level and found an interesting article below:

One of the things we noticed last week was that if you set the permissions on a single folder or document for someone who is not already in the document library, sharepoint adds them to the document library permissions root with [Limited Access].

The full article is here http://office.microsoft.com/en-us/sharepointtechnology/HA101001491033.aspx. , but in summary:

Limited Access


The Limited Access permission level is designed to be combined with fine-grained permissions to give users access to a specific list, document library, item, or document, without giving them access to the entire site. However, to access a list or library, for example, a user must have permission to open the parent Web site and read shared data such as the theme and navigation bars of the Web site. The Limited Access permission level cannot be customized or deleted.
Note You cannot assign this permission level to users or SharePoint groups. Instead, Windows SharePoint Services 3.0 automatically assigns this permission level to users and SharePoint groups when you grant them access to an object on your site that requires that they have access to a higher level object on which they do not have permissions. For example, if you grant users access to an item in a list and they do not have access to the list itself, Windows SharePoint Services 3.0 automatically grants them Limited Access on the list, and also the site, if needed.

Referencing Sharepoint Assemblies on x64bit Installs causes build errors for ASP.NET apps (in VS.NET 2008)

Debugging a WCF application on a 64 bit server using VS2008 gave me odd errors where sharepoint.security.dll could not be loaded - it turned out that you have to use the 32bit version of the microsoft.sharepoint.xxx.dll libraries - see the following article.

robgruen's WebLog : Referencing Sharepoint Assemblies on x64bit Installs causes build errors for ASP.NET apps (in VS.NET 2008):

Oulook 2007 and Sharepoint document libraries sync.

This subject is poorly documented and a bit of a "black art", however Ive found a Microsoft quick ref card here.

Note Outlook behaves differently if the library is greater than 500Mb. If less then it sync everything, otherwise it syncs only headers and you have to manually sync documents. I cant find any way to alter this functionality.

I also find some interesting training slides from TechNev on the subject.

Microsoft's office page for this subject is here

64 bit pdf and onenote printing problems

Whilst MS is pushing 64 bit for everything, using 64bit Vista on my HP laptop continues to be a frustrating experience.

I had deadline and needed to print from MS Project to PDF. Even though I have a professional version of Acrobat 8, I cannot seem to print to PDF and the machine even locks and crashes Project. As a workaround I tried printing to OneNote, but again you cant seem to do this on a 64 bit machine. In the end I had to screenshot what I needed in OneNote and then creating the PDFs from that which is very annoying. These sort of issues along with my being unable to use Groove shared folders on 64bit workstation makes me feel that we are still a good way off from being able to adopt 64bit workstations for mainstream.

A few days after the deadline I found this product which seems to work well Free PDF Creator - Convert to PDF from Any File You Can Print - PrimoPDF

64bit Print PDF troubles

Free PDF Creator - Convert to PDF from Any File You Can Print - PrimoPDF

Excel -- Comments -- Programming

I needed to extract addresses from a comments field in an Excel spreadsheet. The following had some really helpful code to manipulate the comments:
Excel -- Comments -- Programming

No more /console switch in RDC 6.1

Remote Desktop Console "/console" connection switch has been disabled in Vista SP1 and Windows 2008.

It has been replaced by "/admin". This is a huge problem for server administration if you didnt know about it!

It looks like it will be implimented in XP sp3 as well.

The following article shows more detail Ask the Performance Team : MSTSC.EXE - no more /console switch in RDC 6.1

TimeSnapper - make timesheets a snap

An interesting screen shot utility for use when you need to remeber what it was you did !

TimeSnapper - make timesheets a snap

SharePoint 2007 Post SP1 hotfix woes

We have a 6 server SharePoint 2007 Enterprise Farm which we were having a number of memory errors and timer errors on. We had hoped that SP1 would address these issues, however the timer errors and other problems are still arrising.

A MOSS Post SP1 HotFix was made available on Feb 25th and we tried to install it today, but as with most things it didnt go according to plan.

There are no pre-install instructions, however based on the SP1, you need to install the binaries on the Administration server first, then run the Sharepoint Products and Technologies Config Wizard, BUT only to the point it warns you to install the patch binaries on the other servers in the farm. Once you have installed the patch on all the other servers go back to the Administration server and click ok to continue with the Sharepoint Products and Technologies Config Wizard.

All was going well until step 8 which gives an upgrade error and refers you to a log file. There are no helpful messages and the only possible information is the following:

Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException

Has anyone else seen this issue ? and what is the resolution ?

Crawling SharePoint sites using the SPS3 protocol handler

When setting up MOSS Index crawls I noticed an odd prefix of SPS3:// and the following article explains why it should be used for index crawling to improve performance, especially over the WAN: Jose Barreto's Blog : Crawling SharePoint sites using the SPS3 protocol handler

Microsoft SharePoint Products and Technologies Team Blog : SharePoint SDK Downloads Now Live with SP1 Updates

After wetting our appetite with MOSS SP1 at last 1.3 MOSS is here. (Note the new intellisense xml file)

Microsoft SharePoint Products and Technologies Team Blog : SharePoint SDK Downloads Now Live with SP1 Updates

Indexing PDF content in MOSS Sharepoint 2007

Out of the box, MOSS only indexes the meta data of a PDF and not the Text inside it if it is available. To index the full content you need an iFilter which is explained well here Steven Van de Craen's Blog.

We have used Foxit's ifilter as it has an x64 version which our MOSS servers are running on.

After getting this installed I was still having trouble with some files not indexing and found that out of the box MOSS will only index 16Mb files. You can up this using a registry setting on the server - documented here. This works fine except you should be mindful of possible server index time out errors. Mindsharp say that you can increase the timeout value here, but I havnt managed to find this setting in MOSS enterprise yet.

(There is an interesting article on custom Sharepoint Searches here)

VS2008 includes shiny new images to use in our apps

I was looking for some button images in VS2008 and found this usefull blog:
DevLife - .NET General - VS2008 includes shiny new images to use in our apps

SharePoint Surveys - Finding the Results in the Database « Jack of all that is Microsoft, Master of None

Creating a sharepoint survey is easy, however getting sensible data from it to analyse the results is not. This article shows how you can get to the database behind the scenes.
SharePoint Surveys - Finding the Results in the Database « Jack of all that is Microsoft, Master of None

VMWare ESX 3.5 crash introduction - in at the deep end!

As a developer and for systems testing I have used VMWare workstation and VMWare Server for some time, however Ive felt that I needed to get to grips with ESX server to really take advantage of virtualisation. It should be pretty similar to VMWare Server shouldn't it - errm well no...

Firstly you can download a 60 day eval of ESX 3.5 server and other components from VMWare. This gives you a chance to learn but I didn't realise the curve would be quite so steep.

I created a boot disk, booted onto an 8Gb server taking most of the default options and I was impressed how fast it loaded. It asked me to enter a base IP address and gateway which I put it a standard 192.168.x.x address (I thought it would be easy to change after) and rebooted.

ESX booted fine, but just gave me a Linux prompt - no GUI, no friendly interface, just a command line. I haven't worked on Unix since University over 20 years ago so this was a shock.

The first thing you need to do is a connect a browser from another machine on the network and download the "VMWare Infrastructure Client". From here you can carry out quite a lot of admin.
BUT here was my first problem, I had made a mistake on the initial IP address configuration and couldn't find any way to change it to DHCP or anything else !

It turns out that to change an IP address on the ESX server is quite complex, needing to use the system console to deleting a configuration file, re-create it and then update the gateway using the vi text editor somewhere else! My thanks to Damian Murdoch for his article on how to do this. But although I followed this I couldn't find how to switch it to DHCP, so I bit the bullet and installed ESX server from scratch for a second time, this time choosing the DHCP option.

Now I could talk to the ESX server from the "VMWare Infrastructure Client", so I created a new "Resource Pool" and created a new "Virtual Machine" so I could build a Windows 2003 server.

I had a Windows 2003 ISO on my machine with the Infrastructure client but I could find any way of linking the new "Virtual Machines" CD drive to this ISO - except by using the "Connect to CD/DVD" button. This button only works once you start your new VM - so you have to be quick to click on it and connect it to your ISO whilst your VM is booting. Trouble is that the VM boots so fast that it ignores the CD boot and just tries a DHCP network boot and gives up.

Transferring files and ISO's to the ESX Server
I could have burnt a CD from the ISO and put it in the ESX physical server, but I was working remotely and so thought I'd just copy the ISO on the the ESX store and link the new Virtual machines CD to it.

Again a problem. If you are only running ESX Server you seem to need to access the ESX using a form FTP Server. I found a Free Utility called Veeam FastSCP 2.0 which seemed to answer my problem really well, so I loaded it up and tried to log in. As a rookie I tried to login as root but got an access denied error. Searching support forums told me that FastSCP would not login as root except if you check the elevate to root check box and enter the root password. Well this doesn't seem to work on ESX3.5 so I had to create a new user using the Infrastructure client. (You select the ESX Server Icon on the LHS and there is a Users and Groups tab).

Using this new user login, I could access the ESX file system and create Folders, however whenever I tried to copy and paste a file I received a timeout error. I tracked this down to a tightening of the ESX 3.5 firewall. FastSCP uses ports 2500, 2501... but these are now locked down in ESX 3.5. The option is to open the ports which FastSCP uses by using the ESX console command "esxcfg-firewall -o 2500,tcp,in,FastSCP", however you need to do this for 2501, 2502... etc An alternative is to open the firewall whilst copying the files using "esxcfg-firewall --allowIncoming" and then closing it aftewards using "esxcfg-firewall --blockIncoming" - source discussion on "Transfer timeouts" here.

This worked well and I could transfer my ISO file to the ESX store. One forum suggested transferring ISO's to the /vmimages folder, however this seems to be mounted under /dev/sda2 which only had 5Gb of space. If you right click on the properties of the connected server in FastSCP then it will list the devices with space. I ended up using /vmfs/volumes/xxxx.

Now having the Win2k3 iso on the ESX file store, I could edit the new Virtual Machine settings to point its CD to the ISO on the ESX store. (Remember to check the enable the CD device tick box as well as connecting it to the ISO).

Thus this is the story so far. I have a Windows server running on ESX after a weekend of head scratching and searching blogs and forums!

I know that the ESX platform is a very very powerful suite, but for beginners with simple requirement it seems rather tricky to get started!

Vista shortcut to Network Connections

After a few months I'm warming to Windows Vista, however I use VPN network connections quite a lot as well as updating network settings. This is one area where Vista has made things much more complex than XP. However thanks to TweakVista you can create a shortcut to the old XP interface in Vista - Hurrah!

Create a shortcut containing "explorer.exe ::{7007ACC7-3202-11D1-AAD2-00805FC1270E}"

Full article source here : TweakVista.com - Create a direct shortcut to Network Connections

Microsoft SharePoint Server2007 , Security Explorer, ScriptLogic

Managing security for a large and complex MOSS installation with over 1000 users can be a large manual job. We are looking at ways of automating this process to assign individuals from AD rather than groups. This will mean that we can use the permissions to see which users have access to what, however the overhead of maintaining this is large. This is an internesting program which may be of some assistance with backup and restore security policies. Microsoft SharePoint Server2007 , Security Explorer, ScriptLogic

Exposing Excel contents to make reporting easy in SharePoint

Some time adding Excel forms to a Sharepoint document library makes more sense than a complexly designed infopath form. This is especially the case when the form has a lot of financial computations. The problem is how to get the values in the Excel documents back to sharepoint so that they can be used in the document list views. This article gives an example of how to do it Exposing Excel contents to make reporting easy in SharePoint

Microsoft Outlook Utilities and Add-ons - TechHit.com

I was looking for a utility to store some reference email folders into the file system (or ultimatly Sharepoint) and I came across this interesting site.

Microsoft Outlook Utilities and Add-ons - TechHit.com