cannot convert null to ‘system.datetime’ because it is a non-nullable value type

This blog shows you the workaround for the error “cannot convert null to ‘system.datetime’ because it is a non-nullable value type” especially with you are working with SSIS project.

Declare the header

using System.Data.SqlTypes;

In Database Timestamp field you write.

Row.ShiftTimeOut = (DateTime)SqlDateTime.Null;

It will not be null in the database but it will be 1900-01-01 00:00:00:00.

Posted in .Net, Community, Computers and Internet | Tagged , | Leave a comment

Install Visual Studio 2017

This short blog walk you through the installation of Visual Studio 2017.

Run mu_visual_studio_enterprise_2017_x86_x64_10049783.exe from the offline installer (here is how you get the offline installer

For this installation, I choose .NET desktop Development, Desktop development with C++, ASP.NET, SharePoint development and .NET Core (you need to scroll down). You can see the total size at the bottom right corner is less than 9GB.


Visual Studio will start installing. 


You can click the Launch once the installation ends.



It will open Visual Studio but without version number.


If you do not want to sign in now you can click the Not now, maybe later.


You will then choose your color schema and get started.


The final screen will be the one below once Visual Studio completely launched. You can now create your new application or open the existing application.


Posted in .Net, Cloud, Community, Computers and Internet, Microsoft Office | Tagged , , , , , | Leave a comment

Create Visual Studio 2017 offline installer

Good or bad news, Visual Studio no longer comes with ISO file for download in MSDN. You need to run the command in command prompt to get your own Offline Installer.

The instruction link is below.

There are some tricks you may want to be aware of.

1. When you download the exe file from MSDN the file name for Enterprise Version is called mu_visual_studio_enterprise_2017_x86_x64_10049783.exe so do not type vs_enterprise.exe.

2. Do not put you exe in vs2017offline folder. Put it somewhere else.

3. mu_visual_studio_enterprise_2017_x86_x64_10049783.exe –layout C:\VS2017Offline  will be enough. You do not need –-lang en-US if you want the language to be English.


You will then see the packages are download like below.

4. You will see character in Red if there is any error.


5. You need a pretty fast Internet.

Like the one below is not yet completed.


6. The complete size of the offline file command that I issued actually will download 23.8 GB.


You can do the same for Community Edition

Posted in .Net, Cloud, Community, Computers and Internet, Microsoft Office, Office 365, sharepoint | Tagged , , , , , , | 1 Comment

Office 2013 application cannot run Office App Store application

This blog is specifically for Windows 7 32 bit and Office 2013 32 bit.

I have this problem when my Windows 7 32 bit, cannot do Windows Update. That also means MS Office 2013 will not be updated.

You need to make your Windows 7 can run the live update. I use the link below and it works.

My Office 2013 application is able to run the app from Office App Store after Windows Update.

Posted in .Net, Cloud, Community, Microsoft Office, Office 365, sharepoint | Tagged , , , , , | Leave a comment

Microsoft Power BI vs Qlik vs Tableau 2017

Gartner 2017 reports shows Tableau, Qlik, and Microsoft are the leader in Business Intelligence and Analytics Platforms. The full Gartner url is at the bottom. The Gartner link will be expired in sometime later, that was my experience with Gartner link.

I will put the highlight in this blog for the 3 BI platforms.



Cost: Microsoft is placing downward pricing pressure on the BI and analytics market with both a free desktop product as well as low subscription price per user per month. On an annualized basis, Microsoft Power BI is roughly one-third of the license cost of a three-year perpetual BI license, but 80% lower than other cloud BI products. Low total cost of ownership was cited as the second most important reason for reference customers choosing Microsoft Power BI. However, potential customers should be aware that additional data scale-out options incur additional costs when leveraging Microsoft SQL Azure or HDInsight in the cloud — once they reach the 10GB per user limit in the standard Power BI Pro price. Being free but functionally deficient does not succeed; this was a problem with the first release of Power BI. However, Microsoft has now narrowed feature gaps and successfully executed on its “five by five” strategy — five seconds to sign up and five minutes to “wow” the customer.

Ease of use plus complex analysis: Microsoft’s customer reference scores place it in the top quartile for ease of use and complexity of analysis. Ease of use for content consumers was also the most-cited reason for customers choosing Microsoft Power BI. It was also placed in the top quartile for composite ease of use; however, there is room for improvement in ease of administration and authoring (where the vendor was average). Customers now want ease of use not only with simple queries, but also with increasingly sophisticated types of questions — mashing together multiple data sources from multiple fact tables. Microsoft’s ability to manipulate data from multiple data sources — both cloud-based and on-premises, relational as well as Hadoop-based, and including semistructured content — contributed to this high composite score.

Vision: Microsoft is furthest to the right on the Completeness of Vision axis and has also continued to execute on its roadmap with frequent (monthly) product releases. Microsoft was relatively early to introduce search-based queries with Power BI Q&A, and has recently introduced Quick Insights as a basic form of smart data discovery. Microsoft continues to integrate its machine-learning capabilities as part of a complete solution, the Cortana Intelligence Suite. This vendor has also moved a step closer to linking insights to actions, with the recent integration of Power BI with Microsoft Flow and within its business application, Microsoft Dynamics.

Active community: Microsoft has a strong community of partners, resellers and individual users. This community extends the product with prebuilt apps, visualizations and video tutorials, in addition to the content provided directly by Microsoft. This additional content is available via the Microsoft AppSource, further contributing to Microsoft’s far-right placement for its Completeness of Vision. Microsoft’s reference customer scores placed it in the top quartile for the user-enablement metrics of user community and availability of skilled resources in the marketplace.


Product immaturity and cloud-only: The two biggest concerns that Microsoft’s reference customers cited relate to absent or weak functionality and an inability to handle required data volumes. While Microsoft has handled some of the tougher product problems (such as hybrid cloud to on-premises connectivity and search), it is missing basic functionality such as the ability to display data in a pivot table or to create subtotals within a tabular display. Microsoft’s current work-around is to use Excel to create the pivot table, but this creates a workflow challenge. While Microsoft offers scale-up options, the path is not clear and is further complicated by differing strategies for Analysis Services on-premises versus in the cloud. Data can reside on-premises, but for sharing and collaboration the dashboards are stored in the Microsoft Azure cloud. An option to publish Power BI reports to on-premises Reporting Services is part of the 2017 roadmap.

Breadth of use: Microsoft’s scores from its reference clients place it in the bottom quartile for breadth of use (as with last year). Breadth of use looks at the percentage of users who use the product for a range of BI styles, including viewing reports, creating personalized dashboards and doing simple ad hoc analysis, to performing complex queries, data preparation and using predictive models. Microsoft Power BI is mainly being used for parameterized reports and dashboards.

Support: Microsoft’s reference scores placed it in the bottom half of all the vendors in this Magic Quadrant for support quality. While the community is strong, support from Microsoft is not what its customers expect, particularly in terms of response time and time to resolution. Also, 7% of reference customers said that support quality is a barrier to wider deployment, putting Microsoft in the top quartile for this complaint. In part, support challenges can be exacerbated by frequent product releases in which capabilities are changing rapidly.

Not the only standard: Microsoft Power BI is often used in combination with other BI tools, which is not surprising for a newer product to market and one with gaps in functionality. However, this mix-and-match strategy may make it harder for customers who want to minimize their portfolio’s complexity. Here, customers may use Power BI as a low-cost option for broadly used, simple dashboards, and then complement it with products from other BI vendors whose products have more robust capabilities. This dynamic may change as Microsoft continues to improve its product capabilities with monthly releases.



Momentum: Qlik’s customer reference scores place it in the top quartile (of all the vendors in this Magic Quadrant) for its market responsiveness, based on a combination of how successfully the product is deployed in organizations and its strong momentum. Qlik’s revenue grew 20% for the first half of 2016 (financial numbers are no longer available following Qlik’s privatization). Qlik was also placed in the top quartile for interest, based on searches on and customer inquiries. With the incorporation of NPrinting into Qlik Sense, Qlik supports both Mode 1 (traditional BI capabilities to schedule reports) and Mode 2 (agile, governed data discovery and visual exploration); this breadth of product capabilities further contributes to its position in the Leaders quadrant.

Rapid deployment: Qlik’s scalable, in-memory engine allows lines of business as well as central IT to rapidly mash data from multiple data sources that is then accessible via highly interactive dashboards. Qlik’s ease of use for consumers and its visually appealing dashboards have proven to be product differentiators.

User enablement: User enablement is once again a strength for Qlik. With a modern BI architecture, business power users may become the predominant content developers, instead of IT developers. Newer types of training — in the form of online tutorials and community forums — become more important than traditional classroom-style training. Qlik’s customer reference scores placed it in the top quartile for these more self-paced forms of training.

Partner network: Qlik’s partner network continues to be a differentiator through which partners offer not only product extensions and complementary capabilities, but also professional services. Qlik reference customers score the availability of skilled resources in the market in the top quartile. This partner network has also provided a way for Qlik to expand its capabilities through acquisition, most recently with partner Industrial CodeBox — whose capabilities have improved Qlik’s out-of-the box data source connectivity options. In January 2017, Qlik also completed the acquisition of partner Idevio in order to bolster its mapping and location intelligence capabilities.


More narrow use case: Complexity of analysis is an important part of a vendor’s Completeness of Vision in this Magic Quadrant. While Qlik supports a range of data sources and data models, its reference customers are not using the product in this way. A lower portion of Qlik’s references are using it for data integration or visual discovery than in previous years, with a much higher percentage of customers using it for parameterized reports and dashboards. This more narrow use case has affected its positon on the Completeness of Vision axis. Bearing this in mind, Qlik’s sweet spot may increasingly be for agile, centralized BI provisioning, rather than for more sophisticated analytics use cases as it is deployed more broadly in the enterprise. This may be a reflection of how competitors have evolved in terms of easier data preparation, in contrast to Qlik — in which a load script is still required for more complex data models and data preparation. Also, advanced analytics capabilities are largely lacking in Qlik Sense, both in the form of R integration as well as out-of-the-box visualizations and menu options, although these items are on the roadmap.

Software licensing and cost: Cost of software was (again) cited as a barrier to adoption by 30% of Qlik’s reference customers, putting it in the top quartile for this complaint (similar to 2016). Qlik Sense primarily uses token-based pricing, which more closely aligns to a named user than concurrent user licensing; in theory, tokens can be shared by multiple users to provide a degree of sharing, but with a reset frequency that appears to make license management a challenge. As QlikView customers adopt Qlik Sense, this token approach is proving less flexible and more expensive than the myriad packaging options offered to QlikView customers (which offered session, document, and named user options). More recently, Gartner has begun to see CPU core pricing and enterprise agreements for larger deployments that show more flexibility. Beyond pricing, there were no major barriers to wider adoption, or platform problems; this shows an improvement in product maturity over the previous year.

Technical support lags: Customer references scored Qlik’s technical support as slightly below average (compared with other Magic Quadrant vendors) for level of expertise, response time and time to resolution (similar to the responses in 2015). There is a high bar in the modern BI and analytics space, as demonstrated by the fact that despite 66% rating the support as excellent, and only 5% as poor, the vendor was still slightly below average. These scores are part of the reason why Qlik is positioned lower in the Leaders quadrant.

Evolving cloud strategy: Qlik’s cloud strategy continued to evolve in 2016, most recently with the addition of Qlik Cloud for Business, which is positioned for small to midsize organizations and limited to 500GB per workgroup. Prior to this release, Qlik customers could deploy QlikView or Qlik Sense in the cloud in a bring-your-own license model. A Qlik Sense Cloud for enterprise customers, with more fine-grained control and unlimited data storage, is planned for 2017.



Gold standard for intuitive interactive exploration: Tableau’s core product strengths continue to be its intuitive interactive visualization and exploration and analytic dashboarding capabilities for almost any data source — leveraging an extensive set of data connectors with both in-memory and direct query access for larger datasets. The popularity of this combination with business users drove the market disruption for which Tableau is now well-known and the shift to modern BI and analytics. Tableau 10 further streamlines exploration and content creation workflow for core users by further automating routine tasks, such as geocoding and the creation of time hierarchies on data fields, adding type-ahead for formula building, and new drag-and-drop clustering in addition to existing advanced analytics functions for forecasting and trends. Tableau’s reference customers continue to purchase the product for its user experience at a higher rate than for most other vendors in this Magic Quadrant, and score its ease of use among the highest of all these vendors.

Focus on customer experience and success: All aspects of customer experience and operations (among Tableau’s reference customers) have improved this year compared with last year, and it scores above the vendor average for this Magic Quadrant. This includes a top quartile score for the primary measure of success — achievement of business benefits. The key to customer success is user enablement, where Tableau’s references give it top scores across all categories as well as for availability of skills from both the market and the vendor. Tableau offers a vast array of learning options — including online tutorials, webinars and hands-on classroom-based training — to educate and empower its users, which has increased the number of skilled Tableau resources available in the market along with Tableau Public, its online community and its extensive network of Alliance Partners.

Expanding deployments and standardization rates: Some organizations prefer to use Tableau to empower centralized teams to provision content for consumers in an agile and iterative manner, while others adopt a more hands-off approach and enable completely decentralized analysis by business users. Tableau deployments are expanding and become a BI and analytics standard in most of its customer base, with survey customers either considering the platform to be “one of” (39%) or “the” (43%) enterprise standard. Moreover, Tableau’s reference customers report above-average deployment sizes compared with the other vendors included in this Magic Quadrant — driven by 41% of those organizations reporting average deployments of more than 1,000 users.

Flexible deployment options: Tableau can be deployed in the cloud, with Tableau Online, or on-premises. Tableau was early to the cloud, initially relying on deployment in its own data centers. Tableau has evolved its cloud deployment options to also provide prepackaged virtual machines for AWS and Microsoft Azure in order to simplify deployment and support for the Google Cloud platform (although hybrid support for on-premises data is on the near-term roadmap). Tableau Server is available as bring your own license (BYOL) on the Azure and AWS Marketplaces; it is also available in pay-by-the-hour on AWS Marketplace. About half of Tableau’s reference customers say they either have, or are planning, deployment in the cloud, which is slightly less than the 51% average for vendors in this Magic Quadrant.


Mainstreaming of core innovation: Visual-based data exploration (Tableau’s primary disruptive capability) is, while still a differentiator, now being offered by most players in the modern BI market. This includes many traditional vendors (with large installed base market shares) that newly appeal to these customers through a combination of good-enough functionality, enterprise features and integration with years of investment in SOR content and favorable pricing. While Tableau is viewed as the gold standard, the value of that distinction has diminished as feature differentiation narrows, competitive options grow, and enterprise features and price versus value for money factor more in the purchasing decision than before. This has caused increasingly competitive and contested expansion and enterprise deals. Moreover, Tableau’s need to improve its enterprise features diverts investment from the “smart” next-generation capabilities that will be the cornerstone of future competitive differentiation.

Pricing and packaging: Cost of software and complex packaging, particularly as low-cost options grow, is a challenge for Tableau. One of Tableau’s few below-average-rated execution measures continues to be sales experience (with among highest percentage of reference users citing cost as a limitation to broader deployment). With increased price sensitivity in this market, new lower-priced market entrants are being considering by and appealing to buyers, particularly for the more lightweight users in larger deployments. Tableau has responded to this competitive pressure by streamlining its packaging, being more flexible in terms of discounting on large deals and moving to subscription pricing during 2016 in order to address this purchasing barrier.

Lack of complex data model support: Tableau supports a diverse range of data connectivity options — spanning relational, online analytical processing (OLAP), Hadoop, NoSQL and cloud sources — but offers weaker capabilities when it comes to integrating combinations of these sources in preparation for analysis. While harmonized data can now be reused in Tableau 10, complex multifact table data models are not yet supported and must be created elsewhere when needed. Moreover, poor performance for large in-memory extracts often requires modeling in a separate data repository that is directly queried from Tableau. Tableau reference customers score it in the bottom quartile for average number of data sources accessed from the platform, while at the same time reporting that they access among the highest data volumes for queries — which likely reflects the approach that Tableau has taken of leveraging an underlying data warehouse if one exists. Tableau has announced its plans to release a stand-alone self-service data preparation tool (code-named Project Maestro) in 2017, to address its customers’ challenges with large and complex data.

Many enterprise features a work in progress: Most new product investment on Tableau’s roadmap is targeted at closing current enterprise feature gaps — such as adding support for Linux, replacing the TDE file format with a new in-memory engine (Hyper) to support larger datasets, and improving APIs for better embeddability and extensibility. Event-based scheduling, conditional alerting, printing to PDF and PowerPoint, and collaboration and social platform integration are also works in progress. Currently, many of these gaps are filled by partners such as Metric Insights and Computer Intelligence Associates, which again adds to the TCO.


Posted in .Net, Community, Computers and Internet, Microsoft Office | Tagged , , , , , | Leave a comment

The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine

This short blog show you how to resolve the error “The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine.” when you are using SQL Server Management Studio 2016 to import an Excel file.

What you need to do is to download this Office 2007 Microsoft Access Engine. Well, why 2007 work for OLEDB 16 and no newer version, that should be a question to Microsoft.

Download link:

Posted in .Net, Community, Computers and Internet | Tagged , , , , | Leave a comment

Debugger.Break() Crashes Visual Studio

This short blog shows you one of the possibilities to resolve the crash when using Debugger.Break() in your code in SQL Server Data Tool.

When you use Debugger.Break() in SSIS package you might crash your SSIS package with the error below.


Problem signature:

 Problem Event Name:    CLR20r3

 Problem Signature 01:    DtsDebugHost.exe

 Problem Signature 02:    2015.130.1601.5

 Problem Signature 03:    57244eb5

 Problem Signature 04:    SC_b49499f4784c46bdb61997ce00d25fbb

 Problem Signature 05:

 Problem Signature 06:    58aab772

 Problem Signature 07:    3

 Problem Signature 08:    53

 Problem Signature 09:    Debugger.Break

 OS Version:    6.3.9600.

 Locale ID:    1033

 Additional Information 1:    edae

 Additional Information 2:    edae703551258c63f25f3a06c17bbfcf

 Additional Information 3:    6f1d

 Additional Information 4:    6f1d3188eaf2dae00c303d0a60a3bd0d


Read our privacy statement online:



Go the Project in Visual Studio and choose Properties. 

Set the TargetServerVersion to the SQL Server version that you are running.

If you are not in SSIS environment then try to change the .NET Framework version.


Posted in .Net, Cloud, Community | Tagged , , | Leave a comment