Thursday, November 03, 2011 #

SSIS Tools

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

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, November 03, 2011 3:03 PM | Feedback (0)

Tuesday, October 25, 2011 #

SSIS design consideration

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!

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Tuesday, October 25, 2011 10:16 PM | Feedback (0)

Wednesday, September 14, 2011 #

Monitoring BizTalk Server Without SCCM, using SQL query

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
 

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Wednesday, September 14, 2011 10:37 PM | Feedback (0)

Wednesday, August 24, 2011 #

So far USB 3.0 leaves a lot to be desired

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.

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Wednesday, August 24, 2011 9:49 AM | Feedback (0)

Thursday, May 26, 2011 #

Setting up different emails on the windows phone

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

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 26, 2011 10:36 AM | Feedback (0)

Friday, May 20, 2011 #

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

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.

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Friday, May 20, 2011 8:50 PM | Feedback (1)

Frustration and rewards on using SSIS Pivot and Unpivot

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

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Friday, May 20, 2011 3:36 PM | Feedback (0)

Thursday, May 19, 2011 #

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

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

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 19, 2011 9:21 PM | Feedback (0)

Friday, May 06, 2011 #

BizTalk documentator

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.
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Friday, May 06, 2011 11:53 AM | Feedback (0)

Thursday, May 05, 2011 #

Windows Azure Marketplace DataMarket

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

SQL Azure Labs >> Configure OData Services

 

http://www.odata.org/

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 3:35 PM | Feedback (0)

Windows Azure Cloud Computing Scenarios Notes

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

Things to consider for architecture:

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 3:35 PM | Feedback (0)

Windows Azure Diagnostics Notes

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

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 2:13 PM | Feedback (0)

Windows Azure Queue Notes

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.
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 11:02 AM | Feedback (0)

Windows Azure Drives Notes

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
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 10:08 AM | Feedback (0)

Windows Azure Blob Notes

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.
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 10:00 AM | Feedback (0)

Windows Azure Tables notes

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)

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 9:03 AM | Feedback (0)

Thanks to my wife for a dream machine

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!

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 8:39 AM | Feedback (0)

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

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:

 

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
  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 8:29 AM | Feedback (0)

Azure Code Boot Camp

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/

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, May 05, 2011 8:14 AM | Feedback (0)

Thursday, March 31, 2011 #

Code Certificate

http://www.startssl.com/

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, March 31, 2011 12:39 PM | Feedback (0)

Javascript Minify tools, optimization, style checker

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/

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, March 31, 2011 12:37 PM | Feedback (0)

Starting PowerShell learning process

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

http://powershell.com/cs/

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

http://poshcode.org/

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, March 31, 2011 12:31 PM | Feedback (0)

Parallel programming in Visual Studio

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/

 

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, March 31, 2011 12:26 PM | Feedback (0)

Wednesday, March 02, 2011 #

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

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

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Wednesday, March 02, 2011 11:24 PM | Feedback (0)

Thursday, February 24, 2011 #

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

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

 

  • Share This Post:
  • Share on Twitter
  • Share on Facebook
  • Share on Technorati

Posted On Thursday, February 24, 2011 7:43 PM | Feedback (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski