Thursday, November 03, 2011
#
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
Tuesday, October 25, 2011
#
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!
Wednesday, September 14, 2011
#
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
Wednesday, August 24, 2011
#
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.
Thursday, May 26, 2011
#
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
Friday, May 20, 2011
#
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.
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
Thursday, May 19, 2011
#
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
Friday, May 06, 2011
#
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.
Thursday, May 05, 2011
#
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: (http://wabcdemos.cloudapp.net/)
Things to consider for architecture:
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 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.
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
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.
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)
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!
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
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/
Thursday, March 31, 2011
#
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/
Today I start learning powershell, maybe 2 years too late!
http://powershell.com/cs/
http://www.powergui.org/index.jspa
http://poshcode.org/
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/
Wednesday, March 02, 2011
#
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
Thursday, February 24, 2011
#