Tuesday, September 1, 2015

Passing parameters & variables into SSIS package when invoked from C# .Net

SSIS has 2 confusing concepts around dealing with variable. There were Variables earlier and now we can see Parameters also.
They are not really same and there are many places in internet talking about it. Below is a simple code snippet on how both can be set if we are executing SSIS package from C# application.

Sample setup

This sample uses a simple SSIS package which does calculation of square for all the number from 0 to N
  • One parameter named 'number'. This will be used as loop's higher end
  • One variable names 'counter'. Used as looping variable
  • For Loop Container - This loop from 1 to number parameter. Initate FindSquareAndLog script block.
  • FindSquareAndLog script block - This finds the square of variable counter and logs.
There is C# code which invokes this .dtsx file

FindSquareAndLog script block

This is part of .dtsx package. Editing the C# code in SSIS Script task in Visual Studio is simple as clicking on the 'Edit Script' button. As seen this is simple C# script which writes to Dts.Log

C# to pass parameter and variable values

This runs from a .Net application which invoke .dtsx package. Very straight forward. Every execution will create an ExecutionInstanceGUID internally. It is captured here. Very much useful if an operation involves series of SSIS package invocation and logs needs to be correlated.

Experiments

Lets try adding new variables and parameters before invoking from the .Net app.

private static void AddParametersIntoPackage(Package pkg)
{
    pkg.Parameters["number"].Value = 3;
    pkg.Variables["counter"].Value = 10;


    pkg.Parameters.Add("dfd"TypeCode.Boolean);
    pkg.Variables.Add("dfd"false"", 3);
    pkg.Parameters.Add("df"TypeCode.Decimal);
}

Can see it just runs though there is no variables or parameters with these names defined in the dtsx pacakge. Try more so that we will end up in a conclusion that dtsx pacakges can be simply generated from our C# code

http://www.sqlchick.com/entries/2013/9/15/getting-started-with-parameters-variables-configurations-in.html
Happy coding


Tuesday, August 25, 2015

How to make .Net 4.0 & 4.5 use TLS 1.2

Basics of HTTP(S)

Unless its not for fun, all the web sites needs to be hosted using http(s). Is http(s) that much important? I will say yes. Otherwise there are chances that our site will be viewed by people with different content than what we published. The web page is not directly sent from web server to the client computer. It goes through various devices which can manipulate the content inside the site. eg: I as a free Wi-Fi provider can inject my own ads into the sites if they are simply using plain http connection. There were incidents happened as well.

Yes it is important to have our web site served via http(s) to make sure our customer are seeing what we are sending them. How http(s) is solving this. Simply saying, it uses encryption when the web page contents are transmitted. So no one can see the content who has access to the intermediate devices. Even if they inject some content, it won't get displayed in our browser as browser will not be able to decrypt the modified contents.

People who are curious on internal will have many questions by now. If our browser say Chrome can decrypt the content why can't someone who has Chrome browser source code decrypt the same to see the contents.

Welcome to the world of security!!! There are concepts of encryption keys. Only people with key can decrypt the encrypted content. Ok in this case if we have the key to decrypt the contents why can the attacker obtains the same key from the web server by accessing the same site from browser? That is solved with key exchange and usage of asymmetric and symmetric encryptions wisely. What is key exchange? It is sequence of steps involved to establish communication between 2 parties and here its web server and client browser. This process is called the secure http protocol.

Its enough and getting more complicated. Interested readers can please go through the links below to understand it more. 

Others just continue on the assumption that http(s) is the silver bullet to make sure what our viewers seeing is what we sent from server.

Versions of http(secure)

Ensuring security is always a continuous process. There are chances that there may be a new flaw in protocol in future and we need to fix that issue. It becomes next version of protocol. When the issue is fixed by specs the existing web servers and browsers also needs to be fixed and it leads to their next version or patch. 

But can we expect all the people in the world updates their browser as soon as the new fixed version or patch is released? Can the new fixed browsers assume that all the web servers its communicated are updated with latest protocol version? Absolutely no. This requires different versions of this communication protocol to be live side by side in the internet. It is the duty of the communicators to settle in what protocol to be used. When we say protocol it includes the encryption details, key exchange details etc...

Wiki will give more details on secure http versions and the latest as of today is TLS 1.2 and TLS 1.3 is in draft.

Fallbacks

As we seen earlier, https is a protocol before starting both parties involved in communication needs to agree on the method. It is not much complicated. Obviously one party needs to tell that what it is supported and the server selects the latest technique. Though one party is capable of using latest protocol, it may not use that as the other end doesn't know and this is called fallback.

Check what is our client browser is using

We saw that http(s) will solve a big security issue. It needs a server side web server software and client side browser software who understands the encryption algorithms. Also it is versioned

Is our browser capable of working with latest http(s) which is TLS 1.2? Just browse to the below url to see what is the protocols supported in our browser.

https://www.howsmyssl.com/

They provide API as well which our application can call and understand what it can support. We will be seeing how an application can check this shortly

https://www.howsmyssl.com/a/check

Check whether web site is using SSL/TLS?

Similarly if we want to check whether a particular site is using secure http go to below url and enter the web site url.

https://www.ssllabs.com/ssltest/

Tools to inspect the network communication

If we want to ensure the protocol at the low levels of system we can use tools such as wireshark and all. Those tools help us to inspect the messages in byte level.


How to host a web site in https

What ever we discussed above was all general and nothing related to software development. Lets see how a web site can be hosted with secure http protocol.

The basic need for this is a certificate which is used as key in encryption. We can create self sighed certificate or buy from certificate issuing authority. Once we have the certificate, it can be associated with our web site via web server. How to associate the certificate with web site changes from technology to technology.  Below is a link which explains how a site can be hosted securely using https protocol in IIS web server.

How to access SSL site from .Net

We can write applications which can request web sites for web pages as well as data in the same way browser application is requesting data. In MIcrosoft .Net there are many ways such as WebClient, WCF etc...

Below is the code which can be used to access https://www.howsmyssl.com API from C#.Net application.

var response = WebRequest.Create("https://www.howsmyssl.com/a/check").GetResponse();
var responseData = new StreamReader(response.GetResponseStream()).ReadToEnd();
Console.WriteLine(responseData);


On console we can see response similar to this.

Hosting WCF Service using http(s)

Hosting WCF using http(s) is not much different than hosting a web site using https. Differences mainly include changes in the web.config file to use Transport security. Below is a link which explains how we can host a WCF web service via http(s)

Accessing http(s) WCF Service from .Net client

Similarly when we are consuming WCF service hosted via https, we need to make sure the service client is configured to use Transport security. Below link explains how a .Net client can access http WCF service. Just change the binding security to Transport in web.config

Why TLS 1.2

Lets come to TLS 1.2. As we have seen above, the latest version of secure http protocol is TLS 1.2. It means there were problems in earlier versions and its fixed in 1.2. So we need to make sure that as a security conscious user our browsers needs to be upgraded to support TLS 1.2. Will upgrading browser make sure all the connections are using TLS 1.2 protocol? 

No if we are accessing a web site which is hosted in a environment without TLS 1.2, our browser will fall back to older protocol version. If we want to avoid falling back to TLS 1.2, we can change the settings in all modern browsers such as ChromeFirefox and IE to always use TLS 1.2 and that is recommended.

More interested readers can google and see what are the changes happened in TLS 1.2 which is based on TLS 1.1.

Obviously as a responsive software engineer, we should make sure all our web applications must be supporting TLS 1.2 which is the latest at this moment. Need to use proper web servers and technologies to support the same. If we are not moving with the crowd it is very difficult to sustain in the market.

Enable TLS 1.2 at OS level

Not every windows operating systems are equipped with TLS 1.2 by default. Below is a link explains how to enable TLS 1.2 in Windows 2008R2,Windows 7 and IIS 7.5. I believe people are still using these versions. 

http://www.derekseaman.com/2010/06/enable-tls-12-aes-256-and-sha-256-in.html
http://tecadmin.net/enable-tls-on-windows-server-and-iis/

In Windows Server 2012 & Windows 8.1 TLS 1.2 is enabled by default.

Not that this will make sure the TLS 1.2 is enabled at OS level. Also the default apps such as IIS web server and IE browser will also follow the same protocol by default. Basically there is a system level dll called schannel.dll which is responsible for this secure communication and applications are calling the API provided by it to have secure communication. Obviously there are applications which are not replying on schannel to do communication. eg:Chroms and Firefox are not using this schannel so they can use TLS 1.2 even when they are running inside Windows Vista.

Similarly when we say Windows Vista, 2008 and below are not supporting TLS 1.2, we should understand that their schannel.dll doesn't have support for the protocol.


TLS 1.2 and Microsoft.Net

Now lets focus on using TLS 1.2 in .Net world. We need to make sure that the web sites are served via TLS 1.2 protocol and client apps which are consuming the same need to support TLS 1.2. .Net is running on top of operating system and mostly its windows. If host windows supports TLS 1.2 .Net can also support TLS 1.2 as it relies on schannel.dll1

The first task here is to make sure we are using the right tools and technologies.

TLS 1.2 and .Net Framework 4.5

.Net is also versioned. Versions below 4.5*doesn't know how to communicate via TLS 1.2.In .Net 4.5 the TLS 1.2 is enabled by default. Simply compile our applications in ,Net 4.5 and we will get TLS 1.2 communication for our applications.

How to make .Net 4.0 app talk using TLS 1.2

Technically speaking just recompile existing older application to .Net 4.5 to get TLS 1.2 support. It sounds simple as everybody expecting that there are no breaking changes in .Net 4.5 and our application will function as is. But if we are serious about delivering quality software we also need to test entire app in 4.5 before shipping. It really adds cost.

Lets see if there are any ways to use TLS 1.2 by .Net 4.0 apps.

System.dll overwrite

When we install .Net 4.5 its basically adding changes on top of .Net 4.0. In other words the System.dll used for 4.0 apps will be overwritten to 4.5 version of System.dll. So there are possibilities that .Net 4.0 apps will execute 4.5 code when they access functions in System.dll. Which means if we have .Net 4.5 installed in the machine where our .Net 4.0 is running it can take advantage of TLS1.2. All our solutions below are depending on this factor.

Below links explains the .Net versioning and overwriting.

1.Code change in 4.0 to use TLS 1.2

Now its the matter of changing the default protocol used by 4.0 to TLS 1.2. This can be done by forcefully changing the  protocol as below.

ServicePointManager.SecurityProtocol = (SecurityProtocolType)3072;//SecurityProtocolType.Tls1.2;


If we look at the SecurityProtocolType enum for .Net 4.0, we will not be able to see the TLS1.2. But in 4.5 we can see that. So .Net 4.0 will not compile if we use TLS1.2 enum value. But if we use the TLS1.2 enum number it will compile and at runtime since the .Net 4.0's System.dll is replaced with 4.5 the cast will work. 

Please note that this will fail if we are running the same app in a machine which don't have 4.5 installed. Recommended only for servers where its easy to manage the .Net version.

2.Registry change to force .Net 4.0 to use TLS 1.2

If we inspect the .Net 4.5 ServicePointManager source code we can see that the default protocol is depending on the below registry entry.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319: SchUseStrongCrypto to DWORD 1

The default value will be 0. Simply change this to 1 to get .Net 4.5 System.dll use TLS 1.2. Since our 4.0 application uses 4.5 System.dll 4.0 gets TLS 1.2 support.

http://stackoverflow.com/questions/28286086/default-securityprotocol-in-net-4-5

References

https://www.owasp.org/index.php/Transport_Layer_Protection_Cheat_Sheet#Client_.28Browser.29_Configuration
https://www.simple-talk.com/dotnet/.net-framework/tlsssl-and-.net-framework-4.0/
https://msdn.microsoft.com/en-us/library/system.security.authentication.sslprotocols(v=vs.110).aspx
https://istlsfastyet.com/
http://blogs.msdn.com/b/benjaminperkins/archive/2014/11/04/using-tls-1-2-with-wcf.aspx
http://blogs.msdn.com/b/benjaminperkins/archive/2011/10/07/secure-channel-compatibility-support-with-ssl-and-tls.aspx
http://www.dotnetnoob.com/2013/10/hardening-windows-server-20082012-and.html


1 - This is as of my understanding. Was not able to get any authentic links.
*-As far as my understanding TLS 1.2 support came from .Net 4.5. I am using 4.5.2 currently.

Tuesday, August 18, 2015

Multiple ways to iterate an array in Javascript

Javascript now seems the way to survive in software industry at least for the next 5 years. I have written a post earlier describing why developers should invest time in learning good parts of Javascript. This is small post where I would like to show how many ways are there in Javascript to loop through an array.

The purpose of this post is not to compare and find out which is the best way. For that please refer this link. Lets start.

Simple javascript JSON array

The iteration needs an array at the minimum. Below is an array which we are going to use.

var strings = ["Joy", "George"];

When we iterate we need to have a test mechanism to show the output. I am just defining an output function which handles it. For the time being we are showing in alert box.

function output(msg) {  
     alert(msg);  
 }
Now lets start exploring different ways to iterate this array.

  1. Using for(;;)

  2. This is the simplest way to iterate any collection. It uses a index pointer variable and gets element from the array using that pointer.Below is the code which does it.

    $scope.loopStringsUsingFor = function () {
            for (i = 0; i < strings.length; i++) {
                output("i=" + i + ", strings[i]=" + strings[i]);
            }
    };

    We can see that there is a $scope variable and the function is assigned to it. This is because I am using AngularJS in jsFiddle.net and this is the way I can associate click handler. The jsFiddle.Net linke is given at the end of this post. It will help to see this code running and play around with the same.
    The output will be 2 alert boxes with the below content respectively.

    i=0 , strings[i]=Joy
    i=1 , strings[i]=George
  3. Using for(var in array)

  4. This is similar to the iteration model available in other languages such as C#. It does not need the index variable. But in Javascript, its not working exactly the same way it works in other languages. Lets see the code below

    $scope.loopStringsUsingForIn = function () {
            for (s in strings) {
                output("s=" + s + ", strings[s]=" + strings[s]);
            }
    };

    The difference with the other languages is clear. In other languages s will be the element in the array. But here s is again the index and we need to use that index to get the string object. The output will be

    s=0, strings[s]=Joy
    s=1, strings[s]=George

    If people are coming from other languages, they will expect string object to be available in the s variable but it will never be. 

  5. Using for(var of array)

  6. This is more similar to the iterations of other languages such as C#. But unfortunately this is available  only from ES6 specs which is released in 2015. If we are targeting application towards global audience, this may not work. See the browser compatibility for more details.

    $scope.loopStringsUsingForOf = function () {
            for (s of strings) {
                output("s="+s);
            }
    }

    The output will be 
    s=Joy
    s=George
  7. Using Array.forEach(function(element,index))

  8. Here we are iterating the array and for each element in the array the function which is passed as argument will be invoked. The element will be the actual string value and index will be the pointer. Lets see how this works

    $scope.loopStringsUsingArrayForEach = function () {
            strings.forEach(function (element, index) {
                output("index=" + index + ",element=" + element);
            });
    }

    The output will be as follows

    index=0,element=Joy
    index=1,element=George
  9. Using Object.keys(array).forEach(function(element,index))

  10. This iterate through the keys in array. Keys is again an array. Which means we need to find the actual object using indexing mechanism. See the code below

    $scope.loopStringsUsingObjectKeysForEach = function () {
            Object.keys(strings).forEach(function (element, index) {
                output("index=" + index + ",element=" + element + ",strings[element]=" + strings[element] + ",strings[index]=" + strings[element]);
            });
    }

    Since its looping through the keys of array it will return index as well as element as 0,1,..Then the indexing mechanism brings the actual data. The output will be as follows

    index=0,element=0,strings[element]=Joy,strings[index]=Joy
    index=1,element=1,strings[element]=George,strings[index]=George

  11. Using Array.map(function(element))

  12. This is another method to execute a function against all the elements in a array. Lets see the code 

    $scope.loopStringsUsingMap= function(){
            strings.map(function(s){
                output("s="+s);
            });
    }.

    The output will be

    s=Joy
    s=George

  13. Using Array.filter

  14. This technique again uses another method named filter defined on Array. Below is the code

    $scope.loopStringsUsingArrayFilter = function () {
            strings.filter(function (element) {
                output("s=" + element);
            });
    }

    Output is same as
    s=Joy
    s=George

  15. Using Array.every(function(element,index))
  16. This is not intended for iterating. But for testing all the elements in the array for condition. If any element fails on test, it won't iterate to next element. The return boolean value indicate whether the test is success or not. If we return false or don't return any value the iteration will not continue to next element. Instead it just stops. Code below

    $scope.loopStringsUsingArrayEvery = function () {
            strings.every(function (element, index) {
                output("element=" + element + ", index ="+index);
                return true;
            });
    }

    Output will be
    element=Joy,index=0
    element=George,index=1

  17. Using Array.pop()

  18. This is not exactly iterating the array. But its taking each item from the array. Once this loop is completed the array will be empty.

    $scope.loopStringsUsingArrayPop = function () {
            while (s = strings.pop()) {
                output("s=" + s);
            }
            output("length of strings="+strings.length);
    }

    Output will be
    
    
    s=George
    s=Joy
    length of strings=0
    
    
    There are more methods on Array which we can use for iterating. Please refer the documentation for more details.
  19. Using Symbol.Iterator

  20. This specification came in ES6 in 2015. So only very latest browsers support this. Code goes as below.
    $scope.loopStringUsingIterator = function () {
            var iter = strings[Symbol.iterator]();
            while (s = iter.next().value) {
                output("s="+s);
            }
    }

    Output will be
    s=Joy
    s=George
  21. Using angular.forEach(array,function(element))

  22. Now most of us would be in a state of confusion which one should use. How to make sure which is supported where etc...This is the exact time frameworks comes to help us. Most of the frameworks provide a mechanism to iterate arrays. The below code snippet is applicable only if we are using AngularJS script is included in the html page.

    $scope.loopStringsUsingAngularForEach = function(){
            angular.forEach(strings,function(s){
                output("s="+s);
            });
    };

    Output will be 

    s=Joy
    s=George

    This works in most of the places. Similarly jQuery has mechanism to iterate arrays.
If we use these frameworks we can relax from these repeating general concerns of will this work in other browsers and focus on our business problems and features. Why should we bother to a problem which everybody faces and solved already?

There are more complicated scenarios when we deal with complex objects. Explaining every scenario will be very difficult within a post. Will try to address complex objects related things in another post.

Please go to the jsfiddle to see the complete code

Tuesday, August 11, 2015

www.opencontainers.org - Standardizing the software containerization

Good to see that companies are uniting to create a single open source standard for packing software. It started June 22nd,2015. Right now different vendors such as Docker, Inc has their own standards and it would be difficult to get our software ported to another vendor. This open standard should solve this issue.

All the best to http://www.opencontainers.org/

Tuesday, August 4, 2015

SSIS - Index of features

  • Creating package
    • Programmatic creation - https://msdn.microsoft.com/en-us/library/ms345167(v=sql.110).aspx
  • Variables & Parameters
    • Inbuilt System variables list -https://msdn.microsoft.com/en-us/library/ms141788(v=sql.105).aspx
    • System::ExecutionInstanceGUID - The unique identified generated each time a SSIS package executed. This id can be used to correlate the operations and logs in SSIS package.
  • Control flow
    • If condition in package - No out of the box support. Add expression into Disabled property of task
    • For loop http://www.bidn.com/blogs/TomLannen/bidn-blog/2640/ssis-for-loop-containers-part-1
    • Script task
      • Using variables and Parameters in C# script task.
  • Event handling
  • Running package
    • Run the package and capture events using C# .Net.
      • https://msdn.microsoft.com/en-us/library/ms136090(v=sql.105).aspx
    • Run the package by passing parameter values.
    • Run the package by modifying logging file path connection
    • Run the package by injecting logging provider and connection manager
      • https://msdn.microsoft.com/en-us/library/ms136023.aspx
  • Logging
    • Logging from C# script task
      • https://msdn.microsoft.com/en-us/library/ms136131(v=sql.120).aspx
      • Enable logging of script task log entries
    • Logging into sysssislog table.
  • Extending SSIS

Tuesday, July 28, 2015

Best practices in SSIS Logging


  • Log always using SSIS logging mechanism
  • Do not reinvent logging mechanism using any manual methods even in C# script tasks
    • Script task to log into SSIS logging stream
      • http://dataqueen.unlimitedviz.com/2013/09/how-to-enable-custom-logging-for-an-ssis-script-task/
      • https://msdn.microsoft.com/en-us/library/ms136131%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
  • Inject the log destination from invoking platform. This will help to correlate the SSIS logs with other application logs.
    • eg: If the SSIS is invoked from C# application let the C# provide the logging connection string.
  • Better log all the events and point to database as logging store.
  • Have periodic routine to cleanup the logs.

Above are the best practices as of my understanding and experience with SSIS. Sorry to say that, I cannot guarantee that it will work for all.

Tuesday, July 21, 2015

Do we need SQL Server Integration Services running to execute SSIS / .dtsx package?

Question - Do we need a SSIS windows service to execute .dtsx file?

Here is the scenario. We have a .dtsx package file and need to execute it. Should we need to have SQL Server Integration Services running in our machine to execute that package?

Answer - No

Lets look at what is SQL Server Integration Services. By definition

"The SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages" 
From Microsoft KB https://support.microsoft.com/en-us/kb/942176 & MSDN Page

It clearly says we don't need need SSIS running in our machine to execute a package. We simply need the DTExec.exe utility to run .dtsx packages. So what is the use of SSIS windows service?

It is just to keep the .dtsx packages safe, initiate, monitor and stop the execution. It really means that there can be different users and roles who can only execute packages. Also helps to control the packages in remote machine.

Question - Do we need SQL Server running to run integration services package(.dtsx)

The SSIS is related with SQL Server. So do we need to have any SQL Server instance running, if we want to run a .dtsx package?

Answer - No

Though the Integration Services is for data transformation, it is not developed with tight integration with SQL Server. We can develop any algorithm using integration services. As seen above DTExec.exe is the execution utility. It doesn't need any SQL Server instance running to execute dtsx package is the package is not establishing connection with SQL Server.

What is the confusion?

Why people often confuse .dtsx package with SQL Server? The reason is mainly everybody thinks that since Integration Services are for ETL and data related operations, it must be linked with SQL Server and SSIS. Another reason is the expansion of SSIS starts with SQL Server. The better way is to start calling it as simply "Integration Services". As of my understanding even MSFT has the confusion. Some places they use simply Integration Services, some places Microsoft Integration Services and sometime refer full SQL Server Integration Services. So we cannot blame anybody for getting confused :)

So lets consider this as another technology to achieve workflow execution platform recommended for building enterprise-level data integration and data transformations solutions. This technology has many tools and components to help us in various phases of development and deployment. 

BIDS - Business Intelligence Development Studio

The tool which is used to create the workflow using drag and drop mechanism. It is Visual Studio itself. It gives option to define parameters, variables, connections, log provider and edit the script tasks. It captures the workflow steps in the form of xml and saved as simple file with extension .dtsx.

.dtsx

The file extension of file which contains the workflow in xml. This format is open and XSD can be found in MSDN. This file can be imported to SSIS package store to have better management. Integration Services .(dtsx) files can be created either from BIDS or using our own program. 

DTExec.exe

Utility used to to execute any .dtsx package either stored in file system or SSIS package storage. Parameters are passed via command line. Location
[Drive]:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe

DTExecUI.exe

GUI utility to run .dtsx packages. Provides UI to enter the package parameters. Located at
[Drive]:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\DTExecUI.exe

SSIS

A windows service used to store and track execution of Integration Service packages (.dtsx)

Microsoft.SqlServer.ManagedDTS.dll

This is the .Net managed dll which provides API to create and execute .dtsx packages. This contains the namespace Microsoft.SqlServer.Dts.Runtime which holds the relevant classes. Located in below location if 2012 is installed

[Drive]:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll

References

https://en.wikipedia.org/wiki/SQL_Server_Integration_Services
http://stackoverflow.com/questions/454802/what-is-the-ssis-package-and-what-does-it-do