Running SSIS packages under other Users

There is one place need your very special attention when you try to deploy SSIS package, and let other users run the package. That is "Package Protection Level". By default it chooses "Encrypt sensitive data with user key". That is why you can't run it under the other users' account. Maybe you can try to use "Rely on server storage and roles for access control".

SQL 2005 64bit with Linked Server SQL 2000 Query Issues

We just upgrade our main DB from SQL 2k to SQL 2k5 64bit. But we still need to use linked server to link to another SQL 2k for running some queries. But I get the following error when trying to execute some query:

OLE DB provider "SQLNCLI" for linked server "SQL 2k Server" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0TCP Provider: An existing connection was forcibly closed by the remote host.Msg 18456, Level 14, State 1, Line 0

The solution is: When running a query, SQL 2k5 64bit is trying to invoke the sp_tables_info_rowset_64 store procedure. But this proc is not in SQL 2k server. So one wrapper is needed to put in SQL 2k server to make the query work.

Create Procedure sp_tables_info_rowset_64
@table_name sysname,
@table_schema sysname = null,
@table_type nvarchar(255) = null
as
declare @Result int
set @Result = 0
exec @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type

Running SSIS package programmatically

Here I found a nice blog post for Running SSIS package programmatically. Sql Server Integration Service is still a little bit confusing. Not just like you can easily run some SSIS packages on remote DB server which most likely every DBA dreaming for that. You need to use SQL Agent or do some programming of Web Service to run it.

Silverlight Cool...

Yesterday on MIX08, Scott Guthrie announced the Silverlight 2 Beta. Demonstrating their support for Silverlight, customers and partners also took part in the keynote, including AOL, Aston Martin, Cirque du Soleil, DoubleClick, Hard Rock, Move Networks and NBCOlympics.com on MSN. Scott talked about Web, Media, RIA, and Mobile. Those cool demos really impressive. You can't image how good those HD Video, and Rich Inteactive Applications experiences are. AOL demo their Blazing Fast Email client using Silverlight. And the performance is so good, much better than AJAX.

IE 8 beta 1 for Developer

Microsoft announced IE 8 Beta 1 for Developer on MIX 08 yesterday. I download and install it today. I'm really interested in a few features:

• Activities: No more copy and paste to search, map or blog in another tab or window. Very convenient.
• Webslice: This is also a cool feature like they demo it using ebay.
• Developer tools: Very cool and useful.
• Emulate IE7: If you don't like IE8, you can switch back to IE7 engine.

But when I tried out, still have some problems because it is still in "Beta".

• Performance: it is too slow on my XP SP2 machine.
• Most of the website doesn't look good. I try Yahoo, MSN, MSDN etc.

Hope it will be better in next Beta. :)

Microsoft Office Live is up

Microsoft is offering their new Office Live Workspace service now for free. The user can upload and share documents as well as access important information from any computer. The user will also be entered into a $100,000 Sweepstakes drawing or win one of 30,000 other prizes just for signing up.

Although it is still in Beta. But looks very nice. Especially more familiar than Google Document for me who works with Microsoft Office everyday.

Free Microsoft Software for Students

Microsoft is giving out free softwares for students, including Visual Studio 2008, Windows Server 2003 etc. It is nice.

Microsoft DreamSpark

SQL Server 2008 Feb CTP

A few features I like in the SQL 2008:

• Database encryption
• Database backup compression
• FileStream support for big files
  

I also like the IntelliSense in the new SQL Management Studio.

Execute SSIS Package in C# Apps

Add referrence to Micosoft.SQLServer.ManagedDTS.dll. It is in your SQL Server Program Files Folder\SDK\Assemblies.

using Microsoft.SqlServer.Dts.Runtime;

namespace ExecuteSSIS
{
class Program
{
static void Main(string[] args)
{
Application app = new Application();
//
// Load package from file system
//
Package package = app.LoadPackage(@"c:\myPackage.dtsx", null);
package.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars = package.Variables;
vars["variable"].Value = "values";
DTSExecResult result = package.Execute();
Console.WriteLine("Package Execution results: {0}",result.ToString());

//
// Load package from Sql Server
//
Package package2 = app.LoadFromSqlServer(
"myPackage","server_name", "sa", "password", null);
package2.ImportConfigurationFile(@"c:\myPackage.dtsConfig");
//Pass parameters
Variables vars2 = package2.Variables;
vars2["variable"].Value = "value";
DTSExecResult result2 = package2.Execute();
Console.WriteLine("Package Execution results: {0}",
result2.ToString());
}
}
}

Use Active Directory to Authenticate

Today I spent sometime to review the System.DirectoryServices name space in .Net 2.0. It is very convenient to use it to access A.D. and doing Authentication for your apps. Here are some sample codes in C# you might need to start with:

//Variables for getting groups
string _path;
string _filterAttr;

bool validateUser(string username, string password)
{
//Validate Users
string path = "LDAP:// your domain";
string domainUserName = domain + @"\" + username;
DirectoryEntry entry = new DirectoryEntry(path, domainUserName, password);

try
{
// Bind to the native object to force authentication to happen
Object obj = entry.NativeObject;
DirectorySearcher search = new DirectorySearcher(entry);
search.Filter = "(SAMAccountName=" + username + ")";
search.PropertiesToLoad.Add("cn");
SearchResult result = search.FindOne();
if (result != null)
{
// Authenticated
_path = result.Path;
_filterAttr = result.Properties["cn"][0].ToString();
return true;
}
else
return false;
}
catch (Exception ex)
{
throw new Exception("User not authenticated: " + ex.Message);
}
}

//Getting the groups this user belongs to
string getUserGroups()
{
DirectorySearcher search = new DirectorySearcher(_path);
search.Filter = "(cn=" + _filterAttr + ")";
search.PropertiesToLoad.Add("memberOf");
StringBuilder groups = new StringBuilder();

try
{
SearchResult result = search.FindOne();

int nCnt = result.Properties["memberOf"].Count;

for (int i = 0; i < nCnt; i++)
{
string dn = result.Properties["memberOf"][i].ToString();
int eidx = dn.IndexOf("=", 1);
int cidx = dn.IndexOf(",", 1);

if (eidx == -1)
{
return string.Empty;
}

groups.Append(dn.Substring(eidx + 1, cidx - eidx - 1));
groups.Append("|");
}
}
catch(Exception ex)
{
throw new Exception("Groups not getting: " + ex.Message);
}
return groups.ToString();
}

Referenced assembly System.Deployment is not installed for ClickOnce?

My computer has been complaining for "Referenced assembly System.Deployment is not installed..." whenever trying to install any ClickOnce apps. And those apps were not installed successfully since sometime. Here are the solutions I found from msdn forum. It worked very well after I delete the ClickOnce store folder.

1. Run "Mage -cc". (Mage.exe can be found in the .NET 2.0 SDK)

2. Uninstall the application via Add/Remove Programs and reinstall it

3. If neither of these work then the last option is to delete the ClickOnce store and get back to clean state. To delete the ClickOnce store delete the folder "%userprofile%\Local Settings\Apps".

Use Authorization Manager (AzMan) with ASP.NET 2.0

It is very convenient to use AuthorizationStoreRoleProvider with ActiveDirectoryMembershipProvider for ASP.net application authentication and security control. But there are a few tricks you need to know when developing and deploying. Here are the useful links:

How To: Use Authorization Manager (AzMan) with ASP.NET 2.0

When I tried to deploy the app to Windows 2003 Service Pack 1, it didn't work. I found out it needs to apply this fix (KB915786) to make it work.

LINQ Pad

Want to try LINQ. This is a cool start for LINQ. Have fun.

LINQ Pad

SoCal MSDN Event

Went to MSDN Event yesterday. Lynn's talk and presentation was quite useful. Here are some of the new stuffs I got.

Microsft Popfly (www.popfly.com) is a new Web Platform still in Beta. But looks very interesting. I just signed in and played around a little bit. The design and architecture is very good. I will use it definitely.

Astoria (astoria.mslivelabs.com) is also a very interesting project. It is kind of advanced Web Data Access framework and foundation.

And I got a book "Introduction to Silverlight 1.0", too. Although it will be outdated soon since 2.0 is on the way.

How to examine SQL Server's I/O Statistics

I just happened to find this old article. But it is still good.

http://www.databasejournal.com/features/mssql/article.php/10894_2244381_1

Visual Studio 2008 trial

I have been tested the Studio 2008 Beta 2 for two months already. So far, it is pretty stable. And I already used it to maintain some of our websites. It is faster than Studio 2005. Let's wait for the final release soon.

Create Data Dictionary for SQL Server Database

Recently, I need to create a data dictionary for our 12GB database. And I did a research on creating data dictionary, and found this good tool - Data Dictionary Creator. It stores all the information in Extended Properties. Here are some blog link for this cool tool:

Data Dictionary Creator - Rapid database documentation

Generate Sql Server data dictionary table

Creating IIS7 sites, applications, and virtual directories

This is a post about configuring IIS7:

Creating IIS7 sites, applications and virtual directories

This is about the new command line util in IIS7: AppCmd.exe.

Some tutorial for running Java on Pocket PC

These are good articles for setting up Java on Pocket PC:

Opera on Pocket PC

Java Midlets on the Pocket PC - the Complete Tutorial

Based on Java: Mobile GMap

Open Source HTML Templates Site

There is an online repository of free HTML site templates that you can browse, download and use. The templates on the site are pure HTML (can use them with any server-side programming technology), using clean CSS and XHTML markup:

http://www.opensourcetemplates.org/

http://www.oswd.org/