?

Log in

In MSBI while working on SSIS projects we have to run repetitive task which has to carried out daily like running package or to take weekly backup and making such task as automated is the

smart choice. For this we have expert present within SQL Server often commonly referred as SQL Server agent. In this article we will step by step how to create job using SQL Server agent.

Use of SQL Server Agent


  • SQL Server Agent is a service which is used to perform jobs or tasks in a Scheduled manner. Now for example if we want to update our SSAS cube whenever our Fact table gets updated, so we create a job which will run on a fix schedule and that job will update the SSAS cube. So for this thing we use SQL Server Agent.

  • So this SQL Server Agent is a service or a utility provided by SQL Server to do jobs or processes in background in a scheduled manner.

Steps for creating the Job:

Step 1: First of all start the SQL Server Agent in the object explorer. Now we can start this service from the object explorer in SQL management studio and also we can start this service from the services in our machine.




Once the SQL Server Agent is started it will show green color sign. Here in the above image we can see that the SQL Server Agent is already started.

Step 2: Now go to the Jobs folder in the SQL Server Agent and select the option new Job.


Then we will see the following window :-


We will give a name for our job.

Step 3: After giving the name we will then write down the steps which will be there in our job, so that when our job is executing these steps will be executed as well.



Now in the step we will have to copy paste our XMLA query and select other options like,


Step 4: Now after creating steps we will now create a schedule so that our job can run in the background.




Now we can fill all the details as per our convenience or choice what will be our schedule, when the job will repeat itself, etc. we can see in the above image what all fields have to be filled.

After creating the schedule, all the steps are completed and then we can select OK and our Job will be created. We can see our created job in the Jobs folder in SQL Server Agent.



By following the above steps we can create a job and also we can run XMLA query in SQL Server Agent.

Invoking SSAS cube processing from SSIS:

We can also make a SSAS cube in our SSIS and process the cube from SSIS. To do that we can follow the steps here:

Step 1: Create a new SSIS package and then from the SSIS toolbox and select the component “Analysis Services Processing Task”.




Step 2: Now we will configure the “Analysis Services Processing Task”.




Here in the configuration we will give our analysis connection name and add the object lists, like which all objects Facts, dimensions we want to add. In short it asks us what objects we want to add for processing.


And that’s it; these are the steps for invoking SSAS cube through SSIS.


Below is one start-up video for SSIS to start with project: -

Copy right www.questpond.com

Learn Buffer Size Tuning in SSIS.

Buffer Oriented Architecture:

Now in SSIS usually there are many different components present from which the records or the SQL data passes. Now if a SQL has a tale which contains 10 records then it does not mean that all these 10 records will go one by one from one component to other. This assumption of ours is wrong. The Data passing from one component to other follows Buffer Oriented Architecture. It means if there are 10 records, then all these 10 records will be present in side a buffer and that buffer will transfer from one component to other.

Hence all the records combining to form a Buffer and then transferring it from one component to other is known as Buffer Oriented Architecture.






  • Now as the SSIS follows Buffer Oriented Architecture, it is very important to make sure in our program what will be our buffer size. Because buffer size will matter when it comes to performance tuning.

  • If our buffer size is not according to the calculation and is in random manner it will affect the performance of our application. And this will not help in the real time production environment. So we have to make sure that our buffer size should not be too big or too small but should be optimal according to our application.

  • Hence determining the optimal buffer size is known as Buffer Size Tuning.

  • Now to see where are these buffer size values are present we have to go to the properties in the Data Flow tab and there we will see the Default Buffer Size and the Default max rows which fit in the a buffer.





Different ways of processing the data from component:

Now when there are more components in a SSIS package and there are many complications, the processing of the rows of data from one component to other happens in three types.

Full Blocking: In this type the data does not move ahead to the next component until all the rows are processed. It means that after processing all the rows then and only then the data will move forward.

Semi-blocking: In this type some of the rows are processed and moved forward and some of the rows are processed but are blocked. Hence it is known as semi-blocking.

No-blocking: In this type as soon as the rows are processed they are moved forward or sent to the other component without blocking any rows. Hence it is known as no-blocking type of process.


  • Now for deciding the optimal buffer size which helps us in getting a better performance for the application, the important factor is reviewing the data types. Now the reviewing the data types are very important because if we have rows with “char” data type then the row size will be different and according to that the optimal buffer size will also be different.

  • Now if our row has “nvarchar” data type which is used for Unicode data has row size different and according to that our buffer size will also be affected. Now we know that SSIS has its own data types so it is necessary to check that the data type from our database and SSIS should be the same.

  • And therefore it is necessary to review the data types for optimal buffer size to increase performance of the application.

Buffer Calculation Policy:

Now in every project we should have some kind of SSIS Buffer Calculation policy which has some kind of formula which will help us in determining the optimal buffer size. So every project should have this kind of calculation.

Above is the advanced topic of SSIS if you want to brush-up SSIS project in MSBI do view the following video: -

Copy right www.questpond.com

Learn Transaction and Checkpoints in SSIS.

What is Transaction?

Transactions all are success or all are fail. If problem in any one component occurs it will fail the component. Transaction by default is not enabled in a SSIS package. You can create a transaction that includes any task or container. Transaction helps us to logical group activities. So activities which are the part of that logical group either all pass or all will fail.

Transaction option is of three types.

Not Supported: It is not support transaction. He is not going to create any transaction. He will not join any exiting transaction. He indicated that the container does not start a transaction or join an existing.

Supported: it will join the transaction, if transaction is exists by default. Parent sequence container has created transaction he goes and joins the transaction.
Indicates that the container does not start a transaction, but joins any transaction started by its parent container.

Required:parent sequence container has created a transaction and if you select required here. He will create his own transaction, that mains PSC will also have own transaction logical unit and this Child container separate logical unit.
If the parent sequence container (PSC) has not enable transaction than he got create his own new transaction.
Indicates that the container starts a transaction, unless one is already started by its parent container.

How to go enable transaction?

We have a container “Sequence container” under the SSIS Toolbox. The Sequence container can include multiple tasks in addition to other container. Providing scope for variables that a group of related tasks and containers use. See the below screen shot…



Move all three components from Control Flow into Sequence container, and then it becomes one Container as see in the below screen shot…



Now configure the “Sequence container”. To configure, right click on the “Sequence container”, go to the property as show in the screen shot…


To enable transaction we will say “required”. Required mains the entire .dtsx package now in one logical transaction as see in the below screen shot…



When you run the project we have faced the error as show in the below screen short…



In order to solve the error, follow...

Press the “window+R” then Open the run window and type “services.msc” and click “OK” see the following screen shot…


Now open the “services” window and select the “Distributed Trinsaction Coordinator”. Than right click on the “Distributed Trinsaction Co-ordinator” and “Start” the services.



Now project will run see the below screen short…


Check point

Checkpoint start from the last failed point where as in transaction is all fail or success. Checkpoint and transaction is apposite to each other. Start from the point where the fail. Do not start first point.

When you configure your SSIS package to use checkpoints, SSIS maintains a record of the control flow executable that have successfully run. In addition, SSIS records the current values of user-defined variables.

Check point are enable in SSIS at the component label. It will not track check point at row label. Check point in SSIS are enable at component label. So it can restart from component and not from row. If you want to do check in row label than you can use the “SQL Server check point”. SSIS check point internally use SQL Server check point.

It you want to do check point, you have to disable the transaction (Transaction➜ all of them successful or all of them fail)

If you have a problem in the “Product table” like that…


Where “ProductID” is not numeric, then application is not running as show in the below screen shot…


Now you can see that Country Table is executing but “Product table” is not. To solve the error Right click on the control flow and go to the property. Click to the “CheckpointFileName” as

see the below the screen shot…


When you click to three Dots (…) then create the .txt file and “Open” as see the below screen short…




Then click on the “ChangePointUsage” and configure as following screen shot


Then configure the “SaveCheckPoint” follow the screen shot…


Now also configure the “FailParent OnFailure” is “True” as show in the screen shot.


It will save the ID of the component on notepad. Then it show which of my component is failed as it is shown in the below screen shot…


In the product table my component is failed. Also it is showing the ID in the notepad CheckPointFile.txt file.

Check point track an entry label not in the row label. He can’t go to row label. If you want to check in row label than go to “SQL Server Checkpoint”.

Below is the one fine video for MSBI learner and hope reader will like this project video: -


Copy right www.questpond.com

With SSIS having Merge Join, Inner Join, Left Outer Join, and Full Outer Join can be achieved. in this article we will practically demonstrate same step by step.

Merge Join

Merge joins like a union. It combined two records and creates one set of record.


  • Specify the join is a FULL, LEFT, or INNER join.

  • Specify the columns the join uses.

Now, you create the “Data Flow Task” by Dragging into the Control Flow. Double click on the “Data Flow Task” it will go to the “Data Flow” and go to the “Other Source” under the “SSIS

ToolBox” then drag and drop the two “Flat File Source” into the “Data Flow” and configure the both “Flat File Source”. Then also drag and Drop the “Merge” under the “Common” and one “Flat

File Destination” component under the “Other Destination” as show in below screen shot…


Double click on the “File 001 Flat File Source” and Click on the “New” as show in below screen shot…



Create a new connection and select the “File Name” then click “OK” as show in below screen shot…



When you create the connection manager than check the “Columns” all “available External Columns” are check or not. All properties are right than click OK.
Tack the “File 001 Flat File Source” and give as input to the merge component as show in below screen shot…



Then go to “Merge component” and say Edit as Shown in below screen shot…


Now, double click on the “Flat File Destination” and say “edit”. Than create the new connection and give the “connection manager name”. Create the delimited txt file which wills final output as shown in below screen shot…



Now, click to OK than run the project.

Output

SSIS package should be complete. To run it to make sure everything is working. To run the package click the “Start” on the menu bar.


See the output value as shown in below screen shot…


Inner join

Includes rows only when the data matches between the two tables. It will only display the record which is match. It will only go and join that record which is only exiting in the both join. It will send the matching record.

To add the “data flow task” to the control Flow, drag the task from the toolbox to the Control Flow design as shown in below screen shot…


Double Click on the “Data Flow Task”, it goes the Data Flow item section. Then add the two “Flat File Source”, “Merge join” and “Flat File Destination” on the “Data Flow” drag the task from the toolbox to the “Data Flow” design as shown in below screen shot…



Double click on the “File 001 Flat File Source” and Click on the “New” as show in below screen shot…



  Create a new connection and select the “File Name” then click “OK” as show in below screen shot…



When you create the connection manager than check the “Columns” all “available External Columns” are check or not. All properties are right than click OK.
Tack the “File 001 Flat File Source” and give as input to the merge component as show in below screen shot…


Then go to the “Merge join Component” and configure as show in below screen shot…


When you configure the “Merge join” than click ok.

Now, double click on the “Flat File Destination” and say “edit”. Than create the new connection and give the “connection manager name”. Create the delimited txt file which wills final output as shown in below screen shot…


Now, click to OK than run the project.

SSIS package should be complete. To run it to make sure everything is working. To run the package click the “Start” on the menu bar.




Left outer join

Includes all rows from the left table, but only matching rows from the right table. You can use the Swap Inputs option to switch data source, effectively creating a right outer join.

Merge left join will display all the records of the left table. It will match or not match those value send to the output.

We want to do “Merge Left join” than drag the “Merge join” in the “Data Flow” and configure as show in below screen shot…



Now, click to OK than run the project.
SSIS package should be complete. To run it to make sure everything is working. To run the package click the “Start” on the menu bar.





Full outer join➜

Includes all rows from both tables. He displays all the record which record is match or not match.

Whenever the record matches or not matches in both file and table. It will show the all record. When you want to do “Full outer join” than go to the SSIS Toolbox and drag the “Merge join” into the “Data Flow”. Than configure as show below screen shot…



Now, click to OK than run the project.
SSIS package should be complete. To run it to make sure everything is working. To run the package click the “Start” on the menu bar.

Output




Also go through below SSIS video which is project series oriented: -

Copy right www.questpond.com

QuestPond’s Best Practices in ASP.NET MVC

What are best practices that need to be followed in ASP.NET MVC?

Whether it is ASP.NET MVC, WCF or ASP.NET Web Forms when it comes to best practices its endless.

Let me share couple of practices I followed during ASP.NET MVC development.

    1. Keep Model as separate class library.

    2. Implement Repository pattern to isolate Business layer from Data Access Layer.

    3. Follow MVC architecture properly
                  a. Controller should contain only user interaction logic
                  b. View should contain only UI Design

    4. Use ViewModel and isolate View from presentation and Data Transformation logic.

    5. Always have base controller created. Means make sure your controllers are not directly inheriting from
         Controller class instead there should be an intermediate base controller
         Example:
         public class BaseController: Controller{….}
         public class CustomerController:BaseController{}

    6. For Generating input controls in view always use Html helper class methods.

   7. Always put appropriate Http verbs on action methods. Example: If it is a post action
        HttpPost and if it is Get put HttpGet action. By default it will be both and it should not be.

   8. Strictly follow the traditional best practice, avoid magic strings. Magic strings means
        writing something in Double quotes.

   9. If there is an action method which returning same result many times, apply caching into it.

10. Action methods with HttpGet attribute attached should not be performing an operation.
       Example: it should not delete, update any record. In short Get methods should perform
       anything which looks like Post.

11. Avoid putting hardcoded Action URIS.
       Example :
       Instead of
                        Html.Form(“~/MyController/MyAction”,FormMethod.Post”)
       Use
                        Html.Form(“ActionName”,”ControllerName”,ForMethod.Post)        

12. Always include bundling and Minification in your application.

13. All your view should be strongly typed

14. Use Model Binder for updating Model class object with posted data instead of manual update.

So the story of best practices doesn’t ends here we have more complete detailed article on best practices in .NET

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla
Copy right www.questpond.com

Multi Targeting in .NET

From VS 2008 they have added multi-targeting feature. You can use same visual studio with different versions of .NET framework. So in previous versions of Visual studio, every version was tied-up with its framework. For example VS 2003 only works with 1.1 framework whereas VS 2005 works with 2.0.

Project created in VS 2008 or after that onwards edition if you click on project properties you can now change the framework version to different types and check if your code is compatible with old version.

.net

Above is a brief explanation for Multi Targeting. Click to learn Multi Targeting topic in more detail.

Copy right www.questpond.com

What is Inversion of Control (IOC) and why it is required?

In order to understand Inversion of control let’s talk about a scenario.

Scenario: - We have Customer_Business_Logic class which is using FileLogger class internally for logging Exception inside file.

Understanding problems with this approach–


  • Now let’s say some time later our organization asks us to replace FileLogger with DBLogger
    so that error can be logged inside Database instead of File. In this case we
    will explicitly change the code written inside Business logic layer to achieve
    goal.
    Changes in the business logic may leads to error in business logic.

  • Testing of business logic in isolation is not possible because it is directly referencing FileLogger class.

  • FileLogger need to be available at compile time. Means we can start working on Business logic only after FileLogger is created.

Solution: - Move concrete type creating power to some external source. In short Business class won’t directly create FileLogger object rather someone else will does that and give it to business logic. This is called Inversion of control. Control of object creating is given to someone else.

How to implement Inversion of Control (IOC)?

IOC or Inversion control can be implemented using many ways like Factory Pattern, Dependency injection, Service Locator Pattern etc.

For technical trainings on various topics like WCF (Windows Communication Foundation), MVC (Model View Controller), Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

See the following video on real time scenarios on Dependency Injection and Inversion of Control (DIIOC): -



Click and see here for more learning Inversion of Control(IOC) in .NET
Copy right www.questpond.com
How to write a FizzBuzz program in C#?

Ok first let me explain what is FizzBuzz, Fizz

"Write a c# program that prints numbers from 1 to 100. But for multiples of 3 print “Fizz” instead of the number and for the multiples of 5 print “Buzz”. For numbers which are multiples of both 3 and 5  print “FizzBuzz”.

for (int j = 1; j <= 100; j++)
 {
      string Output = "";

      if (j % 3 == 0) Output = "Fizz";// Divisible by 3 --> Fizz

      if (j % 5 == 0) Output += "Buzz"; // Divisible by 5 --> Buzz

      if (Output == "") Output = j.ToString(); // If none then --> number

      Console.WriteLine(Output); // Finally print the complete output
 }


Click and see here for more programming algorithm in .NET
Copy right www.questpond.com
How can we save all data from dataset?

Dataset has “AcceptChanges” method, which commits all the changes since last time “Acceptchanges” has been executed.

How can we check which rows have changed since dataset was loaded?

For tracking down changes, Dataset has two methods, which comes to rescue “Get Changes “and “Has Changes”.

Get Changes
Returns dataset, which are changed since it, was loaded, or since Accept changes was executed.

Has Changes
Or abandon all changes since the dataset was loaded use “Reject Changes This property indicates that has any changes been made since the dataset was loaded or accept changes method was executed.

How can we add/remove row is in “Data Table” object of “Dataset”?

“Data table” provides “NewRow” method to add new row to “Data Table”. “Data Table” has “DataRowCollection” object that has all rows in a “Data Table” object. Following are the methods provided by “DataRowCollection” object:-

Add
Adds a new row in Data Table

Remove
It removes a “Data Row” object from “Data Table”

Remove At
It removes a “Data Row” object from “Data Table” depending on index position of the “Data Table”.

How can we load multiple tables in a Dataset?

objCommand.CommandText = "Table1"
objDataAdapter.Fill(objDataSet, "Table1")
objCommand.CommandText = "Table2"
objDataAdapter.Fill(objDataSet, "Table2")


Above is a sample code, which shows how to load multiple “Data Table” objects in one “Dataset” object. Sample code shows two tables “Table1” and “Table2” in object ObjDataSet.


lstdata.DataSource = objDataSet.Tables("Table1").DefaultView

In order to refer “Table1” Data Table, use Tables collection of Datasets and the Default view object will give you the necessary output.

How can we add relation between tables in a Dataset?

Dim objRelation As DataRelation
objRelation=New
DataRelation("CustomerAddresses",objDataSet.Tables("Customer").Columns("Custid")
,objDataSet.Tables("Addresses").Columns("Custid_fk"))
objDataSet.Relations.Add(objRelation)


Relations can be added between “Data Table” objects using the “Data Relation” object. Above sample, code is trying to build a relationship between “Customer” and “Addresses” “Data table” using “Customer Addresses” “Data Relation” object.

See the following video on Dataset is a disconnect while datareader is connected: -



Click and see here for more step by step training in ADO.NET
Copy right www.questpond.com
What are Accessors and mutators in C#?

Look at the following example,

public class Customer
{
     publicint Age;
     .
     .
     .
 }


Now in the client code we will do something like this.

Customer c=new Customer();
c.Age=55;


But do you realize one thing even we can do something like this,

c.Age=563;
OR
c.Age=-65;


This is not a valid statement logically but it’s valid syntactically. Our program will just execute perfectly fine.

One of the pillar of Object oriented principle called Encapsulation let us protect our data by creating a wrapper around our data, logic etc. by means of functions, classes etc. In short Encapsulation also let us protects our data.

Usually we will do something like this.

public class Customer
{
       privateint Age; // data is private
       .
       .
       .
       publicintGetAge() // Data is exposed to outside world via public method
       {
               Return Age;
       }
       publicvoid SetAge(intpAge) // Updating of data is only possible via public method
       {
             If(pAge>0 &&pAge<100)
             {
                    Age=pAge;
             }
             else
             {
                   throw new Exception(“Invalid Age”);
             }
        }
 }


Now our data is protected.

Client code will look like this.

Customer c=new Customer();
c.SetAge(55);
Console.WriteLine(c.GetAge());

c.SetAge(965); // It will throw exception


In the above code snippet GetAge function which usually return the data is called Accessor method and SetAge function which updates data is called Mutator method.

Good news?

Now we are in C# things become much simpler. We can make use of properties instead of creating two functions.

public class Customer
{
       privateintpAge; .
       .
       .
       publicintAge()
       {
               get
               {
                    //Accessor
                    returnpAge;
               }
               set
               {
                   //Mutator
                   If(value>0 && value<100)
                   {
                           pAge=value;
                   }
                   else
                   {
                           throw new Exception(“Invalid Age”);
                   }
              }
        }
 }


Client code will look like this,

Customer c=new Customer();
c.Age=55;
Console.WriteLine(c.Age);

c.Age = 965; // It will throw exception


For technical trainings on various topics like WCF(Windows Communication Foundation), MVC, Business Intelligence, Design Patterns, WPF, TFS and Basic fundamentals feel free to contact SukeshMarla@Gmail.com or visit www.sukesh-marla.com

For more stuff like this, click here. Subscribe to article updates or follow at twitter @SukeshMarla

Click and see here for more .NET training.

Copy right www.questpond.com

Buy 600+ Real Time Interview Videos

Call +91-22-66752917 or mail us on questpond@questpond.com for more details on C#, .NET, ASP.NET, SQL Server interview question videos.

SQL Server interview question videos

Latest Month

January 2017
S M T W T F S
1234567
891011121314
15161718192021
22232425262728
293031    

Tags

Powered by LiveJournal.com