So let us move ahead with lap six. And what I've done is I have completed the half thing which were left in lap five. So in lap five, you know, we just loaded the country master dimension, right? So you can see what I've done is, I've completed for the states I've completed for the salesperson I have completed for the product master. So basically, if you see my solution explorer here, you can see that we have all the respective DTS X Files. So here is a product here is a salesperson here is a stage master and so basically, I have completed you know, that half left thing from left five.

So at this moment, our dimensions are loading. So in other words, if I go and run this package, remember I have disabled the fact customer at this moment. So if I run this, so there we go, country is loading, states loading, salesperson loading and product is loading, loading. It still running, I think. Okay, so basically, if I go and do a select here, so you can see that all my masters are loading. But now there is a serious problem with this project.

The problem is if I go and run this project at this moment, so let's say I go and run this project at this moment, I land up with an exception. So you can see here in the main DTS x design, there is an error in the country master loading. So, if I go to the country master package, and if I go to this red sign here, you can see there is some error message here. So, let me copy this message and let me paste it in Notepad. And let us read this message. So the message clearly says that, you know, I cannot insert a Duplicate key in the dim country table.

So why? Because the dimension country as well as the dimension, state and dimension product all have a primary key. So basically, when I read on the package, you're trying to load the same data inside the table. And that's why it gives this exception you're saying I cannot insert a duplicate key in the table. Now, this situation, we want to handle it definitely. or answer we want to handle it gracefully, right.

So in other words, I want to do something like this. I would like to say that if the country data is existing in the database, then don't do anything. In the country data is not existing, then you do a insert. But now if the country data is existing, and also if it has changed, then do an update. Again, I repeat I want to handle the situation as follows. If the data exists in the database, then try to see that if there are changes, then do an update.

If the data does not exist, then just go and insert it. So, now, in order to achieve this we have a very very nice component in SSIS called as slowly changing dimensions. So, the slowly changing dimension component is available in the data flow task. So, in other words, you can see that I am on the country data flow. So, if you go to the toolbox here, you will find this component here so you can see your slowly changing dimension. Now, the first thing what will come to your mind is, why is the name slowly changing dimensions?

Why isn't it replication or data synchronization? Why is the name so we're slowly changing dimension Now, first thing from these three words the dimension word is very clear it is very much related, because at this moment we are loading the Dimension Data. So that's why this word dimension is there. Now, look at the Dimension Data look at the nature of Dimension Data. They are mostly master data master table. In other words, the data of dimension changes very slowly.

If you look at master data master data, they are not updated now and then probably they will get updated must be in a week or must be in a month. For example, look at the country master table. The country master table we've changed slowly, isn't it? That's why the name is slowly changing. What it also means is that this component you shouldn't use with tables which are having large number of records and they are changing frequently. If you do that, then this component becomes very slow.

So this component is made typically for updating your Dimension Data, updating your master data Updating your data these changes very very slowly. So, let us go and start using the slowly changing dimension. So, the first thing is let us go and delete this. So, once the data has been read from the text file you would like to go and feed this data to the slowly changing dimension component right so, I'm going to go and drag and drop the slowly changing dimension component here and the data output of this country dot txt file will go to the slowly changing dimension. Now, we do not need this area dotnet destination anymore because the slowly changing dimension will write all the logic as well as it will go and connect to SQL Servers right. So let us go and right click on the slowly changing dimension and let us say Edit.

Now the time you say Edit, it actually starts up a wizard. So the time you say Edit, you will see that there is a wizard started there. It is And this resolve will help us to configure our slowly changing dimension components. So, let us do a next. And the first thing is it says that, can you give me the table on which you want our slowly changing dimension to work on? So at this moment, the database is this, copy this and we say Ctrl V, and the database is customer data warehouse.

And it's okay. So, okay. So I want this text file to be loaded into the dem country table. Right. So there it is. But the first thing you know what it asks is, you know, it asks for, it asks you to map the columns.

So basically, this is you know, so these are my input. Columns country ID, I'm trying to map with the country ID of the table country name with the country name of the table. So at the left hand side, I have my text file or my CSV file columns. And on the right hand side, I have my table columns. I can see I'm getting this typical error here, which says that the left side country ID is actually a string, while the right side country ID which is coming from the database, right is actually a in so this mapping is difficult, right. So in other words, what we have to do here, we have to go and use the conversion component, remember, so let us first thing go and try to convert this data.

So I'm going to go and delete this and I will say SSIS Toolbox and I will use the data conversion component. I will give it to the data conversion. Then I will say Edit and I will say I want this guy Entry ID will be transformed to a four byte signed integer. So four byte sign integer, okay, we'll talk about this mapping in a way I have mapped to four byte. But at this moment, what I'm doing is I'm creating a new column, right? And this new column will be named by country and this column I will use to create my business key.

Okay? So I'm going to go and now give this output to the slowly changing dimension. Again, let us start edit and hope that this time, we don't have to, will not be shouldn't be getting that error. So dim country and now I will not map the country ID I will map in country ID. Now one of the things a city needs is it needs a column which is unique Because at the end of the day, when you say that you want to replicate between two sources, one thing should be very common between them is some kind of a unique key, some kind of a unique key by which they can identify the record, right? And this key should not change.

If you see at this moment, the country ID should be constant, but the country name can change, right? So I'm going to go and say okay, country ID is the business key, the primary key kind of thing. I'll do a next. Now, the slowly changing dimension wizard gives you three kinds of options. First one is fixed, fixed means if the value of the column change, then it will be treated as an error, it will come out as an error in that red arrow right. Now, the synchronization of the data or the updating of the data can happen in two ways.

One is you Don't want to maintain the history. So when you update the data, you say no, I'm not interested to maintain what was the last value to be updated. So, such kind of replication or synchronization is termed as type one change in the type one change the current data is overridden. So, for example, let us say on the source you have a value called as test and on the destination you have a value called as test one right before replication. And afterwards when you run a CD, it will just go and override that test one bit test, it will not maintain any kind of history while in historical attributes, this one The last option here, which is also called as the type to change in this the history is maintained in this it is maintained that what was the last change that was done. So, three ways of doing a CD one, when you say that when change happens, it is an error, fixed attribute.

Second one is type one and type two change In type one change, there is no audit trail maintained or there is no history maintained while in type two change history is maintained. So at this moment I don't want to maintain history I want to say this is a changing attribute. Okay. And now I will do a next next for now uncheck this box we'll talk about this later on, and I will say Next and Finish. Now the time you do a finish, you will see lots of logic been returned here automatically. So you can see here, the slowly changing dimension has given out two outputs, one which says new output.

So the record does not exist, it will actually fire over here. And if the record exists, it will actually go and fire update command to the database. So you can see two outputs here coming from the slowly changing dimension. One, if it is a new record, it will get added but if it is not a new record in will actually get updated you can see that there are some red signs here. So, you can see that there is a red red cross on the Insert destination as well as there is a red cross on the update destination right on the oledb command. Let us go and read what the error is.

So when I move my mouse here you can see it says column country name. Column country name cannot convert between Unicode and non Unicode string data types. Again, let us read the error column country name cannot convert between Unicode and non Unicode string data types. So what does this error mean? What does Unicode actually mean? So let us then spend some time in understanding Unicode and non Unicode first and then we will go and fix this error of Unicode and non you concepts are very important when it comes to creating multilingual applications.

Multilingual applications means applications which have to support languages other than English as well. Let us assume that your application does not need support of other languages it just needs to support English characters. If you look at English characters, the maximum English characters we have is smaller to say characters head capital A to Zed, then zero to nine and so on right. So maximum the characters are like 200 or 250 right. So, for one character, I can represent one character by one byte. So if I represent one character by one byte, then I can make to raise to eight combinations that is 256 combinations.

Right? And in 256, I can easily store all the characters of English right? So, if you are just if you just want to support English language, then maximum you need is for one character you just need one bite, okay for storage, but now let's assume that you need to support Sanskrit or you need to support Chinese characters you need to support Arabic you know where the number of characters are much more than 256 then for one character, you need two bytes. So, two bytes means to raise to 16 that becomes something like 65536 characters or combinations I will say. In other words, if you want to just support English, then one character can be represented by one byte, but if you want to support more than English, then one character has to be stored in two bytes. So what is happening here in this in this case now, if you see at this moment, our country name in database is Vertical and vertical means for one character he will take two bytes to store if this was a car datatype if this was a car data type right So, if you see here there is also a car datatype so if it was a car today, we won't have got this error right.

But now what is happening is in the database you are saying that your country name will get saved for one character it will take two bytes that's why it is an America so this American 60 means it will they hundred bytes internally actually. Okay. So now in the database it is. For one character is two bytes. It's assuming that this is a Unicode character when you say Unicode means for one character, you will use two bytes to store right and when it reads from the text file, when it actually reads from that text file, right? Country dot txt file, it assumes that this is a non Unicode character.

So now what is happening is when is reading from a text file, he's saying it is non Unicode. And he makes the data type as non Unicode. But it when it goes to SQL Server, suddenly there is a Unicode representation of data. So that's where the confusion is. So in other words, we need to actually go and do conversion, right. So we need to go here and say, let us go and create a new country name column.

Right? So let's say this is your country name you country name means this column will represent Unicode, right? You can see now we have at this moment, string DT underscore str, so I'll say No, not this DT underscore str, as used for one character is one byte. So we need to use something called as DT underscore W. str. So DT underscore doubleu str what is it that is you can see Unicode DT underscore w STR and this one is non Unicode so I'm going to go and use this and I will see okay and what I will do here is in this insert let me go and map you can see it at this moment country name is trying to map with the country name over here right? But we will say don't match Don't try to map the country name use this your country name, which is compatible with your data type.

Because as your country name actually represents one character with two bytes, right? So when I say okay, you can see now the red sign has gone off. You can see that the Same in the insert destination has gone off. Now the destination components What do you see here? So all these destination components here, what you can see on the toolbox, right? They only do insert, they don't do update or delete.

So this insert ad or dotnet destination here what you're seeing at this moment only does insert it does not do update it does not do delete. For that we have to use something called us oledb command. Okay, so this oledb command automatically, the STD wizard has added it but in case you want to use it individually, what you can do is you can go here to the SSIS Toolbox. And over here in the common you can see this oledb command. At this moment the SCD wizard has added this automatically in only DB command the full What you need to do is you need to go and give the oledb connection here, which he has, again done it automatically. And whatever SQL you want to write, you will right here, you, you have to click on this component Properties tab.

And you can see over here, you need to go and write the update command. So you can see that automatically, when they give the table name, he has written a update command here saying update the country name to something that the country ID is something. Now, in order to understand this update syntax, and how we can use this in the oledb command, we need to understand two concepts. One is parents and the sequencing of parents. Now over here, these question marks which are in this update command are nothing but their parameters. So the set country name, this is a parameter to this.

This is a parameter for the country ID right The first question mark, what you see over here is named as Param zero. The second question mark here is termed as Param, one and so on. So if you have four or five question marks, then you will have Param, three params for batum, five and so on. So the first question mark is Param zero second question mark is Param one, and then Param, two and then Param, three and param, four and so on. So, if we put in simple words Param zero is nothing but country name, and param. One is nothing but country ID.

So, now, when we go to map the columns, this is the way we do it. So, at the left hand side you can see that this is your output coming from slowly changing dimensions. So, I'm seeing a couple items zero go and map country name and pattern one, go and map country ID. But now And also we are getting the same error of Unicode you can see. So that means what we need to do is we cannot map this bottom zero with this country name, we have to map it with your country name the one which we have created from the data conversion component, okay. And then we will say Okay, so now let us go and run this program.

But before I do that, at this moment, we have done a silly calling one for country master. So, what we'll do is we will go and disable all the other data flows. So, I'm going to go and disable all the other data flows and let us first test if our series working with country master or not, right, and also you'd have to do a couple of cleanups. So let us go here. And first thing, I will go and delete all the records from the country table. So we'll say delete And let us go and open a new window.

So I'm going to open a new query window, so that we don't have confusion right. And in this let me go and fire this query. So at this moment, we have no records loaded from the country table right? Now, let us go and run this program. So I'm going to go and run this program. So you can see that our program has ran and you can clearly see it seems that okay, three records were loaded and those were all new records.

So, we can see they have all gone into the insert destination. Now, let us do like this, let us go back to the country dot txt file. Also let us go and check in our database they have been loaded or not. Yes, they have been loaded right. Now let us go and change a record let us go and change this to USA. Okay.

So, in this case, what I expect is I expect that because these two records are already been inserted, nothing should happen to them. But the third record which has been updated, I would like to go and see the update query been fired, right. So I'm going to go and run the program again. So that our program starts. So this time, I would like to see the record only in the update. So you can see now very clearly, in the new output, you know, there are no rows which have been sent, but look at the update command in the oledb command, you can see that one row has gone here.

And if I go and select again, you can see it as USC. Now couple of important points to remember here, if you go and change the ID, then nothing would work. Remember the ID is the business key. So if you go and make this for and if you try to update them, nothing will happen it will not work. The whole concept of STD stands on the basic Simple that the ID of the business key will never change. Now, this CD, what we have just implemented, right?

Is the type one. Remember I said in type one, it just goes and it overwrites the existing values but it does not maintain a history. But let's say I want to go and maintain a history I want to maintain a history saying that okay, previously it was us and now it is USA. So I want to maintain some kind of audit trail. Now, in order to do that, we have to first go and change our database design right. In other words, we need to go and change this table design here, which will actually help us to, you know, put audit trail data into this because at this moment, this structure what we have is not supportive for auditory So, I will do something like this, I will say that if the record is new, we can see that I have created a sub A third column here called as is new, which will store a boolean value.

So, you can see that I have used a bit data type. So, if the value is new, then this flag will be true orange this flag will be false. So, in other words basically whenever, so, if you see at this moment very quickly let us go. So, basically at this moment all the records are current new and fresh right. So, by default all the values will be two years So, this will be two, right this is two this is true, but now let us say I want to go and change this USA to us. So, it will now become the way the new entry should be.

It will say us this is true and this one One here will become false, right? So I'll say three and this value will become false here, right? But now you can see there's a problem. There's a problem here, the problem is that the country ID will not change, right, so that so we need to have duplicate country ID entries into this table. Again, a repeat. In order to support this new structure of audit trail, we have put a new flag here called us is new.

Whenever the record is new, this is new will become true. But the country ID here will be repeated. In other words, you know, this country ID can no longer be a primary key, we need to go and remove the primary key. So let's do that first, and then we'll do the things later on. So let us go to the design, view Object Explorer. So let's go to the design here and I will say I need to go and Remove this primary key right see if this is save it.

So, now, let us understand how the data will look like. So, first thing when everything is there, the record is there and it is current, the value is true. But now let's say somebody goes and changes the USA to us. So, what will happen is this will become false, I will have one more entry here saying us and this will become true. So, now, you can see it says that the previous record was USA and the new record what has been inserted is us So, this is the current record and the active record and the other records are there only for the audit trail. So, now let us go ahead and reconfigure our SCD to use this new database structure Write a new table structure.

Now in order to go and re edit the slowly changing dimension, what you have to do is you have to just right click on a component and say Edit. Once you do that, he again starts up the same wizard. So we can see now, he has started again the same wizard, and you can see that he's displaying this new field, what is that in your dim country, right. Now, this new field is only there to identify the old and the new record. So in other words, it cannot be a business key, or it cannot be a column on which the replication will happen, right. So I'm going to go and leave this as it is.

I'll say next, and over here, I will say that now I want to maintain history. So in other words, I want to implement type two change. Remember, type one change was or writing the or or writing the existing values and type to change will maintain historical values then I will say next. So now he says, Okay, tell me the column which indicates the current record. So I'll say the column which indicates a current record is this and the value is current, you can see when will the value be current the value will be current, when the value is true, or else if the value is expired, it is going to be false. So I'll say next and next and finish.

And you can see that there are again some couple of errors here and these errors are the same errors, what we have seen previously, so if you see these errors here, this red sign here if you see it is the same thing, that Unicode thing so let us go and configure it. So we will go and say that map this country name with country name is you country name is the same field which we have created from the which we have created from the data conversion component right. So there it is everything is fine, right and we are all set. So, let us go and run our application first thing in the text file, my mouse okay. So let's go to a text file here. You will say okay, so at this moment let me go to my table first.

Okay, so there is a table. Let me do like this first, let me go and delete all the records. So I'm going to go and delete all the records from the country. This and let us go and load the record. So at this moment in my table, I don't have any record Let me go and run this you can see even the logic has been quite bigger here if you look at the logic what we have at this moment for country master it is having derived columns can union all and a lot of things right. Okay.

So there it is, this is running. So let me go and see what is there now. So, you can see now he has loaded all the records and he says sees that this is all the new and the current records, so, he has marked them as true, right. Now, let me go to the text file and let me go and change this to us. Right. And let me now rerun the program, so, I'm going to go and stop debugging and let me again, rerun the program.

So, now what should happen is yes, that is you can see now Seems that okay, the new record is us and he has marked it as true. And you can see that he's saying, okay, USA is the old record and he has marked it as false. So you can see now he's maintaining the audit trail. Right? So, it's great, the audit trail is happening, if there are any updations he is maintaining the audit trail. But there is one problem, this audit trail the way it is maintaining have the updated values it is it does gives us the information saying what is the latest value as well as it tells you that you know what had changed, but it does not tell me when it has changed.

For example, at this moment, if you see, USA is old record and us is a new record, but it does not tell me that when did USA expire? When did this record expired? Right. So there are scenarios. For example, now if you see over here Like this product table, this product table I want to maintain that the if the product name has changed, I want to know at what moment the product name was changed, right. So, I want to maintain audit trail Yes, but I want to maintain in such a way that I want to know when the record was updated.

But now in order to have such kind of audit trail which will tell that when the record has been updated, it cannot be done by that simple is new flag right our table structure will become bit more complicated. So, let us go to the Object Explorer. So, let us implement this kind of SCD for the product table right. So let me go and open the product table in a design mode. So basically, if you want to know when the record has been updated, Or has expired we need at least two fields one field which is created date created right and let it be date and time so that we can know exactly at which time it expired and then I will say date, date not dates date expired and this will again be date and time and the product tape the you know there shouldn't be any primary key because we are going to go and insert duplicate records here right.

So, the data will look something like this now, so, let me go and open the table and let me show you how the data will look like. So, basically now let us say we have shoes so we'll say okay shoes record has been inserted in 2010 let us say at whatever time and if the record has not expired then this value will be null. But now let us say a new value comes into the shoes right So, somebody goes and says now henceforth chooses not shoes because it is shoe and it is for shoes, let us see, okay, so he goes and he changes the product name so, what I'll do now I'll say okay, this record is created in 2011 and it is not expired, but this record has expired. So, basically, if it is a new record, I will come to know or if it is an active record, I will come to know that the record is active when I see this date expired as null.

If I see this data expired, that means you know, this record is now inactive And it is archived. Right? So let's see if we can do this by using our CD or that right. So I'm going to go and delete this at this moment. Let me also go and delete all the records from the product table just to clean up right delete it right. And let us go and implement a CD in the productivity SS package right.

So, first thing as you have as we have done for the country, we have to first go and use we have to first go and use the we are first going to use the data conversion component right. And by using the data conversion component, we have to first go and convert that product ID into a numeric and we have to convert that product name into a Unicode value right. So let me go and repeat the full exercise. sighs what I've done previously for country. So let me just do it very quickly. So this 510 minutes is like a revision for you guys.

So this is the number we need to convert this force to numeric. So this is in product ID, this product name, we need to go and convert it into the Unicode so. So basically this is for byte signed integer, and this is DT underscore str. Right. And I will see Okay, the next thing is I need to go and use the CD component. So a CD, there it is.

So this data conversion input goes to him. And then we start with the wizard. So I'll say in one of the problems, what I face here Is that I'm not able to use the shared Connection Manager. I really hope that sometimes down the line, Microsoft implements shared Connection Manager also for a CD. And over here now I will go and use the product. So basically, this is my business key.

And this is what needs to be updated. And these two fields, I'm going to go and use it to indicate if my data has expired or not, right. So I'll say Next, I'll say product name. Yes. And this time, I want to go and implement historical values again, but this time, I'm reuse it with date and time. Then I will do a next and you can see that we have a second option button here.

So previously, we used this option button, where we had a single field of Boolean which indicated when the know whether the value has expired or not. But you can see now the second option button here which says that Okay, tell me, which is the column, which will indicate the start date, or the creation date of the record. So I'll say this is date created, and when is the end column that is date expired. And the variables will be set. As per the continuance start time, so basically, when the container gets initialized, at that time, I would like to go and set the date and time right? When I say container gets initialized means when you run your DTS x package, okay, so I'll say Next, we'll talk about the Enable inferred member support later on.

And I'll say finish. Right. And again, if you remember, we can land up into the problem of mapping. Let me just check that is it mapping with my Unicode Yes, it is good. At this moment SGD is implemented only in country master and in product. Right.

So what we'll do is we have already enabled the country master but we also need to enable the product right so I have enabled the product and the country Master, but the state sales person and fact I've disabled because over there we have done nothing at this moment right. So, this country master is using the CD CD where the audit trail is maintained by using the is new flag, but the product in this case we are also maintaining at what time the record was updated right. So let me go and run this. And let us go to our SQL Server and let us see what do we see in dem product? Okay, so I'm going here and saying okay, Execute okay the package just ran. So, let us do execute again oh okay there it is, I thought I executed a delete or what okay.

So, we can see now, he has updated all the products here and the best part is you can see that he has said that the date expired is null right. So, in other words you know this says that all of these records are active once right. Now let us go back to the product txt file and let us go and update shoes. So let us make shoes as sports smoke shoes. Okay. So I said this is sports shoes.

So, again we will stop this and read on this package and our expectation is the following. So, what we expect now here is that he will go and He will give an end time and date to this shoes entry and he will put a new entry here called a sports shoes with the time and date created and the date expired as an All right. So, let me go and fire this let us see what happens. And there it is, you can see so we can see now this shoes record here is expired, and the sport shoes is the new record. So now from this I can exactly know at what time the record was created, and at what time it was updated. Okay, so again, in type one, we have two kinds of CDs One is just a simple, flat, true and false, where we know what is the history of the update, but we have no idea when it was done.

And the second one is where we can exactly know at what time and date was the record updated. Now, before we end this video very quickly, I want to talk about a small thing here. Probably I did not do justice, I did not talk too much about that thing. You can see that when I created this integer data type right? In the data conversion, I chose four byte signed integer signed signed means it takes both negative as well as positive values. So it will go from minus hundred to plus 100.

Right. So basically, that's what we termed it as signed integer. Why aren't you signed integer because in SQL Server I have an in data type. And in data type in in SQL Server takes both negative positive values. in SQL Server, there is no way we can create a data type which takes only unsigned values, that means from zero to something. But in case if there are databases where you will connect and they are having unsigned, you can see that there is an unsigned integer here as well, right.

So, that's why I chose four byte signed integer. So great. So that brings us to the end of lab six. And in lab six. Again, we did a lot of things. We talked about CD, we talked about the different types of a CD that is type zero type one.

We saw that how we can do SVD. Without historical data we saw saw how we can do SVD with historical data. And with historical data again, there are two types of STDs in that. One is you can use a simple flag like is new is active, whatever you want to say. Second is you can use the data Time and know exactly when the records change. Also, we talked about a very important component that is the oledb command component, right.

So if you remember we talked about this oledb command component, which helps you to fire SQL. So, till now we are only doing insert by using the the destination components, but in case you want to do an update or a delete, then you need to use them with the DB command. And we also talked about Unicode. Right. So, in case in SQL Server, you have defined your data type as an archive, then you need to do a data conversion. So, we talked about what is Unicode what is non Unicode and how to do the mapping between SSIS and SQL server using the same So, almost a one hour video but covers very, very important fundamentals.

Right So, we have more Approximately 28 hours to go ahead 28 hours to go ahead, but you will find that in the coming one or two hours I'm going to go and start with SSRS very soon because what my belief is that we shouldn't be doing only one thing right. So, we have been doing SSIS for past four hours now but I would like to equally spread my hand into SSRS as well and then into SSIS and then after like 10 hours, I will be doing all the three things in one go. Okay. So 28 hours more more to go, do not give up and let us become a true msba professional. So again, finally, I know that I'm repeating this again and again, but your feedbacks are very, very important to us. You must be wondering that why after every video we talked about the feedback because we want to know that if we have done anything wrong in that video, okay, in case anything wrong is done if you want to correct it right away.

So please go to facebook.com slash postponed and let us know On which lab you are, right? Are you facing any issues? And how is this whole lesson going on? Thank you. Thank you so much.