Sunday, 15 October 2017

DTS package conversion in SSIS to use in SQL 2012 and SQL 2016

In my company my team decided to migrate whole SQL 2008 to brand new SQL 2016 in Azure VMs. This was a good idea as we should always be up to date in terms of technology.
We did migration all SQL 2008 to SQL 2016 without any problem but suddenly we saw we need to migrate warehouse as well and it was containing very complex DTS package almost 50 in number and we have very less time to finish this task.

We first decided to do following things:

Upgrade DTS in SSIS with wizard available in SQL 2008

But this was not helpful because if you have some VB script written in DTS and it enables or disables any step then it will not work in SSIS because enabling and disabling is not allowed in SSIS. YOu will have to manually correct all these code. So we tried this and dropped this idea.

Upgrade DTS in SSIS with wizard available in SQL 2008

Manually Convert whole DTS in SSIS:

This was blunder for us as it was going to take many months of re creation and testing so tried this and dropped this idea as well because we got a very motivational article over internet 😂😁 .

How to Run DTS in SSIS?

When you will search this over internet people will suggest you there is no success in this way and you will ave to convert DTS in SSIS manually no wizard will help you to do so but they are not correct because we saw a very complex DTS package which was calling other DTS package as its child and again those childs were calling further their childs DTS package.
Microsoft provides a way to run DTS in SSIS.
You just need to provide correct variable values and you will be able to run this package in production without any problem.

How to Run DTS in SSIS

Sunday, 2 July 2017

SQL Server Failover Clustering in SQL Server 2016 with DNS in Azure

As we know that database mirroring is no longer supported in the latest version of SQL Server i.e. in MS SQL Server 2016.
So people are moving toward SQL Server failover clustering as they want to achieve High Availability of database.
Here we will setup the things in Azure cloud service, you can enjoy your free subscription of Azure cloud for 1 month.

So we can do this in two ways:
1. by using DNS Server which will cost you extra server need more money and maintenance.
2. By using workgroup i.e. without DNS Server this will take less cost in comparison to first one.
Best way to do implement SQL Server failover clustering

Its all up to your requirement if you have low budget then go without DNS and if have sufficient budget then go with DNS Server extra.
Both ways have their own pro and cons so we can't rigid with one way.
Steps by Steps to create a SQL Server Always On Availability Group on Azure Virtual Machines.

Create resource group

1)     Sign in to the Azure portal.
2)     Click + to create a new object in the portal.
3)     Type resource group in the Marketplace search window.
4)     Click Resource group.
5)     Click Create.

6)     In the Resource group blade, under Resource group name type a name for the resource group.  

For example, type SQLTEST-RG-WestUs


7)     If you have multiple Azure subscriptions, verify that the subscription is the Azure      subscription you want to create the availability group in.
8)     Select a location. The location is the Azure region where you want to create the availability group. For this tutorial, we are going to build all resources in one Azure location.
9)     Verify Pin to dashboard is checked. This optional setting places a shortcut for the resource group on the Azure portal dashboard.
10)     Click Create to create the resource group.

Create availability sets:

      Before creating virtual machines, you need to create availability sets. Availability sets reduce downtime for planned or unplanned maintenance events. An Azure availability set is a logical group of resources that Azure places on physical fault domains and update domains. A fault domain ensures that the members of the availability set have separate power and network resources. An update domain ensures that members of the availability set are not brought down for maintenance at the same time.  To create an availability set, go to the resource group and click Add. Filter the results by typing Availability Set. Click Availability Set in the results. Click Create.
       After you create the availability sets, return to the resource group in the Azure portal and create three SQL Virtual Machine (2 SQL+ 1 DNS)
       It will take few times to create all three machines and then do following steps:

1. Make one server as DNS server and to configure DNS please follow following steps:

Install DNS Role first by using Server Manager you will get link to install new role and feature.
You need to configure  new DNS server if you don't have already Follow this link to set up DNS





Saturday, 19 November 2016

Entity Power Tool for Visual Studio 2015

EF Power tool is widely used for reverse engineering in MVC Projects to create Model and Mapping which takes more time for developers when do manually.
Since EF Power tool is initially designed for VS 2010, 2012, and 2013. You can see it on Microsoft website where compatible version is clearly mentioned.
So here I am going to tell you how you will install EF Power tool in VS 2015.

Step 1: Just download the file click to download and simply Install it by just double-clicking on it and then Next Next.

Step 2: Now when You will do reverce engineering you will get error

One or more errors occurred while processing template 'Entity.tt'. 
To solve this error you can just change the Target framework 4.1.5 from your current version.


It will solve your problem.

When you have done with reverse engineering then again change the Target framework.

Thursday, 4 August 2016

How to use AngularJs with MVC C#

So this tutorial is related to JSON data

Download the project file form here
What we will do in this tutorial?

1. Will know what is JSON data?
2. How to get JSON data returned from a method in C#?
3. How to use them in AngularJs or in Javascript?

First start with  what is JSON data?

As per Json.org it is Javascript Object Notation which is very lightweight and easy to use for machine and us also.
For more details visit : http://json.org/

How to get JSON data returned from a method in C#?
So yo can see video here and get to know how it actually works.
Steps are as:


  1.  Take a empty MVC project and name it what ever you want.
  2.  Create a new model StudentDetails (or what ever you want you are free to do so)
  3.  Take a controller and it will be good to take Default for learning purpose
  4.  Create A JsonResult (Represents a class that is used to send JSON-formatted content to the response.) Type method as mentioned in Video
  5.  Create instance of model and assign value to its property.
  6.  and get returned value by calling that action method from web browser.




How to use them in AngularJs or in Javascript?

So in this example we take real life scenario in which we develop real time web application using AngularJs and MVC C#.
First of all we need to setup angularJs in our application file to do so follow these steps:


  1.  We need to add all script files of angularJs in Script folder of our application for reference of Angular and Jquery library.
  2.  When we will create Controller and Factory method then we should not manually give reference to these files.
  3.  We need to implement Bundeling using BundelConfig.cs file in App_Start But here we are doing it manually as this is for example purpose for quick start.
  4.  We need a Global.js file that we store all global static value fr our Ui application Like some Enum value or MVC controller URL.


No we have added all files of related to AngularJs in our application so we are ready to do.


  1. Create Static Folder in project >> JS folder >> Controller Folder, Factory folder
  2.  Add app.js, Global.js files in Static folder app.js file will holde app module of our application in which we will pass our all dependency.
  3.  Global.js file to hold constant value like MVC Controller URL.
  4.  Inside Controller folder we will create our AngularJs controller and Factory methods in Factory folder (Factory method will be used to create Http request to server)
  5. Now we have a _Layour.cshtml page that will act like master page for our application and all reference to Js files goed there. (You can see in video)
  6. We already have MVC method to get data.

Just need to implement this in our angular project.


So we are going to get data in run time so our controller will access Factory service method to hit MVC controller method. To access this Factory service method we

will have to dependency of Factory in app.js file.
After this we should create controller are of angular by doing ng-controller in cshtml file of our view.

I assume you must have some basic introductory knowledge of AngularJs like what is ng-app, ng-controller and how to render angular data.

So just use this in your application and hope you will enjoy this article.

Friday, 13 February 2015

Insert Unique Value in MS SQL Database By Using SQL Stored Procedure

When we enter some data in database then its repetition is major problem for at all. Here I am using a table with Mobile No. as primary key and trying to insert unique value to it.

You can see database in this image.
 
Stored procedure wants phone no as a parameter which can be passed easily

create procedure insert_unique
(
@phone numeric(10,0)
)
as
begin
INSERT INTO
PhoneData(Phoneno)
    SELECT @phone
WHERE NOT EXISTS (SELECT phoneno FROM PhoneData WHERE Phoneno = @phone)
end

Pass your phone no in this procedure and it will insert only unique record.

Wednesday, 28 January 2015

Interview Questions on .Net Part: 1



Abstraction

Abstraction means displaying or allows access to that part of program or class which is important. Take an example.

How to achieve that?

Take the below example in which I am declaring four string and out of them only one is public and rest are private or protected. When we create object of this class and try to access all these variables then we get permission to access only public variable.


class AbstractionWaliClass
    {
        private string my_id;
        private string my_name;
        public string my_contact_no;
        protected string protected_id;
    }
 

Abstract Class:

If you want to create a class that is only base class.

No one can make instance of it. Then you should use Abstract class with the help of keyword ‘abstract’.

abstract class AbstractClass
    {

    }

Now what inside this class?

Any method doesn’t necessary it should be abstract method but it can also contains non abstract method.


public abstract class AbstractClass

    {
        public int my_first_method()
        {
            return 1;
        }
        public abstract void second_class();
    }
  


Now in main method code like this:

class Program:AbstractClass
    {
        static void Main(string[] args)
        {
            Program obj = new Program();
            Console.WriteLine(obj.my_first_method());
            obj.second_class();
            Console.ReadLine();
          
           
        }
//Here we are declaring the abstract method named secomd_class and writing code by overriding the abstract method
        public override void second_class()
        {
            Console.WriteLine("this is abstract method");
        }
      
    }

 
Know more about abstract class:

1. It cannot be sealed because sealed class is restricted to be inherited.

2. While overriding the abstract method, method type should be same as  you can see here that second_class() method is void in both condition.

3. Abstract method should not be private since it will be gain in accessible due to private keyword.

4. Abstract method is by default public.

5. Constructor and destructor can also be in abstract class but they should be private or protected but not public.

6. You will have to implement all abstract method in your class if you are deriving it from abstract class. Otherwise it will show compile time error.