Advertisement
English (United Kingdom) transcript
Hello everyone and welcome to Learn MSBI in 4 days with a project end to end.
again i repeat welcome to this series of Learn MSBI in 4 days with a project end to end.
now each day of this training comprises of 8 hrs of teaching because of person works maximum for 8 hrs.
So every day of this training will comprises 8 hrs of teaching so that mean you can also say that this series
also be renamed as it can also be named as Learn MSBI in 32 hrs with a project.
Now the first questions which will come to your mind is is it really possible to learn MSBI in 4 days in 32 hrs
That means i sit on Monday then we have Monday, Tuesday, Wednesday, Thursday and Thursday i am done
with MSBI and Friday i can go and do party the answer is "YES"
Absolutely YES because even thogh MSBI is joined thing still MSBI is simple it is sweet and i can beat you i can
give 100% guarantee nobody gives that in the world i can give you 100% guarantee that if you watch even this
one hour video you should be able to work MSBI project.
Now before we move ahead let us try to understand to this word BI. Let us try to definition of this word
Business intelligence. Business intelligence or BI is nothing but it's process of converting data into information.
So BI again i repeat BI is nothing but converting your data into your information.
Now if you look at the English vocabulary of both of this things that's data information you will understand
that they look very much similar but they are very much different. When we say data data is normally technical format
For example your data can lie in CSV format your data can lie in sql server,
your data can lie in some binary format, your data can lie in excel sheet i don't know what
So data is more of technical format and if you ask simple end user to go and understand this technical format it is very difficult.
So you have to take this data you have to take all this technical format and presented to the user in a very user friendly way so that you can understand
that data and make meaning out of the data. That thing you know which is where we said we have to make meaning out of the data is nothing but information.
So BI is nothing but it's a process of transforming from your technical data to a meaningful information so that the end user can look at the information and make
proper discussion he can do forecasting he can run is business in a more proper manner.
Now this journey from data to information involves very important step called as analysis.
Now this analysis involves lot of complexity. Because if you look at the data site data can be in different formats
you can have data in CSV you can have data in XML you can data in sql server and to go and run the analysis
algorithm individually on each one of this data sources is very difficult tds and it is also illogical actually.
So the best approach would be to take this data and dump it into some central database
This central database is trump as data wire house and later on what we can do is on this central database
we will go and run the analysis algorithm. So first part is you have data then this data is dump into your central
database called as data wire house and on this data wire house the analysis is run and from that analysis information is generated.
Now again the journey from data to data wire house is not so easy it involves lot of processing it involves lot of massing of data.
If you see the data part the data is available different different data sources you have XML you have CSV
you have binary format etc..
And if you want to go and bring all of this data different different data types into the central database.
The first thing what you have is you have to do extraction of data you have to extract data from
this data sources and dump up into this common platform in the common data warehouse.
So the first process involves extraction the second process involves transformation.
Transformation means if you look at the data sources they have different different data type
for example must be one of the data sources terms mail as M and female as F and in some other data sources
and they are saying mail as 1 and female as 0 so when we bring this different different data type into the common data wire house
we need to go and ensure that the data has been transformed into the common format.
So that second step involves transformation and the last step involves loading this transform data into data warehouse.
So in short the journey from data to data wire house involves extraction transformation and loading.
this complete process in MSBI world or i will say rather in BI world trump as ETL.
Now once the data has been analyze from the data warehouse. We would like to store this analysis into some kind of data store
so this data store structure is trump as a cube and from this cube we display the information or we display this
data to the end user as a information. So some kind of reporting software will read this data from this cube and show it to the end user as a information.
Now this is the complete BI cycle write from data to information the first thing what we need to is
do is we need to do ETL after that we need to do analysis and create a cube auto fit i will talk about this
cube structure later on in more details when we look into SSAS and after that we would like to go and
display this analysis to the end user in a simplified graphical manner or must be in a tabular manner
or whatever is it. Now Microsoft MSBI solution remember this BI word is very generic business
intelligence whatever i am discussing now BI so you have both Microsoft as well as non Microsoft solution
for it but when you used the word MSBI that means Microsoft Sql Server BI that means you are specifically
talking about Microsoft technology here. Now MSBI provides end to end support to the full BI cycle
so this BI cycle which i discuss write from data to information MSBI provide full support for it.
When you go and create a MSBI project i will talk about the necessary softwares later on but for now you can see
see on the screen if you want go and create MSBI project. You are given different different templates here
you can see that i have click on this Business Intelligence link and as soon as i click on this
Business Intelligence link there are lot's of templates you can see right which is displayed on the screen.
and each one of this templates serve some section of your BI life cycle. For example the ETL section that is the
journey from data to the data wire house is done by integration services project.
The analysis part in where we run the analysis and we create cube is done by this analysis services template.
The Reporting part where we take that massage data or the analyze data so it to the end user is done by the Report Server Project.
So all of this three templates Integration Services, Report Server and Analysis Services actually help you to
get that full BI automation so this Integration Services also trump as SSIS.
The Analysis Services is trump as SSAS that SS stand for Sql Server so Sql Server analysis services.
Sql Server integration Services and last one is Reporting Services that is SSRS.
So SSIS to do the ETL, SSAS to do the Analysis and SSRS to display that analyze data to the end user.
Now we can go and start execution the project before we executive the project let us try to understand what
Before we move ahead here is a nice cartoon which is created by my friend Mr Shekher. Look at this cartoon
you will understand important s of the each one of this templates and where they are helpful in the BI cycle
Now that we know what is role of SSIS what is role of SSAS what is role of SSRS.
Now we can go and start execution of project befor we executive the project let us try to understand what
project we are going to complete. Now before we start a project we need to have all the necessary tools with us.
So that we don't have any problem while we execution the project. So the first thing is we are going to learn
MSBI using Sql Server 2014 which is the latest edition while i making this video. So go ahead and download the
Sql Server 2014 Enterprise Edition now you can see other i have not said to download some ordinary edition
like express or something i have told you to download enterprise full blown edition
because this tutorials is targeted to make you true MSBI professional.
So i don't want loose any kind of installation or any kind of things so just go ahead and download this.
This ultimate kind of thing that is enterprises edition and remember that the enterprise edition evaluations period
is 180 days which is more then enough for learn MSBI.
First thing is you need to go and download Sql Server enterprises Edition install it and the next thing what we
the next thing what we need is the Sql Server data tools go ahead and download the Sql Server data tools 2013
When you search on google Sql Server data tools 2013 you will come to this link go ahead and install this Sql Server data tools 2013
Now you must be wondering that why have we install two different tools. first thing that is Sql Server 2014
enterprise edition what you have downloaded is nothing but the actual Sql Server RDBMS.
When you install the Sql Server enterprises edition you would get something like this where you can go and
create the database where you can create table etc. So the enterprise edition is the Sql Server enterprise edition
is nothing but the main Sql Server for storing data creating table writing store procedure etc.
And the next thing what you have downloaded the data tools is to create the MSBI project. The hole coding of
extraction, transformation, loading, Analysis, Reporting you are going to go and do by using this 2013 data tools
When you install 2013 data tools what you get is you get such kind of menu if you are not able locate this what
you can do is you can go to Sql Server 2014 and inside that you can find here Sql Server data tools for Visual Studio 2013.
Remember in case you are going to use different for the new version coming it will just the Sql Server data tools
2015 or 2016 so one is versions will change here but is other things will remain the same.
So you have the database and you have the tools here and if you want to go and create project what we will do
is we will do File --> New --> Project and you will click on this Business Intelligent Menu and then you can go ahead and start creating a SSIS project
or SSAS project or SSRS project depending on situation what you are working on.
So let us first discuss about the project what we can go to execute in this MSBI learning. The project is very very
simple customer information system this customer information system get's data from CSV files
This CSV files have to be imported that means it has to be extracted it has to be transformed it has to imported
into the system and then from the imported data we need to go and give some reports to end user
So this customer data which comes into the system is simple CSV file basically it has customer name
remember CSV file means it's comma separated file it has customer name it has customer amount it has
customer code and the sales date in when the customer actually broad the product
Let me go and put some data into this CSV file here and this CSV file you can get from the quetpondvd.com from the site
you don't have type this out so i am going to enter dummy data here let's assume that this customer code
is unique customer code which is coming into the system has to be unique.
So 1001,shiv, 100.23,1/1/2010
write in the same way 10002,Raju,300,2/2/2012
So you can see that i have entered like 8 records over here in the CSV and so let me go and save this file.
Now the extension of the file is not important so it can be txt or it can be any other extension.
But the format of the file has to be CSV format means the internal data representation of the file has to be comma separated
so at this moment i will just give the name here as customer.txt the extension does not matter but the
format of the file that mean over here it should be comma separated.
Basically this customer information system get's this customer data inside the CSV file and this CSV file has
to be imported first thing in the data wire house and then over that data warehouse we need to go and generate the report.
So now the first thing is we need to go and create data warehouse where we would like to go and upload this CSV file
i am going to my Sql Server 2014 go ahead and open Sql Server 2014 click on this Sql server Management Studio
and open Sql Server so let us go to Sql Server let us create a very simple database here called as customer
data warehouse and let us give a name to this database as "CustomerDataWareHouse" now the goal is that we
need to go and upload this CSV file into our customer data warehouse if you go to the database here we have
that customer data warehouse you can see we have the "CustomerDataWareHouse" so now our goal it is to load
loads data into data warehouse is by using
this CSV file into this CustomerDataWareHouse and if you remember the first stage in our BI life cycle which
loads the data into Data Warehouse is by using ETL. In order to do ETL we need to go and create SSIS project.
So the first thing is in this customer data warehouse let us go and create simple table so i am going to create a
a table here and this table will have 4 fields one is customer code "invarchar" Customer name "invarchar"
Customer amount now when we will talk about the financial values like amount or any kind of account
financial values we would like to keep that data types that money and sales date when the customer did the
sales just put it here sales date and date and time save this i will save this as TblCustomer
Let me go and make this as a primary Key so i am going to make this customer code as Primary Key and Save
Now you can see here i am getting this error saying that saving changes is not permitted. This one of those error
which you will keep getting in Sql Server what this means what this error says that if you want to go and say
this changes then the table has to be drooped and re-created which is a danger things he is actually sending
out of warning to you so what you can do here is remember this step you will always get this error again
and again Tools --> options --> and you need to go the designer here and you need to go and say Prevent saving changes that required stable recreation.
But remembers that it's very dangerous things so don't try to do this in production but what it means that it will
actually go and drop your table and recreated create the same with using this new changes what you have done
so i have done with everything our main goal is to load this data from this CSV file into this Sql Server table
that's our main goal so in order to do this remember we said we have to use SSIS to do the ETL we have to use
SSIS to do the analysis and we have to use SSRS to do the reporting at this moment our goal is to extract this
data from this file and push it to this table so let us go and open Sql Server data tools for Visual Studio 2013
you can get this things from here or if you wish you can get this things from here as well so let me go and click
on this so there it is opening so let us click on New Project so you can see that Visual Studio 2013 Sql Server
data tools are open i have clicked on --> New Project and let us go to this --> Business Intelligence menu let us
click on --> Integration Services remember why integration Services because we want to do ETL
you can see this word here ETL now i order to create SSIS project there are two ways one is you can use this
from scratch template other you can use the wizard so let us not use the wizard let us try to go and learn the
hard way so i am going to go browse over here and create this project in a separate folder all together
So let me go and create the folder here saying "CustomerInformationSystem" and select this folder
and let me give a name here saying "SSISCustomer" and say ok once new project is created you would see
something like this on your screen you would see five tab at the top and you would see this SSIS tool box
in case you are not able to see this SSIS tool box what my suggestion is go and click on this SSIS menu and
click on SSIS tool box menu so you will see this five tabs and the tool box for now don't think about this three tabs
you can see there three tabs here Parameters, Event handlers and Package Explore don't think about this
three tabs for now concentrate only one the tool box Control Flow and Data Flow so in other words at this
moment we will just concentrate on Control Flow, Data Flow and SSIS Toolbox the other three tabs i will talk
later as we move ahead again from this two tabs this Control Flow and Data Flow let us concentrate more at
this moment on Data Flow tab as time goes by i will definitely explain all the tab but i don't want to
pressurizes on the first time it self with all these concept let us try to complete that project complete that project
Complete that project means load that file that CSV file into table so let us try learn less concept here
less theory and as time comes will explain you all the tabs don't worry it's promise ok.
but in case you still want have definition around you this Control Flow is nothing but it invokes the Data Flow so
that's not the full definition but in case some other people they would like to still have some kind of a very
mild definition then you can think about this Control Flow invoke the Data Flow so let us go ahead and drag
and drop this Data Flow task component from this SSIS toolbox and let us give name here saying "Load CSV"now
if i double click on this you can see that it has fallen back to the data flow task so you can see here i am
on the Control Flow tab if i double click on it it goes to data flow so in other words you can see that control flow
has data flow code in other words control flow invokes the data flow and second one more important point note
here is you can see that the SSIS toolbox changes when it goes from control flow to data flow you can see here
the SSIS toolbox looks like this when you are in control flow but the time you go to data flow the SSIS toolbox
looks different now this data flow tab is the tab in where you go and write the ETL code in other words your
extraction, transformation and loading code is all return in this data flow tab control flow invokes the data flow
so that's very simple definition for now and later i will go into more in depth definition as the video moves ahead
but for now remember control flow invoke the data flow so when you go and double click on the control flow
it goes to the data flow tab and you can see now when you see this data flow tab when you are on the data flow
tab and if you see the toolbox you can see that it has that absence of ETL you can see here there are
components for source so in other words you would like to first go and extract the data from the source so that is
so that is done by all this source component the next thing is you would like to go and do some transformation
on the data you can see for that we have the transforms and finally you would like go and load that into the destination.
So you can see your ETL thing is seen in this data flow tab here remember this source is for extraction this
transform is for transformation and destination for loading all your ETL is done in this data flow tab let us go
step by step first thing is we need to do the extraction in other words we need to go and defined the source from
where the data will coming right now remember that our source at this moment nothing but it's simple flat CSV file
so you can see that when i clicked on this other sources you can see there lot's up different types of sources here
but for now what we need is we need a flat file source so i am going to drag and drop this flat file sources over
here now every component yo
…
again i repeat welcome to this series of Learn MSBI in 4 days with a project end to end.
now each day of this training comprises of 8 hrs of teaching because of person works maximum for 8 hrs.
So every day of this training will comprises 8 hrs of teaching so that mean you can also say that this series
also be renamed as it can also be named as Learn MSBI in 32 hrs with a project.
Now the first questions which will come to your mind is is it really possible to learn MSBI in 4 days in 32 hrs
That means i sit on Monday then we have Monday, Tuesday, Wednesday, Thursday and Thursday i am done
with MSBI and Friday i can go and do party the answer is "YES"
Absolutely YES because even thogh MSBI is joined thing still MSBI is simple it is sweet and i can beat you i can
give 100% guarantee nobody gives that in the world i can give you 100% guarantee that if you watch even this
one hour video you should be able to work MSBI project.
Now before we move ahead let us try to understand to this word BI. Let us try to definition of this word
Business intelligence. Business intelligence or BI is nothing but it's process of converting data into information.
So BI again i repeat BI is nothing but converting your data into your information.
Now if you look at the English vocabulary of both of this things that's data information you will understand
that they look very much similar but they are very much different. When we say data data is normally technical format
For example your data can lie in CSV format your data can lie in sql server,
your data can lie in some binary format, your data can lie in excel sheet i don't know what
So data is more of technical format and if you ask simple end user to go and understand this technical format it is very difficult.
So you have to take this data you have to take all this technical format and presented to the user in a very user friendly way so that you can understand
that data and make meaning out of the data. That thing you know which is where we said we have to make meaning out of the data is nothing but information.
So BI is nothing but it's a process of transforming from your technical data to a meaningful information so that the end user can look at the information and make
proper discussion he can do forecasting he can run is business in a more proper manner.
Now this journey from data to information involves very important step called as analysis.
Now this analysis involves lot of complexity. Because if you look at the data site data can be in different formats
you can have data in CSV you can have data in XML you can data in sql server and to go and run the analysis
algorithm individually on each one of this data sources is very difficult tds and it is also illogical actually.
So the best approach would be to take this data and dump it into some central database
This central database is trump as data wire house and later on what we can do is on this central database
we will go and run the analysis algorithm. So first part is you have data then this data is dump into your central
database called as data wire house and on this data wire house the analysis is run and from that analysis information is generated.
Now again the journey from data to data wire house is not so easy it involves lot of processing it involves lot of massing of data.
If you see the data part the data is available different different data sources you have XML you have CSV
you have binary format etc..
And if you want to go and bring all of this data different different data types into the central database.
The first thing what you have is you have to do extraction of data you have to extract data from
this data sources and dump up into this common platform in the common data warehouse.
So the first process involves extraction the second process involves transformation.
Transformation means if you look at the data sources they have different different data type
for example must be one of the data sources terms mail as M and female as F and in some other data sources
and they are saying mail as 1 and female as 0 so when we bring this different different data type into the common data wire house
we need to go and ensure that the data has been transformed into the common format.
So that second step involves transformation and the last step involves loading this transform data into data warehouse.
So in short the journey from data to data wire house involves extraction transformation and loading.
this complete process in MSBI world or i will say rather in BI world trump as ETL.
Now once the data has been analyze from the data warehouse. We would like to store this analysis into some kind of data store
so this data store structure is trump as a cube and from this cube we display the information or we display this
data to the end user as a information. So some kind of reporting software will read this data from this cube and show it to the end user as a information.
Now this is the complete BI cycle write from data to information the first thing what we need to is
do is we need to do ETL after that we need to do analysis and create a cube auto fit i will talk about this
cube structure later on in more details when we look into SSAS and after that we would like to go and
display this analysis to the end user in a simplified graphical manner or must be in a tabular manner
or whatever is it. Now Microsoft MSBI solution remember this BI word is very generic business
intelligence whatever i am discussing now BI so you have both Microsoft as well as non Microsoft solution
for it but when you used the word MSBI that means Microsoft Sql Server BI that means you are specifically
talking about Microsoft technology here. Now MSBI provides end to end support to the full BI cycle
so this BI cycle which i discuss write from data to information MSBI provide full support for it.
When you go and create a MSBI project i will talk about the necessary softwares later on but for now you can see
see on the screen if you want go and create MSBI project. You are given different different templates here
you can see that i have click on this Business Intelligence link and as soon as i click on this
Business Intelligence link there are lot's of templates you can see right which is displayed on the screen.
and each one of this templates serve some section of your BI life cycle. For example the ETL section that is the
journey from data to the data wire house is done by integration services project.
The analysis part in where we run the analysis and we create cube is done by this analysis services template.
The Reporting part where we take that massage data or the analyze data so it to the end user is done by the Report Server Project.
So all of this three templates Integration Services, Report Server and Analysis Services actually help you to
get that full BI automation so this Integration Services also trump as SSIS.
The Analysis Services is trump as SSAS that SS stand for Sql Server so Sql Server analysis services.
Sql Server integration Services and last one is Reporting Services that is SSRS.
So SSIS to do the ETL, SSAS to do the Analysis and SSRS to display that analyze data to the end user.
Now we can go and start execution the project before we executive the project let us try to understand what
Before we move ahead here is a nice cartoon which is created by my friend Mr Shekher. Look at this cartoon
you will understand important s of the each one of this templates and where they are helpful in the BI cycle
Now that we know what is role of SSIS what is role of SSAS what is role of SSRS.
Now we can go and start execution of project befor we executive the project let us try to understand what
project we are going to complete. Now before we start a project we need to have all the necessary tools with us.
So that we don't have any problem while we execution the project. So the first thing is we are going to learn
MSBI using Sql Server 2014 which is the latest edition while i making this video. So go ahead and download the
Sql Server 2014 Enterprise Edition now you can see other i have not said to download some ordinary edition
like express or something i have told you to download enterprise full blown edition
because this tutorials is targeted to make you true MSBI professional.
So i don't want loose any kind of installation or any kind of things so just go ahead and download this.
This ultimate kind of thing that is enterprises edition and remember that the enterprise edition evaluations period
is 180 days which is more then enough for learn MSBI.
First thing is you need to go and download Sql Server enterprises Edition install it and the next thing what we
the next thing what we need is the Sql Server data tools go ahead and download the Sql Server data tools 2013
When you search on google Sql Server data tools 2013 you will come to this link go ahead and install this Sql Server data tools 2013
Now you must be wondering that why have we install two different tools. first thing that is Sql Server 2014
enterprise edition what you have downloaded is nothing but the actual Sql Server RDBMS.
When you install the Sql Server enterprises edition you would get something like this where you can go and
create the database where you can create table etc. So the enterprise edition is the Sql Server enterprise edition
is nothing but the main Sql Server for storing data creating table writing store procedure etc.
And the next thing what you have downloaded the data tools is to create the MSBI project. The hole coding of
extraction, transformation, loading, Analysis, Reporting you are going to go and do by using this 2013 data tools
When you install 2013 data tools what you get is you get such kind of menu if you are not able locate this what
you can do is you can go to Sql Server 2014 and inside that you can find here Sql Server data tools for Visual Studio 2013.
Remember in case you are going to use different for the new version coming it will just the Sql Server data tools
2015 or 2016 so one is versions will change here but is other things will remain the same.
So you have the database and you have the tools here and if you want to go and create project what we will do
is we will do File --> New --> Project and you will click on this Business Intelligent Menu and then you can go ahead and start creating a SSIS project
or SSAS project or SSRS project depending on situation what you are working on.
So let us first discuss about the project what we can go to execute in this MSBI learning. The project is very very
simple customer information system this customer information system get's data from CSV files
This CSV files have to be imported that means it has to be extracted it has to be transformed it has to imported
into the system and then from the imported data we need to go and give some reports to end user
So this customer data which comes into the system is simple CSV file basically it has customer name
remember CSV file means it's comma separated file it has customer name it has customer amount it has
customer code and the sales date in when the customer actually broad the product
Let me go and put some data into this CSV file here and this CSV file you can get from the quetpondvd.com from the site
you don't have type this out so i am going to enter dummy data here let's assume that this customer code
is unique customer code which is coming into the system has to be unique.
So 1001,shiv, 100.23,1/1/2010
write in the same way 10002,Raju,300,2/2/2012
So you can see that i have entered like 8 records over here in the CSV and so let me go and save this file.
Now the extension of the file is not important so it can be txt or it can be any other extension.
But the format of the file has to be CSV format means the internal data representation of the file has to be comma separated
so at this moment i will just give the name here as customer.txt the extension does not matter but the
format of the file that mean over here it should be comma separated.
Basically this customer information system get's this customer data inside the CSV file and this CSV file has
to be imported first thing in the data wire house and then over that data warehouse we need to go and generate the report.
So now the first thing is we need to go and create data warehouse where we would like to go and upload this CSV file
i am going to my Sql Server 2014 go ahead and open Sql Server 2014 click on this Sql server Management Studio
and open Sql Server so let us go to Sql Server let us create a very simple database here called as customer
data warehouse and let us give a name to this database as "CustomerDataWareHouse" now the goal is that we
need to go and upload this CSV file into our customer data warehouse if you go to the database here we have
that customer data warehouse you can see we have the "CustomerDataWareHouse" so now our goal it is to load
loads data into data warehouse is by using
this CSV file into this CustomerDataWareHouse and if you remember the first stage in our BI life cycle which
loads the data into Data Warehouse is by using ETL. In order to do ETL we need to go and create SSIS project.
So the first thing is in this customer data warehouse let us go and create simple table so i am going to create a
a table here and this table will have 4 fields one is customer code "invarchar" Customer name "invarchar"
Customer amount now when we will talk about the financial values like amount or any kind of account
financial values we would like to keep that data types that money and sales date when the customer did the
sales just put it here sales date and date and time save this i will save this as TblCustomer
Let me go and make this as a primary Key so i am going to make this customer code as Primary Key and Save
Now you can see here i am getting this error saying that saving changes is not permitted. This one of those error
which you will keep getting in Sql Server what this means what this error says that if you want to go and say
this changes then the table has to be drooped and re-created which is a danger things he is actually sending
out of warning to you so what you can do here is remember this step you will always get this error again
and again Tools --> options --> and you need to go the designer here and you need to go and say Prevent saving changes that required stable recreation.
But remembers that it's very dangerous things so don't try to do this in production but what it means that it will
actually go and drop your table and recreated create the same with using this new changes what you have done
so i have done with everything our main goal is to load this data from this CSV file into this Sql Server table
that's our main goal so in order to do this remember we said we have to use SSIS to do the ETL we have to use
SSIS to do the analysis and we have to use SSRS to do the reporting at this moment our goal is to extract this
data from this file and push it to this table so let us go and open Sql Server data tools for Visual Studio 2013
you can get this things from here or if you wish you can get this things from here as well so let me go and click
on this so there it is opening so let us click on New Project so you can see that Visual Studio 2013 Sql Server
data tools are open i have clicked on --> New Project and let us go to this --> Business Intelligence menu let us
click on --> Integration Services remember why integration Services because we want to do ETL
you can see this word here ETL now i order to create SSIS project there are two ways one is you can use this
from scratch template other you can use the wizard so let us not use the wizard let us try to go and learn the
hard way so i am going to go browse over here and create this project in a separate folder all together
So let me go and create the folder here saying "CustomerInformationSystem" and select this folder
and let me give a name here saying "SSISCustomer" and say ok once new project is created you would see
something like this on your screen you would see five tab at the top and you would see this SSIS tool box
in case you are not able to see this SSIS tool box what my suggestion is go and click on this SSIS menu and
click on SSIS tool box menu so you will see this five tabs and the tool box for now don't think about this three tabs
you can see there three tabs here Parameters, Event handlers and Package Explore don't think about this
three tabs for now concentrate only one the tool box Control Flow and Data Flow so in other words at this
moment we will just concentrate on Control Flow, Data Flow and SSIS Toolbox the other three tabs i will talk
later as we move ahead again from this two tabs this Control Flow and Data Flow let us concentrate more at
this moment on Data Flow tab as time goes by i will definitely explain all the tab but i don't want to
pressurizes on the first time it self with all these concept let us try to complete that project complete that project
Complete that project means load that file that CSV file into table so let us try learn less concept here
less theory and as time comes will explain you all the tabs don't worry it's promise ok.
but in case you still want have definition around you this Control Flow is nothing but it invokes the Data Flow so
that's not the full definition but in case some other people they would like to still have some kind of a very
mild definition then you can think about this Control Flow invoke the Data Flow so let us go ahead and drag
and drop this Data Flow task component from this SSIS toolbox and let us give name here saying "Load CSV"now
if i double click on this you can see that it has fallen back to the data flow task so you can see here i am
on the Control Flow tab if i double click on it it goes to data flow so in other words you can see that control flow
has data flow code in other words control flow invokes the data flow and second one more important point note
here is you can see that the SSIS toolbox changes when it goes from control flow to data flow you can see here
the SSIS toolbox looks like this when you are in control flow but the time you go to data flow the SSIS toolbox
looks different now this data flow tab is the tab in where you go and write the ETL code in other words your
extraction, transformation and loading code is all return in this data flow tab control flow invokes the data flow
so that's very simple definition for now and later i will go into more in depth definition as the video moves ahead
but for now remember control flow invoke the data flow so when you go and double click on the control flow
it goes to the data flow tab and you can see now when you see this data flow tab when you are on the data flow
tab and if you see the toolbox you can see that it has that absence of ETL you can see here there are
components for source so in other words you would like to first go and extract the data from the source so that is
so that is done by all this source component the next thing is you would like to go and do some transformation
on the data you can see for that we have the transforms and finally you would like go and load that into the destination.
So you can see your ETL thing is seen in this data flow tab here remember this source is for extraction this
transform is for transformation and destination for loading all your ETL is done in this data flow tab let us go
step by step first thing is we need to do the extraction in other words we need to go and defined the source from
where the data will coming right now remember that our source at this moment nothing but it's simple flat CSV file
so you can see that when i clicked on this other sources you can see there lot's up different types of sources here
but for now what we need is we need a flat file source so i am going to drag and drop this flat file sources over
here now every component yo
…