Error reading Excel in SSIS package

The task was simple: import from an Excel file some data in a SQL Server table.

But the first task of reading the Excel file was already in error:

And in the SSIS solution Progress tab there was the error:

“[Excel Source [26]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0209303.”

This is the second time that i don’t remember the simple solution… even if the last error message is a bright hint:

“The requested OLE DB provider Microsoft.Jet.OLEDB.4.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode.”

In the SSIS project right clic on solution name , Properties and here deactivate the 64bit debugging:

Transform legacy js in Module Pattern

Tipically a lot of existing javascript code is written in the old plain mode:

function cmdAddNew_onClick() {
    showEditBox('0');
}

Every javascript programmers know that this spaghetti-code approach is hard to maintain, every function is public so it is easy to have strange behaviors that is a nightmare to debug.

The worst problems are in portals as the old Aqualogic, where was easy to have portlets with a different behavior is you inverted two portlets position in the page…

A better approach is to write our modules using a modern pattern as the Module pattern or the Singleton, there is lot a documentation about searching on Google, for examples this and this.

A skeleton of a correct Module Pattern implementation could be:

$(document).ready(function ($) {
    mycomsite.customername.projectname.context = (function ($) {
        "use strict";
        
        var cmdAddNew_onClick = function () {
            showEditBox('0');
        };      
        
        var showEditBox = function (id) {
            // js code for showing an Jquery dialog...
        }
        
        var numericSetvMax = function () {
            return '100';
        };        
        
        return {
            cmdAddNew_onClick: cmdAddNew_onClick,
            numericSetvMax: numericSetvMax            
        };
    }(jQuery));
});

The main trick is the namespace; note that we pass the jQuery variable to the module in order to use jQuery inside the module.

For example if we are a IT company with a internet site www.acme.com , we are working to a tickets solution for the scandinavian KLM company and the current is the js file for the ticketing by VISA ,a namespace could be

$(document).ready(function ($) {
    comacme.klm.ticketing.visatickets = (function ($) {
...

But we can’t have js variables with “.” in javascript, you say … the trick is a initial first module of our web app with lines as these :

if (!comacme) {
    var comacme= {};
}
comacme.klm= {};
comacme.klm.ticketing = {};

So we have defined a pseudonamespace.

Using the Microsoft Ajax Toolkit there is a better support for the namespacing , with the Toolkit we have an object named Type and we can use the method Type.registerNamespace :

<script type="text/javascript">
function pageLoad(sender, args) {
    Type.registerNamespace('comacme.klm.ticketing.visatickets');
    window.alert(Type.isNamespace(comacme.klm.ticketing)); //displays 'True'
    window.alert(Type.isNamespace(comacme.klm.ticketing.Samples.Test)); //displays 'False'
    var namespaces = Type.getRootNamespaces();
    for (var i = 0, length = namespaces.length; i < length; i++) {
        window.alert(namespaces[i].getName()); //displays 'Sys' and  other namespace components
    }
}
</script>

Ok , but we don’t use the Ajax Toolkit so let’s go back to our sample.

In the return section we indicate the public names, so cmdAddNew_onClick and numericSetvMax (functions, but also variables) are public , showEditBox is private and cannot be accessed.

That is in another javascript module we can write

comacme.klm.ticketing.visatickets.cmdAddNew_onClick();

and it works , instead

comacme.klm.ticketing.visatickets.showEditBox('0);

gives error, the internal routine is not reachable.

The variables declared correctly (with “var ” in front) remains private in the module.

This encapsulation , if well used, leads to a structure more maintainable.

Ok , but our original problem is that we could have a lot of existing js code written in the spaghetti-mode, how to convert this code to the module pattern with a minimal effort?

The Regular Expression in the Visual Studio 2012 search & replace is our tool.

We can convert an existing js file with a lot of function to the module pattern with these three couples of regexp expressions for search & replace:

^(function)
var

^(var)(.*)(\()
$1$2 = function $3

^(})(.*)
$1;$2

Open the js file (better is you separe the js code form the aspx pages…) in Visual Studio, in Search & Replace (ctrl+h) click on Regular Expression

And write the first two search%replace strings:

Note that Visual Studio 2012 gives an preview of what the search expression has found (and will be replaced):

After the three search & replace we can see something as:

The last operation is to copy the converted code in the namespace structure (below “use strict”, in practice) , inserting in the “return” part the routines names that must be public.

After, there is a tedious work of searching for the original calls (must be added the namespace part) : but this effort should resolve a lot a problems.

Old Crystal Reports Service Packs

I was searching the Service Pack 7 (the latest) for an ancient Crystal Reports 9: in the SAP (the current owner of Crystal Reports) site is not so immediate to find the things.. this is the link where to find this and others ancient SPs.

Apparently the selection of “Software product” does not work on Internet Explorer 10 , but it works on the latest Firefox.

Select Crystal Reports as “Software Product”, leave * as “Product Version”, select Service Pack on “Software Type”.

From the 4th page are listed the old legacy Service Packs.

SQL Servers no list after crash

While debugging a Sql Server 2008 stored on a Windows 2003 production server (should not be done, but…) SQL Management Studio was stuck, i was constrained to kill the process.

After at the SQL Management Studio login i got “Unable to read the list of previously registered servers on this system”, and at every click …the error .

Sigh..and now? … fortunately in the user profile for SQL Management Studio there is a folder named with a number (100 for SQL 2008) , renaming this folder it was recreated accessing to SQL Management Studio, and no more problems:

Apparently everything is ok, i don’t discover any problem, all works as before the problem.

Unable to get property _focusTabbable

I was working with an .NET 4.5 project which was using jQuery 1.9.0, my js declarations was

<script src="http://code.jquery.com/jquery-1.9.0.min.js" type="text/javascript"></script> 
<script src="http://code.jquery.com/ui/1.10.0/jquery-ui.min.js" type="text/javascript"></script> 

Giving the fact that upgrading the jQuery version in an existing project is risky (see this my old post) i left the old declarations, but when i was verifying that all works as expected (inserting duplicated data) there was this error by clicking on every jQuery button after closing a jQuery dialog:

“Unable to get property ‘_focusTabbable’ of undefined or null reference”

This happened when my js code was simply displaying a message in a JQuery dialog: my work is to change the old , simple, plain “window.alert” with something better as user interface (remember that window.alert is really modal, the code is really stopped at the window.alert: with a modal jQuery dialog no!)

Effectively the Internet Explorer debugger shows the problem:

the object .data(“ui-dialog”) is null so the method ._focusTabbable() fails.

I found nothing of useful by googling, so i tried the very latest jQuery versions (at this moment, April 2013)

<script src="http://code.jquery.com/jquery-1.9.1.min.js" type="text/javascript"></script> 
<script src="http://code.jquery.com/ui/1.10.2/jquery-ui.min.js" type="text/javascript"></script>

With these latest versions of JQuery and jQueryUI the problem is disappeared, and it seems that there no problems related to the jQuery upgrade.

WCF size quota exceeded

While testing a WCF service with the WCF Test client can happens this error message:

“The maximum message size quota for incoming messages (65536) has been exceeded

To increase the quota, use the MaxReceivedMessageSize property on the appropriate binding element.”

In order to increase this quota must be changed the configuration for the web service.

Expanding the project node there is a Config file:

Right clicking the Config file it is possible to access the configuration:

Then appears the configuration, here double click the Binding:

The two values in yellow are the ones that we need to change , it is requested that both are changed and are equals, so we can add for example zeroes in front of 65535 until trying the method the error is not given.

Obviously is better to provide test values that will return a limited range of data, anyway.

There is the menu for Save , in any case closing this window is requested to save.

Returning to the Wcf Test Client is requested to reload the configuration ,

and if there aren’t othe problems the WCF service should work.

If the method return a data table you will see

Clicking on “View…” appears a button with “…” as caption

By clicking this button is showed the table.

Local IIS on internet with the NetGear DGN2200

In order to make tests on my local IIS 7.5 from an external device (Android tablet) i was wondering how to do .

The first thing to verify is the local Windows firewall, but tipically is already opened for port 80 as inbound.

Could be that the provider or the local network locks the port 80 as inbound, and this must be verified: for example in a typical office LAN the 80 inbound is prohibited.

But in this case i’m working with my router, where by default the 80 inbound is not enabled, and must be done the 80 port forwarding.

For a NetGear DGN2200 this is done from the router web interface (http://www.routerlogin.net/start.htm):

in Content Filtering->Firewall rules

The default configuration is:

 
 

 
 

Note that for inbound is all blocked, so click on the Add button for Inbound services and add a record using the “HTTP(TCP:80)” Inbound Service:

 
 

Note that we need to specify the local address obtained from ipconfig in “Send to LAN Server”.

And if the local 192.168… Ip changes must be updated this router configuration.

Now we have :

 
 

And click on Apply.

At this point we can obtain our internet IP visiting for example http://whatismyipaddress.com/

From a device, for example a cell phone with his own data connection (do not try the test with the cell phone connected to the router!) with http://125.23…. (for example, use the IP from whatismyipaddress) we should see the typical IIS default screen, and then , with the complete path, access to hosted ASP.NET web sites.

Still on Office 2013 VBA

In my previous post i activated macros, but every time that the Office app starts there is the request of macro confirmation:

In order to remove this annoying popup is requested to create a certificate, that can be released from an authority or , more simple, the user can create a self signed certificate.

In the Office 2013 installation directory , tipically c:\Program Files (x86)\Microsoft Office\Office\Office14, there is selfcert.exe , a program not included in the start menu.

Launching it is possibile to create a self signed certificate:

After doing this from the Visual Basic for Application environment of an 2013 Office app is possible to install the certificate: menu Tools->Digital Signature

and click on Choose, is proposed our certificate:

After saving , the next time we start the Office app with the macro is activated the button “Trust all documents from this publisher” , after clicking this we will not get requested of our macro authorization.

Office 2013 VBA

In Office 2013 is still possible to use old VBA macros.

In a Office 2013 app menù File->Options->Trust Center->click on button “Trust Center Settings”

Here in “Macro Settings” change to “Notifications for all macros”:

Doing this starts a series of confirmations on various dll, the last request is for yours custom macros.

I’m using macros on Outlook event Application_NewMail() since 2007 version for my specific filtering and management of incoming emails, and the old code is working without problems.