Problem is that if I go and run the project there is exception.
Under “Main.dtsx” design, there is error in the Country master and go to the “country.dtsx” design to check the Error. You can see RED cross mark on top right of the component. In the RED sign some error message here. It sees the following screen shot
Message says that, I can’t insert the duplicate key in the “Country” table. Because the “Country Table” as well as ProductTable, SalesPersonTable and StateTable all have a primary key. Re-run the package you try the re-load the data inside the table. While using SQL tables in MSBI duplicate data can occur for analysis or business sake so primary key can be disabled.
We need to go to the SSIS Toolbox➝ common➝ Data Conversion. Drag and drop the “Data Conversion” into the “Data flow” and Edit the “Data Conversion” as following screen shot…
We have a component in SSIS called as “Slowly Changing Dimension”. Slowly Changing Dimension is Available in the data flow task. Go to the SSIS Toolbox➝ Common➝ Slowly Changing Dimension.
Double Click on SCD will initiate Slowly Changing Dimension Wizard
Slowly Changing Dimension will right, all the logic as well as go and connect the SQL Server.
Drag and drop the Slowly Changing Dimension in Data flow and right click in the Slowly Changing Dimension say “Edit”. Click on the edit its start wizard and wizard helps us to configure Slowly Changing Dimension component. It sees following Screen shot…
When we click on the “New” then pop up new windows to configure the “Connection Manager”. It sees the following screen shot…
Select the “Input Columns” which is defined into the “Data Conversion”. Defined the “Dimension Columns” and “Key Type” is “Business key”. Business key is the primary kind of the think and then next. See the following screen shot..
Now, the Slowly Changing Dimension Wizard gives you three kinds of option.
Fixed Attribute ➝ Fixed attribute changing indicate the column value must not change. For which value is not change.
Changing Attribute ➝ Changing attribute changes overwrite records. This kind of change is equivalent to a Type 1 change.
Historical attribute ➝ Historical attribute changes create new records instead of updating existing ones. It is also called Type 2 change. In Type 2 change is maintain.
You can check or uncheck as per your requirement, I am un-checking these two.
Also un-check the “Enable Inferred Members Support”.
Click to “Next” and finish.
With the removal of primary now working with SCD component will be absolutely fine.
Below is “Learn MSBI in 32 hrs” series project starter video to learn more topic: -
SQL Server Agent helps by ensuring to run scheduled job on specified time by the user and internally it uses SQL Server to store job information. Job scheduling involves task running daily, weekly or monthly on a fixed or specific time. It is the feature of SQL Server and involves we have series of step to activate it if you find below error message.
SQL Server Agent Error
SQL Server Agent was stopped. It shown the message “SQL Server Agent (Agent XPs disabled)” are not started. I tried to start the service from SQL Server Management Studio, but this did not work.
What is the issue and how do we fix this?
I started all of the SQL Server services from the “services.msc” console and everything started successfully.
Right Click on the “SQL Server Agent (RAHUL)” and start the “ as shown in the following screen shot...
When you start the SQL Server Agent showed, it was not running with “SQL Server Agent (Agent XPs disabled)”. It shown in the following screen shot.
SQL Server Agent was not running in SSMS, we were not able to access any of the contents like jobs, error logs, etc...
After that click on the “SQL Server Agent” and then Start as shown in the following screen shot...
When you start the “SQL Server Agent”, it’s show all the folder, on the toolbar indicates that SQL server Agent started successfully as show the following screen shot…
Once SQL Server Agent is started then you can run all the task which are scheduled.
I am a new learner to SQL excuse me in case if you find difficulty in understanding it. This small article is inspired from below video so do watch full video: -
For loop container loops a constant value.For Loop is a NON-ETL activities and that code has to be done in control flow. For loop container loop till a fixed count.
For Loop container groups set of tasks together and allows you to looping through the tasks in your package which is similar like for loop in any programming language. In case until a fixed count than you will use the “For Loop container”.
Drag and drop For Loop container from tool box on to Control flow design editor as shown in
Double click on For Loop container task which takes us to configuration editor as shown in
Configuration editor has only two options For Loop and Expressions.
InitExpression: This will initialize the loop with specified values
EvalExpression: specifies an expression to stop the loop when the expression evaluates to false. This expression must evaluate to a Boolean.
AssignExpression: specifies an expression that changes a condition in the same way each time the loop iterates.Which is optional one.
Drag and drop “For loop” container and configure as shown below
When we use For Loop Container task.
For Loop Container Task: When we know the exact how many times we have repeat given data flow for execution.
Difference between For Loop and Foreach Loop Container?
A For Loop container loops till a constant value. So for example if you want to loop until a value is 12 or less than 12 so for upto fixed count you have to use For Loop container.
Whereas Foreach Loop container loops through collection so this collection can files in a folder or collection can be in record set.
Foreach loop container actually loop through a collection. Now this collection row in a record set files in a folder.
For Each Loop Container Task: When there is dependency of number of time task should execute on data such as files, variables we will use this.The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.
Create a source folder (I have created Folder Named Customer) containing some text files and create Destination Folder where you want to copy these files.
First, we need to create an ETL process for a single file. So, create an ETL process for single file.
After that ETL Process is completed now to make it easy we will save both File to a folder Called “Bulkfile”.
In this step on “Control Flow” tab we will create a variable why because if you see on “Data Flow” tab pue source in Flat File Connection we have physical file name is “CostomerSales.txt” which is one single file name. Weworking with multiple files. We will create a variablein “CONTROL FLOW” tab and pass that full file name through this variable name to “DATA FLOW” using “Foreach loop”.
It is temporary stores. We go and store some value.
Create a variable.
In order to create a variable go to Control Flow Right Click Add Variable as Shown in below screen shot.
Add variable and give the name and then we are passing file name so our variable data type should be “String” as shown in below screen shot..
We will configure FOREACH LOOP container. Select container and right click -->edit.
From left-hand side menu select collection and from enumerator select "FOREACH FILE Enumerator".
Go to below in the folder section folder path "rahulxadfg" which we have created earlier and below that give extension as ".txt" since our data is in text format as shown in below screen shot…
Select Variable mapping from left-hand side and choose variable name which we have created earlier i.e. "VarFullFilePath" as shown in below screen shot…
Everytime "FOREACH FILE Enumerator" loops and get a file it will assign that file path to this variable "VarFullFilePath".
After that we configure the Data Flowtable at source. Where file name is needed for ETL process.
So go to Data Flow tab and Connection Manage right clicks on CSV Connection Manager Properties.
In the properties window if you see current "ConnectionString" it consist of single file path that we want to make it dynamic by assigning "VarFullFilePath".
The same property window there "Expression" menu -> configure -> click. In the property select "ConnectionString" and click on expression select variable "VarFullFilePath" and drag it to expression and click on OK as shown in below image.
Run this project to see the outputbelow is the screen shot on Control Flow.
And here is the screenshot from Data Flow.
Hope that practical of looping in SSIS is understood.
Do not forget to see following one such video from project series of MSBI(Microsoft Business Intelligence) for newbie’s: -
Variable is more internal to SSIS. Variable is used to pass the variable from control flowto data flow.
They help to pass data from control flow to data flow.
They are passed from outside and help during deployment in production server.
They are just written with simple name as given.
They are denoted with $ symbol by the name.
They are more internal to SSIS application to pass data.
They are more internal to SSIS application to pass data.
Using Parameters with SSIS deployment
Parameter is more external these are passing from outside.
Parameters allow you to assign values to properties within packages at the time of package execution.
Now we will create a parameter for destination path. So we will go back to project and add parameter as shown in below screen shot…
Click on parameter tab and add new parameter for destination path as shown in below image.
If you see we have created a string type parameter called "BulkFilePath" to set destination value which we have already set in the value.
We will configure this parameter to destination file connection in the connection manager. Right click on that “CVS Connection Manager” and go to property à choose expression.
We need to specify a property as ConnectionString because we want to parameterize our connection and then choose expression as shown in following screen shot….
Expression:- it is help set parameter and variable name values to the properties. To click on the Directory and set a parameter ($Package for parameter which is represent by $) and “VarFullFilePath” is variable.
helps you set variable of properties of SSIS component from variable and parameter”.
Right click on that “ Foreach Loop Container” and go to “Edit” as shown in following screen shot….
The parameter value will be set during deployment in production.
Hope that variables and parameters used within SSIS is understood.
Below see one video from project series of MSBI (Microsoft Business Intelligence)-
In this article we will cover SQL Server Analysis Service step by step.
When learning any technology theory part is also equivalently important along with doing practical. Students coming to learn MSBI in our Mumbaicenter get perfect harmony of both theory and practical’s.
What is SSAS?
SSAS is known as SQL Server Analysis Service.
In the BI cycle SSAS is an important part.
Why do we need SSAS?
SSAS is used to do the analysis of data. In the regular case SSRS & SSIS are more used than SSAS.
Analysis of data means more like calculations which include sum, count or complicated formula. Analysis helps in forecasting the data to the user.
Generally for billions and billions of data we cannot write the query and analyze the data and for the large number of records we need SSAS.
SSAS does pre calculation and stores data in SSAS database
What is a SSAS Cube?
While creating the SSAS project there are many components which are required. From which we have to design a Cube which will handle all our data and do the analysis.
In the SSAS Cube all the Facts and Dimensions are placed such that the Fact should be in the center of the Cube and the Dimensions should be the sides of the Cube.
What is the SSAS project Structure?
We have to start configuring the SSAS project from Top to Bottom in sequence.
What is the Query Language used while storing the data in SSAS database?
As SQL has SQL query for storing the data in database, SSAS also has a query language known as MDX.
What is Latency in SSAS?
Sometimes it happens that the data in SSAS database and SQL database are not in sync. Due to this Latency is created.
To avoid this whenever a new record is added to the SQL database we have to process our Cube in SSAS project.
Therefore we have to inform the customer that the data which is available in SSAS is not real time and there is some latency between the SSAS database & SQL database.
Many more such theory part is covered in the training with that learning becomes stronger and topics get automatically prepared. Check out for more how you can be benefited from MSBI classroom training MSBI classroom training.
Yield is one of the most useful but under-used keywords in C#. The reason is that most of us don't even know about this keyword and the purpose it can serve for us.It is a very simple and easy way to create an enumerable for object. It is helps us to do custom stateful iteration in .net collection. Indicate that the method, operator. it appears is an iterator. Using yield to define an iteration removes the need for an explicit extra class.
This keyword provides is that when iterating a list, we can read an element of the loop, return to the calling code and go back to the loop again at the same point, where it left the loop and continue processing the records in the loop. So, this will be the basic idea behind the example that we will be using.
What is use of yield keyword.
There are two basic use to yield keyword.
It helps to provide custom iteration without creating temp.
It helps to do state-full iteration.
Customize iteration without creating a temp.
We have a simple list called as “List” which are collection 6 number. My main method is caller. That is main method calls the list and show the items. when we use the yield keyword "control(main) moves form the caller to the source and he back to the controller."Below is a simple code how we have used yield.
staticList<int> List = newList<int>(); //create a list
staticvoid Main(string args) // Caller
FillValues(); // Fills the list with 6 values
foreach (int k in List) // Browses through the list
Let’s say the caller only want values greater than 4 from the collection. We will create a function. This function is temporary collection.
List<int> temp = newList<int>();
foreach (int kin List)
if (k> 4)
Yield keyword will return back the control to the caller, the caller work and re-enter the function.
yield keyword moves control of the program to and between caller and the collection.
foreach (int k in List)
if (k >4) //comper
Now next we will see it practically following below steps to understand how to work using “yield keyword”.
Step 1. when caller calls loop function for iteration to find number which are greater than 4.
Step 2. When caller call to function and loop will be start 1 to 2, 2 to 3, 3 to 4 until and unless encounter values greater than 4 ,5. When condition will be true than “yield” keyword sent this data back to caller.
Step 3. Now caller function show all values where condition is satisfied it means when first condition is true than again enter in the loop, it will not start from start,it just go to the next item that is 4.Iteration will be continuing until and unless condition is false.
Stateful means it store the previous Data.
Now understand by simple example.
If we want to display running total of the collection. We will browse from 1 to 6 and it keep adding the total variable. We start from 1 and running total is 1. After that we move to 2 and running total is 1 plus current variable 2 that is 3.We move to 3 and running total is 2 plus the current variable 3 that is 5 and so on…
Runningtotal uses the yield keyword. Runningtotal variable will hold the value every time and caller re-enter the function.
staticList<int> List = newList<int>();
staticvoid Main(string args) // Caller
FillValues(); // Fills the list with 6 values
foreach (int k inrunningtotal()) // Browses through the list
List<int> temp = newList<int>();
foreach (int k in List)
if (k > 4)
// the caller code and output.
staticIEnumerable<int>runningtotal() // IEnurable is state less.
intrunningtotal = 0;
foreach (int k in List)
runningtotal += k;
If you are new to C# language and .NET platform below is a worth watching video covering learning fundamental with practical project:
HTML 5 is latest and one of the important aspects of programming and your interview cannot be completed without HTML 5 interview questions. Following we have answered to the interview questions asked during recent .NET interviews.
Can you tell something about Application Cache in HTML5?
In HTML5 using Application Cache we can make our web application available to client even when there is no internet connection.
What will be the advantage?
There are many. Starting with great performance, highly availability (application will be available for browser even if there is no internet connection) and reduced server load.
How this Application cache is different from normal browser cache?
In traditional browser caching, browser has the ability to cache one or more pages and resources.
There are two drawbacks: -
It may possible that browser just remove one of the file from cache in some point of time to make rooms for other stuffs. In real life scenario our single page (let’s say customer.aspx) is made up of more than one resources like one or more CSS, one more js file etc. Removal of one of them may make the complete cached resource unworthy.
There will not be complete control over caching. For example you cannot specify what all CSS or js files will be cached with your actual resource.
What should be done in order to achieve this?
1) First step will be creating manifest file like shown below
CACHE MANIFEST # Some Comment File1.Extension SomeFolder/File2.Extension
CACHE MANIFEST # Ver 1 Customer.js Customer.css CustpmerOrderDetail.aspx
2) Second Step set manifest attribute of html tag( in the main resource you want to cache) to above file
Reflection is needed when you want to determine/inspect contents of an assembly.
For example look at your visual studio editor intellisense, when you type “.” (dot) before any object , it gives you all members of the object.
This is possible because of reflection.
Reflection also goes one step further; it can also invoke a member which is inspected. For instance if the reflection detects that there is a method called as “GetChanges” in an object.
We can get a reference to that method instance and invoke the same on runtime.
In simple words reflection passes through two steps “Inspect” and “Invoke” (optional).
“Invoke” process is optional.
What is Dynamic?
Programming languages can be divided in to two categories strongly typed and dynamically typed. Strongly typed languages are those where the checks happen during compile time while dynamic languages are those where type checks are bypassed during compile time. In dynamic language object types are known only during runtime and type checks are activated only at run time.
So we would like to take advantage of both the world. Because many times we do not know object type until the code is executed. In other words we are looking at something like dynamically statically typed kind of environment. That’s what dynamic keyword helps us with. If you create a variable using the “Dynamic” keyword and if you try to see members of that object you will get a message as shown below “will be resolved at runtime”.
Now try the below code out. In the below code I have created a dynamic variable which is initialized with a string data. And in the second line I am trying to have fun by trying to execute a numeric incremental operation. So what will happen now?.... think.
dynamic x = "c#";
Now this code will compile fine without any complains. But during runtime it will throw an exception complaining that the mathematical operations cannot be executed on the variable as it’s a string type. In other words during runtime the dynamic object gets transformed from general data type to specific data type (ex: - string for the below code).
Garbage collector is a feature of CLR which cleans unused managed (it does not clean unmanaged objects) objects and reclaims memory. It’s a back ground thread which runs continuously and at specific intervals it checks if there are any unused objects whose memory can be claimed.
In inserted sorted algorithm we compare the current element value with the previous element value. If the current value is smaller than the previous value we swap. This continues until the complete list is sorted.
Let us try to understand the same by using the below diagram. In the below figure you can see we have an unsorted list with values 6, 1, 3, 2.
Step 1: - So we start with the first element i.e. “6”.There is no element before 6 , so we leave it and we start from 1. We compare “6” and “1”. “1” is smaller than “6”, so we swap.
Step 2: - Now we move to the next element “3”. Is “3” smaller than “6”, yes. So we again swap.
Step 3: - Now we compare “6” with the next element “2”. “2” is smaller so we swap again. There are no more further elements which can be compared with “6”. So the “6” value iteration stops here.
Step 4: - Now we take the next element “1”. There is no element before “1” so we move ahead. We move to the next element “3”. Is “3” smaller than “1” , no , so things are left where they are. So the iteration for “1” is done. We then move to the next element “3”. We compare “3” with “2”. “2” is smaller than “3” so we swap. Now “3” is compared with the next element “6”. “3” is smaller than “6” so the elements are left where they are.
Step 5: - Now we take the last element “2”. “2” element is larger is than “1”, so “2” and “1” stay where they are. The complete collection is now sorted.