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());
}
}
}