2.28.2013

SSIS - Excel Querying

The power and flexibility of SSIS never ceases to amaze me. With just a few simple lines of code you can import and manipulate data straight out of excel.

This piece goes on to the actual excel connection provider.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\<FILENAME.xls>;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Instead of table loading the excel file on the datasource, utlize a SQL query such as this.
SELECT F1,F18 from [Sheet1$A3:D8]
WHERE 
  F1 IS NOT NULL
  ........

2.27.2013

SharePoint 2010 - People Picker Configuration

There are so many nuances to this little tool in SharePoint. Configuration settings can only be done after installation using STSADM commands. Below are several resources I have found related to this topic.

Checking Accounts:
https://<SiteURL>/_layouts/people.aspx?MembershipGroupId=0
https://<SiteURL>/_catalogs/users/simple.aspx

People Picker Hide Inactive Accounts:
stsadm.exe -o setproperty -propertyname "HideInactiveProfiles" -propertyvalue "true"

Run These Command as SPFarm Account to Remove Old DBs:
stsadm -o sync -listolddatabases 1
stsadm -o sync –deleteolddatabases 1
stsadm -o sync –synctiming m:5 
(this is to alter the sync timing schedule in a DEV environment, default is 1 hour)

Awesome PowerShell Commands for Checking and Deleting Orphaned Users:
http://www.sharepointclowns.com/2011/04/find-and-delete-orphaned-users/

People Picker Resource Articles:
http://technet.microsoft.com/en-us/library/cc263196.aspx
http://technet.microsoft.com/en-us/library/gg602075(v=office.14).aspx
http://blog.helloitsliam.com/Lists/Posts/Post.aspx?ID=48

Resyncing an Account From AD:
Set-SPUser -Identity "<domain\username>" -web "<SiteURL>" -SyncFromAD

Manually Deleting Accounts:
1. In SQL: SELECT * FROM [WSS_Content_DatabaseName].[dbo].[UserInfo] WHERE tp_Login='DOMAIN\username'
2. Take note of the tp_ID
3. In IE, go to http://<your sharepoint site collection/_layouts/userdisp.aspx?ID=tp_ID, where tp_ID is the number you found from your select statement.
4. It should take you to the user's profile where you can click on the Delete User from Site Collection button.

2.20.2013

2.12.2013