My Digital Life in System Integration

SSIS Tools

Kevin Shyr — Thu, 03 Nov 2011 20:03:37 GMT

Somebody told me this and I'm ready to try some of the components:

http://www.konesans.com/products.aspx

FileWatcher, Regex Clean, RowNumber Transform

SSIS design consideration

Kevin Shyr — Wed, 26 Oct 2011 03:16:37 GMT

The following is my experience with SSIS building:

Make sure you understand the 3 modes of Lookup. General, I would only do full cache if the lookup table only return less than 200 rows
Never use the table or view select unless you really need every field on that table or view.
- select * from a data source will break in the future if more columns are added to the table
- select * in a lookup brings back unnecessary fields and impact performance
Never use "Sort" in SSIS unless it's absolutely necessary. Sort in database first.
- Merge join requires sorted input; you can do order by in your select, then configure the output to specify the "sorted" column.
Transaction matters. If you only need to read data, use the snapshot option to ensure fastest reading and without blocking the table.
Don't use config file unless you are sharing values across multiple packages. Not using config files makes the packages more dynamic as values can be set though SQL Job or xp_cmdshell
Now that I've mentioned it, don't use xp_cmdshell to start package unless you have to. By properly storing variables in well constructed tables should eliminate the need to dynamically pass in values.
use variable value expression instead of using script task to set values
Just like in T-SQL, don't do a select with 15 tables joined together. Do an insert of the key first, then update the destination table.
Learn C# (well, for 2008 or later, otherwise, learn VB as well)

Enjoy your SSIS project!

Monitoring BizTalk Server Without SCCM, using SQL query

Kevin Shyr — Thu, 15 Sep 2011 03:37:04 GMT

For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check.

Note: be aware that the following was tested only against a single suspended instance with a send port. Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join. With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration parts work.

SELECT TOP 1000

_M.nvcName AS ApplicationName

, CASE

WHEN _BSP.nvcName IS NOT NULL

THEN 'Send Port: ' + _BSP.nvcName

WHEN _BSP.nvcName IS NOT NULL

THEN 'Receive Port: ' + _BSP.nvcName

WHEN _BPL.Name IS NOT NULL

THEN 'Pipeline: ' + _BPL.Name

WHEN _BO.nvcName IS NOT NULL

THEN 'Orchestration: ' + _BO.nvcName

ELSE 'Unknown'

END AS ServiceName

, _IS.dtCreated

, _IS.dtSuspendTimeStamp

, _IS.nvcAdapter

, _IS.nvcURI

, _IS.nvcErrorDescription

, _IS.nvcErrorProcessingServer

FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended] AS _IS WITH(READPAST)

INNER JOIN [BizTalkMsgBoxDb].[dbo].[Services] AS _S WITH(READPAST)

ON _IS.[uidServiceID] = _S.uidServiceID

INNER JOIN [BizTalkMsgBoxDb].[dbo].[Modules] AS _M WITH(READPAST)

ON _S.nModuleID = _M.nModuleID

LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_sendport] AS _BSP WITH(READPAST)

ON _IS.uidServiceID = _BSP.uidGUID

LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_receiveport] AS _BRP WITH(READPAST)

ON _IS.uidServiceID = _BRP.uidGUID

LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_pipeline] AS _BPL WITH(READPAST)

ON _IS.uidServiceID = _BPL.PipelineID

LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_orchestration] AS _BO WITH(READPAST)

ON _IS.uidServiceID = _BO.uidGUID

WHERE

_M.nvcName = 'Your application name' -- change application name here to monitor different applications

So far USB 3.0 leaves a lot to be desired

Kevin Shyr — Wed, 24 Aug 2011 14:49:05 GMT

I now have a few docking station for my SATA drives that's hooked on to USB 3.0 PCI Express cards. I also have a laptop with 2 USB 3.0 ports. The straight up transfer speed is good, 86 mbps versus 20 mbps, my virtual PC now runs very well with USB drives. However, using those devices for file storage has not been a very good experience so far. With the old USB 2.0 drives, I could create multiple "move" instances between drives. The speed slows down, but I could initial some moves and leave the computer for a while. With USB 3.0, if I initial a second file move between the drive that are currently being moved, the drive drops off and corrupts the files. I thought it was the drivers, but my 2 desktops and my laptop all are doing the same thing.

For now, I am moving back to USB 2.0 when I need to move files, and only use USB 3.0 when I need something fast straight up. I'm definitely looking forward to new computers with USB 3.0 built in natively and will try other docking stations other than Vantec. However, my HP portable USB 3.0 drive is doing the same thing, so I'm not sure anything would help at this point.

I guess the first thing to do is to look through all the support page on Vantec, HP, Dell, and Gateway.

Setting up different emails on the windows phone

Kevin Shyr — Thu, 26 May 2011 15:36:34 GMT

Here are some links I found useful when I was setting up mine:

Setting up multiple Hotmail account: http://community.spiceworks.com/how_to/show/714

Setting up Gmail as the Outlook sync account: http://www.google.com/support/mobile/bin/answer.py?hl=en&answer=138636

Do NOT trust CHECKSUM or BINARY_CHECKSUM in SQL, use HASHBYTES instead

Kevin Shyr — Sat, 21 May 2011 01:50:01 GMT

We had a process ot using BizTalk to take in a reference file, then send data in via a table_type parameter in a stored procedure. The stored procedure uses a MERGE statement to insert and update; the update part of which does a CHECKSUM first to determine whether an update is even necessary.

Then it happened, we had a reference row that should have been updated by the latest file, but it wasn't done. We checked the incoming and outgoing BizTalk Message in and out of the pipeline; everything looked normal. Then we decided to take out the CHECKSUM part and the update succeeded.

In reading over the Microsoft documentation, I was absolutely shocked when I read this in the remarks section:

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change

http://msdn.microsoft.com/en-us/library/ms189788%28v=SQL.100%29.aspx

Then I checked the page for BINARY_CHECKSUM, a similar remark exists. http://msdn.microsoft.com/en-us/library/ms173784%28v=SQL.100%29.aspx

According to these 2 articles, we should be using HASHBYTES(). http://msdn.microsoft.com/en-us/library/ms174415%28v=SQL.100%29.aspx

Personally, I was really shocked to find a function in any database that "most of the time" does its job. I really do not know any system that tolerates "occasional" mistakes, or maybe it's that I haven't worked in enough industries. I equate this to getting a notice on your bank statement saying "we might not have all your transaction listed, and we don't know for sure whether we have them all".

I love quantum physics, but not in my computer systems.

Frustration and rewards on using SSIS Pivot and Unpivot

Kevin Shyr — Fri, 20 May 2011 20:36:30 GMT

Granted, if you are just transforming data in the same SQL server, why would you do it in SSIS instead of just using Pivot and Unpivot in T-SQL query? But what about in the case where data comes from various sources?

What I had fun doing was the ability to skip using Linked server, pull in data from different sources, like XML over web service. Another point that made me happy was the ability to simulate ROW_NUMBER() function with the merged data by using a Script Component.

Anyway, I highly recommend everyone to try this out. It's very frustrating throughout the process, but the result is very rewarding.

Pivot: http://msdn.microsoft.com/en-us/library/ms140308.aspx

Unpivot: http://msdn.microsoft.com/en-us/library/ms141723.aspx

PowerPivot, Silverlight PivotViewer, Excel Services, Performance Point, Project Crescent from STL BI Use Group

Kevin Shyr — Fri, 20 May 2011 02:21:41 GMT

Meeting notes:

· Reporting Services is now incorporating Dundas Tools for better UI tools (I thought this was done a while ago)

· New to Reporting Services 2008 R2: visualization tools like data bar, sparc lines on row level

· Why should you use Microsoft Reporting Services? It is a big ASP .NET extensible application. If you want customization, you can easily build on top of this.

Reporting tools in addition to Reporting Services reports:

·         Silverlight PivotViewer Control (hosted on web server):
   http://www.silverlight.net/learn/pivotviewer/
    http://www.microsoft.com/silverlight/pivotviewer/

· Excel Services (hosted in SharePoint, managed service configuration):
http://msdn.microsoft.com/en-us/library/ms546696.aspx

·         PowerPivot (can be used in Excel 2010 Professional as standalone add-in; when sharing is required, it needs to be deployed to a SharePoint 2010 and SSAS 2008 R2):
    Note: this builds an in memory cube and MDX, when deployed to SharePoint, the cube is built in the SQL server. It can also be built the other way around where a cube is built first, then use PowerPivot to create report. There are plenty of data source adapters available, including Teradata, Oracle, DB2, oData, etc.
    http://www.powerpivot.com/

·         Performance Point (hosted in SharePoint Server Enterprise, replaced Proclarity):
    Enable drill down
    http://technet.microsoft.com/en-us/library/ee661741.aspx

· Project Crescent (coming soon)
http://team.silverlight.net/announcement/project-crescent/

Discussion:

Naturally, the discussion took a turn on “What tool should I use when?” The official response from the presenters is:

· If we have only 1 user, then maybe start with PowerPivot

· Enterprise level reporting should build cubes, then UI is based on User requirement

· Anything else is in the grey area

My take is that if I work for a company with access to a SharePoint Enterprise server, then I would definitely let the report user build the report in Excel using PowerPivot. When the report is ready, then we can reconcile the differences and migrate the single-user report to use Enterprise cube and hosted in SharePoint.

Resources:

· http://www.bidn.com/

· http://pragmaticworks.com/Resources/PragmaticMinutes/Default.aspx

· http://pragmaticworks.com/Products/Business-Intelligence/BIDocumenter/Default.aspx Database Edition is free

BizTalk documentator

Kevin Shyr — Fri, 06 May 2011 16:53:59 GMT

This looks like a very useful tool: http://biztalkdocumenter.codeplex.com/

Notes:

For this tool to be effective, populate the "Description" property of BizTalk projects.
Documents ports, business rules policies, etc.

Windows Azure Marketplace DataMarket

Kevin Shyr — Thu, 05 May 2011 20:35:57 GMT

One stop shop for data: https://datamarket.azure.com/

SQL Azure Labs >> Configure OData Services

http://www.odata.org/

Windows Azure Cloud Computing Scenarios Notes

Kevin Shyr — Thu, 05 May 2011 20:35:29 GMT

Windows Azure Cloud Computing Scenarios Notes: (http://wabcdemos.cloudapp.net/)

Things to consider for architecture:

Does It improve bottomline?
http://azureroi.cloudapp.net/
http://www.microsoft.com/windowsazure/economics/
Does it improve strategy?
Independent scaling
break down components into the smallest unit
When increasing capacity, cost doesn't go up much, when scaling down, capacity doesn't suffer a huge drop.

Windows Azure Diagnostics Notes

Kevin Shyr — Thu, 05 May 2011 19:13:23 GMT

Windows Azure Boot Camp: Diagnostics (http://wabcdemos.cloudapp.net/)

Remote desktop
MonAgentHost.exe starts Auto, listener wired up in app/web.config
Need to define a storage account
User can set quota
Source to Storage:
Example: Trace log to Azure Table; IIS Logs to Blob; Windows Event to Azure Table, Crash dump to Blob, ...
Load Diagnostic Agent
<Imports><Import moduleName="Diagnostic" /></Imports>
OnStart({DiagnosticMonitor.Start(connectionstring)})
TransferFilterLogLevel and TransferLogPeriod (???)
When done, call the SetCurrentInstance() to persist the change.
IntelliTrace (require .NET 4, Visual Studio Ultimate (can deploy, but need Ultimate to play back log [Kevin: really? wondering how the log parses]})
All API calls must be signed with a registered administrative certificate X509, can register up to 5
Create self-signed certificate: Go to deploy (publish) >> Not the create package
During export, make sure you get a copy of PFX file
Fault Domain, default of 2
Upgrade Domain, default of 5
Azure does not autoscale, mostly a billing concern.
SQL Azure becomes Read-only when you go over the limit.
Download Grey Box application (from CodePlex)
http://greybox.codeplex.com/
Some plugins available: C:\Program Files\Windows Azure SDK\v1.4\bin\plugins\
IIS Remote Manager

Windows Azure Queue Notes

Kevin Shyr — Thu, 05 May 2011 16:02:14 GMT

Windows Azure Boot Camp Note: http://wabcdemos.cloudapp.net/

Queue name must be lower case
Unlimited number, but limited to 8kb in size
must be serializable as XML
GC runs once a week
Producer to Consumer

Example: WebRole PutMessage() to Queue, WorkerRole GetMessage(optional Timeout), (or/then, this part is unclear as to why I have to do this) RemoveMessage() (Q: Does this mean GetMessage, after successful, puts the message back on queue? That should not be right. From the notes I get, there is no commit or lock on the message during processing. I can see this being promising, but for the level it's built right now, I don't think it's ready for business applications)
The follow up I get is the GetMessage doesn't remove the message, the control rests with the program. If the program doesn't respond saying the message is good and processed, the timeout will put the message back into the queue. So if the program processes the message, it must call the RemoveMessage() to dequeue.

Other methods: PeekMessage(); Note: Peek doesn't count as transaction, but GetMessages(10) saves some money.
Notes: combined with the previous point, why would I use Peek at the current time? If I peek, there is no lock to guarantee the message, but I can do GetMessage and do the processing to determine whether I want to RemoveMessage(). I definitely need to do more research on this part.
Create CloudQueueClient
Compare to MSMQ, there is no event, so must use sleep in polling.

Windows Azure Drives Notes

Kevin Shyr — Thu, 05 May 2011 15:08:23 GMT

Notes from Windows Azure Drives: http://wabcdemos.cloudapp.net/

Why use this? One example is to satify PDF requirement (PDF files require a local temp drive)

Durable NTFS drives
Can be up to 1TB
Commands:
CloudDrive.InitializeCache()
account.CreateCloudDrive()
Must use Flush() to persist data
Guidance:
store key in config, encrypt it
wrap key with service

Windows Azure Blob Notes

Kevin Shyr — Thu, 05 May 2011 15:00:41 GMT

Notes from Windows Azure Boot Camp: http://wabcdemos.cloudapp.net/

Blob:

Metadata, not searchable, must loop through to check
Block Blob, 200GB limit
Page Block, 1TB limit
Upload block blob
PutBlock (bloblName, blockId1, block1Bits)
PutBlock (blobName, blockId2, block2Bits)
...
PutBlock (n...)
PutBlockList(blobName, blockId1, blockId2, ..., blockIdN)

Currently no data validation
Page Block
Random Read/Write
Work with random chunks (so can work with smaller chunks, but can over-write data)
Container has extra security
Shared Access Policy
Content Delivery Network (CDN)
For example, Windows update, video streaming
Eample: upload a public blob container, public URL, when request comes, it goes to Edge Node; Edge node download it from Public Blob Container and re-route the content. A timer is set; when second request comes, edge node will check for version.

Windows Azure Tables notes

Kevin Shyr — Thu, 05 May 2011 14:03:06 GMT

Notes from camp: http://wabcdemos.cloudapp.net/

CreateCloudTableClient()
CreateIfNotExists
DeleteIfExists
CreateTable
Create Partition Key (don't use identity key as partition key)
Create Row Key, must be unique per PartitionKey
Use a lightweight copy to improve performance
Use "batching" to increase performance, and also save money by reducing the number of transaction
For example, ???Context.SaveChanges(SaveChangesOptions.Batch);
Queries
Over 30 second is cancelled
result > 1000, a continuation token is returned
Transaction
Limit 100 per batch
Column limit: 260 (URI length)
Data transfer: only counts the transfer in/out of data center (not within data center)

Thanks to my wife for a dream machine

Kevin Shyr — Thu, 05 May 2011 13:39:17 GMT

2 months ago I learned that there is going to be a Windows Azure Boot Camp, free, 2-day, code-intensive. The only catch at the time for me was that I didn't have a laptop, a requirement for this camp.

Thanks for my beautiful and tolerant wife, we provisioned and I got my dream machine. Waited and shopped for a long time to get a reasonable price, but I got the following spec:

8GB ram
Necessary for me to run my virtual environments
i-5 4-Core CPU
Want 4-core for virtual environments, but not a fan of hyper-threading, so no i-7
2 USB 3.0 ports
Hook up to my USB 3.0 portable external drive, it's like having a SATA drive. My virtual machines are flying.
dedicated video card
Free up system resources.

Now I am enjoying this Boot Camp and I got my DEV environments all set up and portable. I'm one happy man!

Windows Virtual PC vs. Hyper-V Virtual Machines vs. Windows Virtual PC after XPMode installation Advantages and Disadvantages

Kevin Shyr — Thu, 05 May 2011 13:29:24 GMT

Hyper-V Virtual Machines

Advantage:

64-bit support
dynamic memory allocation
multi-core CPU
Can install Windows 7 and Windows Server 2008 and later

Disadvantage:

Can't get sound to work on those machines yet
[Updated 2011-05-05] This is disappointing, but I guess it fits most people's requirement as most people don't use Hyper-V for personal development environment. Guess I'll just have to set up those machines for remote connect.
http://sqlblog.com/blogs/john_paul_cook/archive/2009/09/14/enabling-sound-on-hyper-v.aspx
http://social.technet.microsoft.com/wiki/contents/articles/hyper-v-troubleshooting-enabling-audio-over-rdp.aspx
Trying to add an new wireless adapter to create a new virtual network is a pain.

Windows Virtual PC after XPMode installation

Advantage:

USB connection
Direct access to all host drives

Disadvantage:

Not really intuitive management UI at first
No 64-bit support
No Windows 7

Regular Virtual PC

Advantage:

Simple and quick to get started
Virtual hard drive created here has the least problem getting migrated into other setup.

Disadvantage:

Not scalable at runtime
No 64-bit support
No windows 7 support

Azure Code Boot Camp

Kevin Shyr — Thu, 05 May 2011 13:14:39 GMT

This is Day 2 of Microsoft Windows Azure Boot Camp. Yesterday was excellent, can't wait for the events today. One thing I do regret was not writing blogs about each topic yesterday; a mistake I will not repeat today.

For people who missed it, here is the link: http://wabcdemos.cloudapp.net/

Code Certificate

Kevin Shyr — Thu, 31 Mar 2011 17:39:45 GMT

http://www.startssl.com/

Javascript Minify tools, optimization, style checker

Kevin Shyr — Thu, 31 Mar 2011 17:37:54 GMT

Microsoft: http://aspnet.codeplex.com/releases/view/40584

Google: http://code.google.com/p/minify/ ; http://code.google.com/closure/

Some online ones that takes a block of javascript:

http://jscompress.com/

http://www.minifyjavascript.com/

Starting PowerShell learning process

Kevin Shyr — Thu, 31 Mar 2011 17:31:46 GMT

Today I start learning powershell, maybe 2 years too late!

http://powershell.com/cs/

http://www.powergui.org/index.jspa

http://poshcode.org/

Parallel programming in Visual Studio

Kevin Shyr — Thu, 31 Mar 2011 17:26:03 GMT

Not much experience with these products yet.

Free: http://developer.nvidia.com/object/nsight.html

Not free: http://software.intel.com/en-us/articles/intel-parallel-studio-purchase/

For SSIS, this looks very interesting:

http://extendedssispkgexec.codeplex.com/

Instead of alter table column to turn IDENTITY on and off, turn IDENTITY_INSERT on and off

Kevin Shyr — Thu, 03 Mar 2011 05:24:34 GMT

First of all, I don't know which version of SQL this post (http://www.techonthenet.com/sql/tables/alter_table.php) is based on, but at least for Microsoft SQL Server 2008, the syntax is not:

ALTER TABLE [table_name]
MODIFY [column_name] [data_type] NOT NULL;

Instead, it should be:

ALTER TABLE [table_name]
ALTER COLUMN [column_name] [data_type] NOT NULL;

Then, as several posts point out, you can't use T-SQL to run an existing column into an IDENTITY column. Instead, use the IDENTITY_INSERT to copy data from other tables. http://msdn.microsoft.com/en-us/library/ms188059.aspx

SET IDENTITY_INSERT [table_name] ON
INSERT ....
SET IDENTITY_INSERT [table_name] OFF

http://www.sqlservercentral.com/Forums/Topic126147-8-1.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65257

A network card did not show up in the Virtual PC networking adapter settings...

Kevin Shyr — Fri, 25 Feb 2011 01:43:37 GMT

This post saved my life: http://blogs.technet.com/b/windows_vpc/archive/2009/12/07/networking-in-windows-virtual-pc.aspx