 Hello, welcome to SSUnited, so see this side and this is the continuation of SSIS tutorial. So today we gonna discuss about how we can load data into our database from Excel file. So here you can see you will face two type of scenarios. So our first scenario is when database table contains nwarecare data type for string data like as we can see we have a table it contains four columns. First is ID, then name, then date of birth and then setting. So we have four columns. So in name column we can see we have nwarecare data type and we want to load the data in this table. Initially we don't have any data, let's have a look of Excel file. So this is our Excel file. As we can see it has four columns ID, name, date of birth and setting. So we want to load this data into that table. So first of all we have to use a Dataflow task because we are dealing with movement of data. So we have to use Dataflow task inside the control flow. And when we move to Dataflow panel then we can see we have to use Excel source from which we want to get the data. Then LEDB destination where we want to load the data. So let's move to SSGT to create the package. So here we need to create a new package, right click on this SSIS package, new SSIS package and we want to rename this with Excel to database nwarecare. So this is our package. So we have to use a Dataflow task. So we can drag and drop this Dataflow task and once we double click on this it will directly move to Dataflow panel. So inside Dataflow panel we have to use Excel source. So we can drag and drop this Excel source. And here we need to make the connection with Excel file. So once we double click on this Excel source then it will ask for Excel connection manager. So we don't have Excel connection manager at yet so we need to create a new one. It will ask for Excel file path. So we need to specify that file. You can browse and we can see and this is our Excel source. So we can select this file and click on open. Once we click on open then click on then we need to select the seed from which we want to get the data. So we want to get the data from seed one as we can see we have data in our seed one in seed two we don't have any data and seed three we don't have any data. So in seed one we have the data so we can select the seed one. Once we go to on columns then we can see we have id, name, date of birth and seed three. We have all the columns then click on OK. So we have done our source path. Now we move to our destination. So we want to load the data in our SQL server table. So we have to use LEDB destination. Now we can map and once we do click on this LEDB destination. So here it will ask for an Excel manager. So we need to click on new. Once we click on new then we can see our configuration LEDB connection manager. We already have a connection. So now I am going to delete this one and click on now. It will ask for server name. So we can select our server name. So this is our server name. Once we click on server name then it will ask for database name where we want to connect. So here we want to connect with SSIS test. Now we can check our connection, text connection succeed then click on OK. Again click on OK and click on OK. So here we need to select the table name where we want to load the data. So we want to load the data in our employee data destination and where care table. Now go to on mapping. So mapping succeed and click on OK. So as we can see in our table initially we don't have any data. Now I am going to run this package six rows. So these six rows should be inserted in our table. So we can select and we can execute as we can see we are getting the data. Once we have an where care data type for string data in our SQL server table and we want to get the data from Excel file and load in our SQL server table then we don't need to worry about it. It will directly move to data from our Excel source to destination and our Excel source will return an where care data type instead of where care. Our second case when database table contains where care for a string data. So as we can see in our second table. So this is our second table and it also has the same structure as our first table but it has where care data type for name column. Now we want to load the data in our second table and we can execute and we can check we don't have any data right now. How we can load the data first of all again we have to use a data flow task inside the control flow panel because we are dealing with movement of data and then once we move to data flow panel then we can see first we have to use Excel source and then we have to use a transformation which is data conversion. So data conversion because Excel source will return and where care data type and in our database we have where care data type. So it will not be mapped correctly. We have to use a data conversion by which we will convert our n where care data type to where care data type go to on SSGT. Now create a new package of this time Excel to database and where care. We have to use a data flow task. So we can drag and drop this data flow task. Once we double click on this it will directly move to data flow panel. So in data flow panel we need to use Excel source. We can drag and drop this Excel source then we need to make the connection. Once we double click on this Excel source it will ask for Excel connection manager. So we don't have any Excel connection manager at this time. So we need to create new one. Once we click on new then it will ask for Excel file path. Now we can select our file. So this is our file and click on OK. Then it will ask for Excel seat. So we want to get the data from seat one. We can select seat one go to columns. We want all the columns then click on OK. So we have done our source path. Now we need to load the data in our SQL server table. So we have to use LADP destination. We can drag and drop this and connect. Now again we need to make the connection for this LADP connection manager. We have already made the connection. So I am going to use this one click on OK. Now we need to select the table. But this time we want to load the data where string data contains wirecage data type. Click on OK. Now go to mapping, mapping succeed and click on OK. So here we can see our destination will not be succeed. If we read then we can see column name cannot convert between unicode and non-unicode string data types. So we have to convert first. So delete this connection and we have to use a data conversion. Go to on columns and we can see we have a transformation which is data conversion. We can drag and drop this data conversion. Once we do click on this then we want to convert data type for this name and our new data type should be string. So we can select our string and click on OK. So once we click on OK and we can see if we want to rename our output alias name then we can also rename like name new. So now click on OK. Now we need to map this data conversion with our destination. Click on destination, go to on mapping and this time we don't want to map name with name. We want to map this name with name new. So we can drag and drop this name new and click on OK. Now we can see error has been gone. Now we can execute this package. Once we execute this package then we can see our package succeed and six rows should be inserted in our table. So go to because of management studio and execute our selected statement. So as we can see we have six rows. So I hope you have understand how we can load the data if our database table contains where can data type for string and where can data type for string. Now we have few points to be remember. Our first point is it make connections with Excel workbook. So if we need to access multiple seats in same workbook then we don't require to create connection for each seats. Once we create the connection with our Excel workbook then we can access all the which is available in the same Excel workbook. Our second is we can we can also sort data at source by selecting is sorted property true and set a sequence in column spot key position. So we will cover this part once we understand about the merge. Next is we can select only specific range from Excel if required. Like in our Excel source as we can see we are getting six rows but we want to insert only three rows in our database. So how we can select only this specific range. So for this we need to understand about the data access mode. So here we specify how we want to fetch the data. For example if we have table or seat name in a variable then we can use it or if we want to write SQL statement to get the data then also we can use it. In our scenario we can see we have four types. First is table or view second table name or view name variable third is SQL command fourth is SQL command from variable. So I am going to use SQL command to select the specific range of data to go to SSDT also double click on this then we can see table or view. So I am going to change with SQL command. Here we need to write the SQL command. So select start from then we need to specify seat name seat one then we have to specify dollar sign after that we need to specify the range. As we can see select our first three rows. So we want to get one, two and three. So these three IDs and we also want to get our column names. So we will pick the data from A1 to D4 A1 to D4. Now we can preview and we can check. It's going to written only first three rows as we are expected. So go to on columns and we can see we are getting all the columns click on okay then execute our practice. Once we execute our practice, practice succeed now go to on SSMS and execute our selected statement. So we can see we have three rows. So I hope you have understand how we can select the specific range and we have also understand about the data access mode and we will understand about the table name or view name variable and SQL command variable. Thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos and please share to others. Thank you so much.