Hello friends, welcome to SSGunitech. My name is Susheel Singh and this is SSIS Tutorial Part 1. So here you will understand how you can download and install ssdt. If you are using sql2k14 or evo version then you have to download ssdt separately and install to your system. If you are using sql2k12 or lower version then ssdt will be available on your sql server setup. So you need to open your browser. I am using Google Chrome so I am going to open Google Chrome. And then you need to type ssdt or sql server and then you need to specify the version. As I am using sql2k14 so I have to specify 2k14. How you can download and install ssdt? So if you like this video please subscribe our channel to get many more videos. See you in next video. Thank you so much for watching this video. Hello. Welcome to SSGunitech. My name is Susheel Singh and this is continuation of SSIS Tutorial. So today we want to discuss about what is SSIS and what are the use of SSIS. And then we will discuss about what is the control flow and data flow. So let's start. So first of all what is SSIS? SSIS means SQL Server Integration Services. So SQL Server Integration Services is a component of Microsoft SQL Server Database Software that can be used to perform a broad range of data migration. So we can say that SQL Server is a tool by which we can do data integration and workflow applications. Next is what is ETL? So ETL is a three step process. First is extract then transformation and then load. So extract data from different different sources is our first process. Then we need to use some transformation by which our business requirement will be fulfilled. And our last step is load data into our database. So this is our control flow. So before going forward I would like to explain how we can start with SSIS. So first of all we need to go on start and then we need to type SQL Server Data Tool. So as you can see over here we need to open it. So this is our start page for SSIS. We need to go to one file then new and go to one project. So here we can see we have business intelligence inside we have three options. First is analysis services then integration services then reporting services. So now we are talking about integration services. Here we need to select our first option and we need to choose the path where this project will be exist. So this is our path we can rename with SSIS under code testing. So this path will not exist. So first of all we need to create a folder with the name of SSIS testing SSIS testing. Now we can select this folder. And here we can rename with SSIS tutorial. Now we can click on OK. Once we click on OK it will load our SSIS. So this is our SSIS and this is our initial package. So our first step is control flow. So control flow our first step then we have data flow. So we want to discuss about control flow. So here we can define the operations or task execution order. So it to wait until previous task will not be completed. Once previous task completed only then execute next task. So as here we can see in this example we have a data flow task. So if this data flow task will be executed successfully then our execute SQL task will be executed. If this data flow task will be failed then our script task will be executed. How we can configure our task and how we can set their execution order. So let's move to data flow. So this is our data flow. When we are talking about movement of data then our data flow task will come into picture. So it has three components. So first is source then transformation and then destination. In source it will pick data from different different sources like database, Excel, flat file, XML files. Then transformation it is used to clean up the data which we are getting from source according to our business requirements. And our last step is destination. We will load data into our destination. Destination could be database, Excel, flat file or anything else. So here we can see we have a Excel source and then we have a transformation which is row bound and then we have a destination which is our database. So this is our data flow task. In data flow task it will not be wait until our previous task will be completed. Once Excel source get data it will forward to row count and once row count will get data it will forward to our database. So I hope you have understand about control flow. You have understand about data flow as well. If you like this video please subscribe our channel to get many more videos. Thank you so much. Hello. Welcome to ssqlitec. My name is Suseen Singh and this is continuation of SSIS tutorial. So today we going to discuss about how we can load data from flat file to our database. So let's start. So as we can see over here first of all we have to use a data flow task inside this control flow in our first image. And in our second image we can see first of all we have to use a flat file source from where we want to get the data and then we have to use array db destination where we want to load the data. So this is our process which we have to follow. So let's move to ssdt. In our last video we have understand how we can start with ssdt. This is our package. First of all I am going to rename this package with flat file to database. So this is our first practice flat file to database. So as we can see over here we have to use a data flow task. So this is our control flow and here we need to use data flow task. We can drag and drop this data flow task. And once we directly click on this data flow task it will directly move to data flow panel. In data flow panel we can see in our ssis toolbox we have option for other sources. When we expand this other sources then we can see we have flat file source. Then we can drag and drop this flat file source. So before going forward I would like to explain about connection managers. So what is connection managers? So connection managers are used to make the connection to connect with our source or destination. For example here we want to get the data from flat file and then we want to load that data in our SQL server tables. So here we need to make two connections. First is for flat file source and second is for our OLDV destinations. So before making connection let's have a look of flat file by which we want to get the data. So we have this text file. So here we can see it has comma separated values and it contains three columns. First is for ID, second name and then data output. So here we need to load this data in our SQL server table. So go to SQL Server Management Studio. So in SQL Server Management Studio we can see we have a table with flat file to database table. It contains three columns. First is ID, then name and then data output. Initially we don't have any data and we want to load data from our flat file. Which is available over here we can see. It has comma separated values and total 8 rows is available over here. So we want to load this data in our SQL server table. So I need to create a connection for flat file source. So double click on this flat file source and here we can see flat file connection manager. Initially we don't have any connection over here so we have to make a new connection. So click on new. First we click on new then it will ask few things. First is file name. So we need to browse. As we can see flat file comma delimited source. We need to open. Then we need to go to format. In format we can see we have three types of formats available in flat file. First is delimited, second is fixed width and last is ragged write. So flat file source contains three formats. First is delimited, second fixed width and then ragged write. In delimited we can see values will be delimited by any delimited like comma, pipeline or anything else. And in fixed width column size will be fixed and in ragged write column size also will be fixed. But what is the difference between fixed width and ragged write? The only difference between fixed width and ragged write last column value is variable in case of ragged write. First of the column value should be same but the last column value will be variable. So here I am going to load data from delimited file and delimited is comma. So go to columns column delimited is comma. So we can select comma from this drop down. It has another option as well. So here we can see we are getting the data which is available in our text file. But we do not want to load double query which is available in our input. So how we can remove this double query in our source? So go to one general and here we can see text qualifier. In text qualifier we need to specify that character. That character is double query. So double query is specified over here. Now we need to go to one column and here we can see double query is removed. If we want to remove any another character then we can specify so go to one advance. So we can specify the data types. So first is id. Id should contain the value of integer so we can convert this from our integer. And now go to one name. Name contains the string and output column width is 50. It's fine to me. If we want to change this we can change by 100 or anything else. Then we can see in data birth. Data birth is also contains the string value. Now we don't want string value we want to convert this string value to dead time. So now we can select dead time time stamp that is fine to us and go to preview. It looks fine to us then we can click on OK. So here we have another option retain null values from the source as null values in the data flow. So what it mean? So when we check this check box then if we are getting any blank or zero length value from our source then it will be converted to null. As we can see preview over here then we can see our where id is 8 then name value is blank. Once we checked this check box then we can preview then this blank will be replaced by null and once we load this data in our SQL server table then we can see null will be loaded instead of blank. Now we can click on OK. We have done our source part. Now we need to make the connection for destination where we want to load the data. So we need to select OLEDB destination. So this is our OLEDB destination. Now we can see we have two pipelines over here. First is for this task will be completed successfully and then for failure. So we want to pass this data from this flat file source to our destination. Now we need to make one more connection for this destination. Now once we double click on this then we can see it will ask for OLEDB connection manager. Now we need to create one more connection. I have already created the connection now I am going to delete these connections and create new one. So once we click on new it will open this window. It will ask for server name. Now we can select our server. So here we can see this is our server name. And here we need to select the database name by which we want to connect. Then it's SSI status where we want to connect. Now we can check the connection. Connection succeed then we can click on OK. And then we need to click on. So connection has been made. Now we can enable to access the tables. So as we can see we can scroll down and we have flat file to database table. Go to on mapping. So mapping succeed because the input column names and output column names both are same. So it will be mapped automatically. Now click on OK and execute our package. So we can see it was and our package completed with successful. We can stop this package and go to our SQL server. And we can execute our and now we can see now we are getting the data. And for ID 8 name value was blank in our flat file so it has been removed by null. So I hope you have understand how we can load the data from flat file to our SQL server tables. If you like this video please subscribe our channel to get many more videos. Thank you so much for watching. Hello, welcome to SSUnitech. So see in this side and this is continuation of SSIS tutorial. So today we are going to discuss about how we can load data into flat files from database as I am using SQL server. So we have to load data from SQL server to flat file. So here we can see again we are dealing with movement of data. So we have to use a data flow task inside the control flow. And then when we move to data flow panel then we have to use already the source from which we want to get the data and then we have to load in a flat file destination. So we have a table which contains the employee information. We have four columns ID, name, date of birth and salary. So we want to load this data into a flat file and that flat files we contains comma separated values like ID, then comma, name, then comma, date of birth, then comma, salary. So we will get data in comma separated. So how we can achieve this? Let's move to SSGT. So here we have this SSGT. Now we need to create a new package. So now we need to remember it database to flat file and comma separate. As we discussed, we need to use a data flow task. So drag and drop this data flow task was double click on this. It will directly move to data flow panel. Inside the data flow panel, we have to use OLDB source because we want to get the data from SQL server table. So this is our OLDB source. We need to drag and drop. So here we need to make the connection with the SQL server. So double click on this. It will ask for OLDB connection manager. So we need to create a new one. We have already created a connection. So delete this and created new once click on new. It will ask for server name. So we can select the server name. So this is our server name. And then it will ask for database name by which we want to connect. So we want to connect with SSIS test. Then we can test our connection. Connection succeed. Then click on OK. And again click on OK. So we have made the connection. Now we need to select the table from which we want to get the data. So we want to get the data from employee. So this is our employee data table. Now we can preview. So we are getting 4 columns as we have discussed ID, name, date of birth, salary. Close it and go to on columns. So do we want all the columns or we want to skip any column. If we want to skip any column then we can unslip that column name. As we don't want date of birth in our flat file. So we can skip over here. Now click on OK. So we have done our source path. We want to load the data in flat file. So drag and drop this flat file destination. Now we need to connect. And now we need to make the connection with the flat file. So double click on this flat file destination. It will ask for flat file connection manager. So click on new. I need to create a new one because we don't have any connection right now. Now we can see it will ask for flat file formats. So we have different different flat file formats. First it delimited, then fixed width, then fixed width with a row delimiter, then right right. So we want to use first one. Click on OK. First we click on OK. So our flat file connection manager editor will be open. So now we need to select the file. So we don't have any file right now. So we need to create new. So we can do the name with database to flat file comma separated. Click on OK. So here we can see our format. Format should be delimited. Then we can see our header row delimiter CRLF. It will be discussed in next video. And then our first row should be column names. So we can select this checkbox. Now move to columns. So in columns we can see column delimiter. So column delimiter should be comma. As we can see it has been selected by default. If we want to change then we can also have option to change it. Now go to advance. So here we can specify the size. If we want to increase the size then we can increase. Like for name we want length of this should be 500 then we can select over here. Now click on OK. Now go to mapping. So inside mapping our mapping succeed ID will be mapping with ID then name then cell. So now we can click on OK. And run this practice. Once we run this practice we can see 47 rows. So 47 rows has been loaded in our flat file. So go to on flat file. So we can see database to flat file comma separated. Now we can open it. So here we can see we have ID name and cell. And it contains the comma separated values. So ID then comma then name then comma then cell. And then move to next row. So we are getting the data. We can close it and stop this practice. So as we have discussed we have four flat file formats. First is delimited then fixed width then fixed width with row delimited then right right. How we can load data from delimited we have discussed over here. In next video we will discuss how we can load data into fixed width. And then how we can load data with fixed width with row delimited and then right right. And we will also understand what are the differences between these flat file formats. Thank you so much for watching this video. So if you like this video please subscribe our channel to get many more videos. Thank you so much for watching. Hello. Welcome to SS Militech. So see in this side. And this is continuation of SSI's tutorial. So today we are going to discuss about how we can load data into database from flat files. In our last video we have also discussed how we can load data into database from flat files. But the format was delimited. So today we are going to discuss how we can load data when flat file formats are fixed width and right right. Let's start. So again we are dealing with movement of data. So we have to use a data flow task inside the control flow panel. And when we move to data flow panel then we have to use a flat file source and then LEDB destination. In flat file source we want to connect with our flat file from which we want to get the data. And then from LEDB destination where we want to load the data. In fixed width each row contains one complete record of the information. And each row contains one or many piece of data also referred to a column or feeds. Each data column has a defined width specified as a number of characters that is always same for all the rows. If we open our fixed width file then so this is our fixed width file. Now we want to open in notepad plus plus. So here as we can see for ID we have 11 characters and then for name we are having 100 characters. Then for salary we are having 12 characters. And at last we have two characters one for CR then LF. So we want to load this file in our database. So how we can load it? We have a table flat file to database table. So initially we don't have any record and it contains three columns ID, name and salary. And we want to load data in this table. So how we can load it? Let's move to SHGT. So this is our SHGT. Now I am going to create a new package. So I need to rename with flat file to database with fixed width and right right. So as we have discussed in control flow panel we need to use a data flow task. So we can drag and drop our data flow task and this is for fixed width. So we can also rename with fixed width. Once we double click on this it will directly move to data flow panel. Inside data flow panel we need to use flat file source. So go to other sources then flat file source we can drag and drop. And then we need to make the connection to connect with our flat file. So double click on this and it will ask for flat file connection manager. So we don't have any connection manager right now so create new one. Once we click on new our flat file connection manager editor window will be open. It will ask for file name. So we can browse and select our file. So this is our file fixed width. Now we can open it. Once we open so we need to select the file format should be fixed width. So we can select fixed width. And we can see our text qualifier is disabled. So text qualifier is only enabled once we are dealing with delimited. Once we move to drag drive then also it's disabled. Now we move to columns. So inside columns as we can see we have total how many characters. So we can see 123 is the total length. So we have to select 125 because 123 for this data and 2 for this delimited. So we need to select 125. So this is 125 now. So here we need to select 11 as we can see in our flat file for ID how many characters. Once we count it should be 11. So we need to select 11 and second for 100. So we need to select with 100 plus 11 so triple one and go to on advance. Once we move on advance then we can see we have ID name and salary. We can preview as we can see it looks good. So click on OK. Once we click on OK then we have option to retain null values from the source as null values in the data flow. So we can select this checkbox. Go to on columns. So we are getting ID name and salary. We are OK with it. Click on OK. Now we have done our source path. Now move to destination. So we want to load data in our OLEDB destination. So we can drag and drop and then make the connection. Once we connect then we need to make the connection to connect with our database. So double click on this and go to on OLEDB connection manager. Click on new. Now we have already make the connection. So delete this one and create new. Here we need to select the server name. Once we select the server name then it will ask for database name by which we want to connect. So we want to connect with SSIS test. So we can check the connection. Connection succeed. Click on OK again OK and OK. So here we have made the connection without database. And then from which table we want to load the data we can select over here. So this is our table. Go to on mapping. So inside mapping as we can see we need to map the columns. So ID should be mapped with ID. Then name should be mapped with name. Then salary should be mapped with salary. So we have done the mapping. Now click on OK. Once we click on OK. So we have done our practice. Now we need to execute. Once we execute as we can see 18 rows. So 18 rows should be loaded in our table. At least we don't have any data. So now 18 rows should be present over here. So we can see and we can count 18. So it looks good. So we have done our fixed width. Now we move to ragdright. In ragdright each row contains one complete record of the information. As we have discussed in fixed width. Each row contains one or many piece of data also referred as a column or field. Each row column has a defined width specified as number of characters. That is always the same for all the rows. This is what difference between fixed width and ragdright. Last column value is variable in case of ragdright. We can disable this data flow task. So right click on this and disable. Now we need to drag and drop one more data flow task for ragdright. And we can rename with ragdright. Once we double click on this it will directly move to data flow panel. So inside data flow panel we need to select our source and destination. So our source is flat file. So we can drag and drop. Now we need to make the connection. So double click on this as we have already make the connection for fixed width. So we do not want to use it. Create new one. Click on new and then it will ask for file name. So browse and this is our file name. So click on open. Once we click on open then we need to select the format. So format should be ragdright. Click on OK. So now we move to columns. Now we need to select the column size. So first should be 11. Then move to our next. That should be triple one. And our last column value should be variable. No need to select that value. Now we can click OK. Once we click on OK then we need to select this checkbox. Once we select this checkbox then go to one columns. So we are getting id name and cell D. And click on OK. Once we click on OK our source part has been done. Now we need to make the connection with destination. So polydb destination drag and drop this. And we need to connect. Once we connect then we need to make the connection with the database. So here we need to set the table. As we have already made the connection for SQL server. Then I am going to use that one. Just only set the table name. So we want to load the data with the same table. As we have used in fixed width. So we can select go to on mapping. Now we need to map with id. Then name. And cell D is automatically mapped. So no need to worry about it. Click on OK. Once we click on OK then as we can see we have 18 rows. Now we need to delete this data. So delete from table. Once we delete. Now we do not have any data right now. Now execute this practice. Once we execute this practice we can see 47 rows. So 47 rows should be inserted in our SQL server table. Go to our SQL server. Execute. And we can check 47 rows. So data has been loaded in our SQL server table. Stop this practice. And so I hope you have understand how we can load data into database from flat files. When formats are fixed width or right right. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Hello. Welcome to SSUnitech. So see you this side. And this is continuation of SSI tutorial. So today we gonna discuss about how we can load data from database to flat files. In our last video we have discussed how we can load data from flat file to our database. And we have also understand about the flat file formats and connection managers as well. If you haven't watched part 3 of this video series. So I would strongly recommend to watch that video where we can understand how we can load data from flat file to database and flat file formats. Which is very important. And today we will also understand about the fixed width and right right formats. And make the difference between these two. So let's start. We have to use a data flow task. Because we are dealing with movement of data. When data will move from any source to destination then our data flow task will be used. So as we can see inside the control flow panel we have to use a data flow task. And when we move to data flow panel then we have to use our source and destination. So we are going to get the data from database. So we have to use LEDB source. And we want to load the data in our flat file. So we have to use flat file destination. So let's move to SSGT. So this is our SSGT. And we need to create one more package. So now we can right click on this SSIS package. New SSIS package. And our package is created. Now we need to rename this package. We are getting the data from database. And then we are going to load data in our flat file. And format should be delimited. So this is our package. So now we need to use a data flow task because we are going to transfer the data. Once we double click on this it will directly move to data flow panel. So here we want to get the data from database. So we need to use LEDB source. So this is our LEDB source. Once we double click on this then it will ask for connection manager. And initially we don't have any connection manager. So we need to create a new connection. Once we click on new then we can see we have already made a connection. So this time I am going to delete and create a new connection. Once click on new so it will ask for connection manager. First server name. So we can select the server name. Once we click on server name. So it will ask for database name by which we want to connect. So we want to connect with SSIS test. We can test the connection. Connection succeed. So click on OK. So we have made our connection. Now click on OK. Next it will ask from which table we want to get the data. So we want to get the data from EMP data table. Now we can preview and we can see. So we have four columns in this table. First is ID, second name, then date of birth and then salary. So we want to load this data in our flat file. We can see our columns. So we want all the columns. So we can select all the columns. If we want to skip any column then we can unselect this check box. And this time we have only three columns. And we don't want date of birth. So we can uncheck this one. Click on OK. So we have done our source part. Now we need to make a connection for our destination where we want to load the data. So this is our flat file destination. We can drag and drop this. And then once we double click on this flat file destination then it will ask for flat file connection manager. Once we click on new then it will ask for flat file format. So we have four type of flat file format. First is delimited. Second is fixed width. Then fixed width with row delimited. Then right right. So we will cover one by one. So first I am going to load the data in delimited. So click on OK. It will ask for file. So in our destination folder I am going to create a file with data base to flat file delimited. First row so column name. So here we can see we have the columns and we have only three columns because we don't want to load the data for data of birth. So that column is removed in our source. Once click on OK. Then go to on mapping. Mapping succeed. Click on OK. So now I am going to execute this practice. Once we execute this practice 47 rows inserted in our flat file. So this is our destination folder where we can see. So here we have data base to flat file delimited. So here we can see we have comma delimited file and we can see we have all the records. Now we can close it. And now we can rename this with delimited. So I am going to disable this dataflow task and create new one. And this time we will load data from fixed width. So this is our source quality resource. Once we double click on this then we have already make the connection. So we need to select only table name. So this is our table name. Go to on columns. We don't want data of birth. Click on OK. Now I am going to make connection for flat file destination and then we can connect and double click on this. Because we have already a connection in this package. So it is shown over here. But we won't create new one. And this time I am going to load data from fixed width. Click on OK. And create a new file with data base to flat file and this time fixed width. Click on OK. First one should be column name. Go to on columns. We are OK with it. Click on OK. Go to on mapping. So we have done the mapping and click on OK. Now I am going to execute this practice. 47 rows affected. Now go to on folder. And we have this fixed width. So here we can see we are not getting the data as we expected. So why we are getting with this data? Let's cover data. So we need to rename with fixed width. We have two more formats. So we need to create that one. So this time create one more data flow task. Double click on this. Go to on source query db source. Double click on this. And select our table name. So this is our table name. Now go to on columns. We don't want data birth. Click on OK. Now go to on flat file destination. So flat file destination we can select. And once we double click on this we have two connection managers. So it's available over here. But we want new one. Click on new. And this time we want to load data from fixed width with row delimiters. Click on OK. Browse. Fixed width with row delimiters. Open. First row should be column names. Go to on columns. We are OK with it. Click on OK. Go to on mapping. Then again click on OK. Now we can execute this practice. And stop this one. And here we can see. So this is our file. We can open it. This is our fixed width flat file format. But we have one more additional column over here. Flat file format which is ragged write. So we can disable it. And create new one. Double click on this. And go to on source. So we have already the source. Click on it. Create one more connection. Select the table name. Go to on columns. You don't want data birth. Uncheck this one. Click on OK. And this time this is our flat file destination. Now we can connect with it. And now click on this. So this time we have three connection managers. Because we have already created this one. But we won't create new one. And this time with a ragged write. Click on OK. It will ask. So we want to create a flat file with ragged write. So this is our ragged write. First of all should be column names. Then columns. You are OK with it. Go to mapping. Mapping succeed. Click on OK. And execute this package. So once we execute this package or practice executed successfully. Now go to on files. And make the differences between these files. Now I am going to open these files in notepad plus plus. So this is our notepad plus plus. Open this one. So here we can see we have ID, name, salary. We have two more characters in our delimited file. Which is CRLF. Which indicates for new line characters. So this is our delimited file. So now I am going to open fixed with file. It will notepad plus plus. So here we can see we don't have any delimited. That's why we are getting the data in a sequence. So in our first row we have all the data. So this is our column name. And our data is started over here. So we can see this is our data. And So this is our row. And this is our second row. Because we don't have any delimited row here. So once we move to our row delimited. Then we can open in notepad plus plus. And here we can see we have all the data. But we have an extra column over here which indicates the row delimited column. And this is our row delimited column. And we have two characters. CR then LF. So we are getting two more characters over here. So this is the difference between fixed with row delimited and fixed with flat file format. And go to on right right. And open this file in notepad plus plus. So here we can see in our last column we have the variable values. If you are not getting any value then this column contains zero length. If we have the value then like 5000 for this row. So we have 5000. So this is the difference between right right and fixed with flat file format. Once we deal with fixed with then we can see our last row contains the full length. Like cell D contains we can count how many characters. We have 11 characters. So it will take 11 characters in each. Either it has the value or not. But in right right for this we have only 4 characters so we can see over here. So I hope you have understand about these file formats and how we can differentiate between these. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. Thank you so much for watching. Hello, welcome to SS Unity. Sushil this side. And this is continuation of SSIS tutorial. So today we gonna discuss about how we can load data into our database from Excel file. And you can see you will face 2 types 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 4 columns. First is ID, then name, then date of birth and then cell D. So we have 4 columns. So in name column we can see we have nwarecare data type and we want to load the data in this table. So basically 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 4 columns ID, name, date of birth and cell D. So we want to load this data into that table. So first of all we have to use a data flow task because we are dealing with movement of data. So we have to use data flow task inside the control flow. And when we move to data flow panel then we can see we have to use Excel source from which we want to get the data then while ADP destination where we want to load the data. So let's move to SSDT 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 we have to use a data flow task. So we can drag and drop this data flow task and once we double click on this it will directly move to data flow panel. So inside data flow 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. We 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 sheet from which we want to get the data. So we want to get the data from sheet one as we can see we have data in our sheet one. In sheet two we don't have any data and in sheet three we don't have any data. So in sheet one we have the data so we can select the sheet one. Once we go to columns then we can see we have id name, date of birth and set date. 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 which equals our table. So we have to use OLEDP destination. Now we can map and once we do click on this OLEDP destination so here it will ask for connection manager. So we need to click on new. Once we click on new then we can see our configuration OLEDP 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 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 written in 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 the moment 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 written in 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 and where care data type to where care data type go to on sdt 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 seed so we want to get the data from seed one we can select seed one go to columns we want all the columns then click on ok so we have done our source part now we need to load the data in our SQL server table so we have to use OLA-DP destination we can drag and drop this and connect now again we need to make the connection for this OLA-DP connection manager click on new we have already make 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 where the 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 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 and 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 name 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 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 6 rows should be inserted in our table so go to management studio and execute our selected statement so as we can see we have 6 rows so I hope you have understand how we can load the data if our database table contains wirecat data type for string and n-wirecat data type for string now we have few points to be remember so 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 or second is we can also sort data add source by selecting is sorted property true and set a sequence in column sort 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 6 rows but we want to insert only 3 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 face 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 sequence statement to get the data then also we can use it in our scenario we can see we have 4 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 1 then we have to specify dollar sign after that we need to specify the range as we can see select our first 3 rows so we want to get 1, 2 and 3 so these 3 ideas 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 is going to written only first 3 rows as we are expected so go to columns and we can see we are getting all the columns click on OK then execute our package once we execute our package package succeed now go to on ssms and execute our selected statement so we can see we have 3 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 table 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 hello welcome to ss unitec so still decide and this is continuation of ssis tutorial so today we gonna discuss about how we can load data into excel file from our database as I am using sico server so we will use sico server and we will load data from sico server table to excel if you haven't watched previous video of this video series then I would strongly recommend to watch that video where you can understand how we can load data into database from excel file so here we will understand how we can load data into excel file from database so again we will face two type of scenarios in our first scenario as you can see database table contains nwarecat data type for string data as we can see in our first table we have four columns first is id, name, date of birth and salary so we have four columns in our second column as we can see on name we have nwarecat data type so we want to load data from this table to our excel file we have six rows so these six rows should be inserted in our excel file so how we can do this first of all we have to use a data flow task inside the control flow panel because we are dealing with movement of data and then move to data flow panel inside data flow panel we have to use oledb source from where we want to get the data and then excel destination where we want to load the data so let's move to ssdt to create the package we want to create a new package right click on this ssis package then new ssis package now I want to rename this package from database to excel and this is for nwarecat so this is our package name so inside control flow panel we have to use data flow task so we can drag and drop this data flow task and once we double click on this it will directly move to data flow panel so inside data flow panel we have to use oledb source where we want to get the data so we can drag and drop this oledb source next we need to make the connection with our database so double click on this oledb source then we can see it will ask for oledb connection manager so click on new we have already connection I want to delete this connection and create a new one once we click on new then connection manager window will be open and inside we need to select our server name so we can select our server name so this is our server name we can select I am using window authentication then we need to select the database by which we want to connect so we want to connect with ssis test now we can check our connection connection succeed click on ok so here we need to select the table so this is our table employee data destination nwarecat go to one columns as we can see ID name date of birth and cell D so we have all the columns click on ok so we have done our source part now we need to configure our destination so we want to load data in our excel destination so we need to drag and drop this excel destination and connect now double click on this excel destination then it will ask for excel connection manager so we need to select excel connection manager as we don't have any excel connection manager yet so we need to create a new one now we can browse and this is our destination go to excel and EMP data nwarecat now we can click on open once we click on open so here we can see our first row has column names then click on ok once we click on ok then it will ask for seat name so here we don't have any seat because our excel file is not available at yet so we want to create a seat we can click on new as we can see create table excel destination so it will create a seat we can click on ok so once we click on ok then we can see excel destination seat will be available in our excel file so we can select go to one mapping so in mapping as we can see id name date of birth and selfie all these columns have been mapped successfully so we can click on ok now we need to execute our package as we can see six rows should be inserted in our excel file so go to excel file so this is our excel file we can open and we can see we are getting six rows so we have six rows and our first row should be column names so this is our first scenario in our second scenario as we can see database table contains worker data type for string data so here if we want to load data from database to excel file and our table columns contain worker data type then it will not be mapped successfully and our package will not be succeed until our final list we will not use data conversion so let's start here need to create one more package click on new SSIS package and this time we need to rename with database to excel and worker so again we need to use a dataflow task inside control flow panel so we can drag and drop this dataflow task once we double click on this it will directly move to dataflow panel inside dataflow panel we need to configure our package so here we want to select our source so our source is OLEB source because we want to get the data from sequence over table now we can make our connection and I want to delete this and create a new one we can select our server name so this is our server name now we need to select our database name so database name is SSIS test now we can check our connection connection succeed click on ok again ok and again ok so here we need to select our table from which we want to get the data so we want to get the data from this worker table so we want all the columns and click on ok so as we can see in our second table we have worker data type for name so we have selected this table and we have 20 records on this table so these 20 records should be inserted in our excel file so here we need to select our destination so our destination should be excel destination so we can drag and drop this excel destination and need to map now we need to make connection for this excel destination so we need to select file I want to rename with worker now click on open and ok here we don't have any seed at yet so click on new seed click on ok again ok and our seed has been created now go to on mapping so inside mapping we can see our mapping succeed so click on ok so here we can see our destination has reflected an error so what is error the column name cannot be converted between unicode and nonunicode string data type so we have to use data conversion to convert nonunicode data types to unicode data type so we have to use data conversion so drag and drop this data conversion and map double click on this data conversion then we can see our name we can select name and we can rename our output alias name with name new then we can see our data type currently we are having string data type so we want to convert this string data type to unicode string then click on ok now we need to map with our destination now go to on mapping so as we can see we want to delete our existing mapping with name and map with new column which is name new now click on ok as we can see error has been gone now we can execute our practice so once we execute our practice so 20 rows should be inserted in our excel file so go to on excel file as we can see this is our excel file so we are having 20 rows so normally these two types of scenarios will be faced when we want to load data into excel file from database I hope you have understand how we can load data into excel file from database if you like this video please subscribe our channel to get many more videos and please share to others thank you so much for watching hello welcome to SS Unitec social this site and this is continuation of SSIS tutorial so today we gonna discuss about how we can load data into raw files from flat file and then we will also understand how we can load data into database from that profile at last we will understand what are the difference between flat file source and file source so let's start as we are dealing with movement of data so we have to use dataflow task inside the control flow panel and when we move to dataflow panel then we can see we have to use a flat file source where we want to get the data and then we have to use a raw file destination where we want to load the data so before going SSDT let's have a look of flat file so this is our flat file as you can see it has comma separated values first is id then first name, middle name last name and data output so it contains total 5 columns so we want to load this data into raw file so let's move to SSDT so this is our SSDT now we need to create a new package so right click on this SSIS package and create a new package and then I am going to rename this package from flat file to raw file so first of all we have to use a dataflow task inside the control flow panel so drag and drop this dataflow task and then double click on this dataflow task then it will directly move to dataflow panel so inside dataflow panel we have to use our source so our source is flat file so we need to drag and drop this flat file source now we need to make the connection with our flat file so double click on this then it will ask for flat file connection manager so click on new then we need to select our file so file name we can browse so we need to select the file so this is our file and click on open as we can see we have delimited values so go to our columns and we have comma separated values so we have 5 columns first is id, first name middle name, last name and data so we are getting the data now click on ok so retain null values from the source as null values in the dataflow task so go to columns as we want all the columns so we have to select all these columns and click on ok so we have done our source part now we need to configure our destination so destination should be raw file destination so this is our raw file destination we need to drag and drop this raw file destination so now we need to connect once we connect then we need to directly click on this raw file destination so once we active then we see we have access mode it has two options first is file name second is file name from variable if we have file name in any variable then we can select our second part if you want to select our file then we can select our first option now we can browse and we need to create a new raw file so we can set with raw and click on open once we click on open then we can see our right option right option has four options so first is create always create once append and truncate and append so what it means so first is always create it is used to drop and recreate raw file once execute our practice so it will always create a new file and drop our existing file in our second option create once it is used if you want to execute our practice once it will create raw files only once and if you want to execute package again then it will reflect an error so if we have any scenario by which we need to create that row file only once then we can select our second option and our third option is append it is used to append the data to our existing row file truncate and append it is used to truncate existing data and append the new data so these four types of right option available so we need to select create always so go to on column page how many columns we want we want all the columns so we can select all the columns then go to on connection manager and generate initial file so file has been generated successfully now click on ok so once we move to our folder then we can see raw file has been generated and we can also have a look inside the connection manager we don't have any connection manager for this raw file destination so there is no need to make any connection manager to connect with our raw file so I'm going to execute our package so as we can see five rows so five rows should be inserted in our raw file so stop this package and go to on raw file so this is our raw file I'm going to open this in notepad plus plus so as we can see we are getting the data it's not very easy to understand now we need to create one more package by which we will load this data into our database so this is our database and we have raw file table in this raw file table we have ID first name middle name last name and the data upper so all these columns are all label in our raw file so now create a new package right click go to on new package I'm going to rename this package with a raw file to database so now we are dealing with moment of data so we have to use a data flow task inside the control panel then double click on this and inside the data flow panel we have to set our source and destination so our source is a raw file source so we can drag and drop this a raw file source and double click on this then it will ask for the file name we can browse and select our file so this is our raw file click on open so once we click on open then we can see columns so we are getting all the columns which we have seen earlier now click on ok so we have done our source path now we need to configure our destination so destination is already we destination so we can drag and drop already we destination now we can connect once we connect then we need to make the connection to connect with our database so click on new so I have already make the connection I am going to use that one click on ok so now we need to select our table so our table is a raw file table we can select that table and go to on mapping so mapping succeed click on ok now I am going to execute this package so once I execute this package then five rows should be inserted in our SQL Server table so go to on SSMS now I am going to execute our selected statement so here we can see data has been inserted successfully in our SQL Server table so I hope you have understand how we can load the data from flat file to raw file and then raw file to SQL Server next what are the difference between flat file source and raw file source so first is flat file source a flat file is an ordinary file with text data like TXT or CSV files in raw file a raw file is basically dump of our data in SSIS to file system next is we can read the data using notepad or any another editor tool in case of flat file but in case of raw file data is in binary format so we cannot read it in case of flat file connection managers are required to connect with flat file but in case of raw file there is no need to make the connection so in case of flat file it's very slow to transfer the data as compared to a raw file so in raw file it's used to transfer the data very quickly so I hope you have understand how we can load the data from flat file to a raw file and then raw file to a database and you have also understand how we can differentiate between flat file source to a raw file source so 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 with others thank you so much hello welcome to SS Unitec so see this site and this is continuation of SSIS tutorial so today we are going to discuss about what are the project parameter and what are the package parameter and how we can use it on Dataflow task so as we can see parameter are very similar to local variables which we used to create in sikoso and we can access them from the package that we created and we cannot access the package parameter from different packages like if we have created any package parameter in package one and if we want to access that parameter in package two then that will not be available over there so let's move to SSGT this is our SSGT as we can see this is our package and we are going to create package level parameter so how we can create so right click on this package and then we can see our third option which is variables so click on variables then we can see it has few options so we can click on this then we can see first is name then is scope so scope is our package so this parameter will be available on this package if we want to access this parameter from another package then it will not be available over there so we can rename with count and value be 0 and here we can specify the expression as well click on this as we can see so we have formulas over here we can use this formula according to our requirement next is project parameter so project parameter are like two global parameters so we can access these project parameters from any packages which is present in the same project as we can see over here if we will create any project parameter then we can access that parameter in all these packages which is available in a single project so here we can see project dot params so double click on this and it will open another window and this is for the project parameters so now again we can click on this add parameters and here we can specify like package count here we don't have the option for the scope so this parameter will be access from all the packages so now we can save it and close it so now we have created our package parameter and project parameter both so next is how we can use it so this is our case HD as we can see we want to load data from SQL Server table to flat file and apply filter at the source using the variables which we have created so first we will use package parameter then we will use project parameter so let's start so this is our table says detail with cell person table and it has the values according to cell person and we want to load the data from that cell person which we will pass through parameters so as we can see over here then we need to create a access parameter then name for cell person then value should be string and we can specify value now we can close it and so here we are going to dealing with movement of data so we have to use a dataflow task so whatever click on this inside dataflow panel we can configure our source and destination so sources already be sourced now we need to configure our source we need to use the connection as we have already created the connection so click on okay and then we can see data access mode so this time I am going to use SQL command and here we will specify the command so this is our query so we can specify where condition with cell person equals to question mark so if you want to apply the filter on the variable then we have to use the question mark so this is our question mark and companies command go to on SSGT and here we need to paste that command then we can see we have the parameters option we need to map those parameters so click on parameters and here those parameters will be all able as we can see this is system parameters so this is our cell person so this is our package parameter click on okay then again go to columns as we can see we are getting all the columns then click on okay so need to configure our destination so destination should be flat file now click on this delimited file click on okay we need to select the file this is our file click on open column names go to one columns so here we can see we have the data in this file we have the data for our now click on okay good one mapping mapping succeed click on okay so let's have a look of flat file so this is our flat file as we can see it has few rows but the cell person is Harvard so now we are going to execute for join as we can see our variable and this time for join so now we need to execute our package so 16 rows should be inserted in our flat file stop this package and go to our flat file open it we have the data for join now close that file and go to one project parameters then click on this and this time we need to add one more parameter with package cell person and value should be a string and then we can specify the cell person name so here we have join Howard Rahul so we need to put Rahul icon we can save it and close it now we need to change our mapping so go to on this quality be source then go to one parameters so inside parameters as we can see we have X parameter so change with parameter so this is our project parameter and this is for cell person so click on okay again click on okay and execute our package 30 rows should be inserted in our flat file so go to one flat file and open it as we can see Rahul's record has been inserted successfully so I hope you have understood how we can use parameter and how we can use package parameter thank you so much for watching this video if you like this video please subscribe our channel to get many more videos hey guys welcome to SSU Tech so still this site and this is continuation of SSIS tutorial so today we gonna discuss about dynamic connection manager so what is dynamic connection manager so dynamic connection manager will be created automatically like we can see we have a scenario where we have SQL server table and we want to load the data from SQL server table to our flat file destination and here we can see we have flat file name with sale underscore 2018 which is our year then month then date then are and then minute so we have this flat file once we run this flat file on 1054 our flat file name should be 10 underscore 54.txt file when our time will be changed then we need to run our package then it will automatically generate a new file with the new date and time so let's have a look of SQL server table now we need to execute so as we can see it has total six column cell order number cell order date customer ID quantity value and country go to on ssgt to implement the package so here we need to create one more package so right click on this SSIS package then new SSIS package then I gonna rename this package with dynamic connection manager then we need to drag and drop data flow task after that we need to double click on this data flow task so it will directly move to data flow panel inside this data flow panel we need to configure our source so our source is already be source so we need to drag and drop already be source then we need to load data in our flat file this is our flat file destination so we can drag and drop now we need to configure our source so double click on this already be source here we need to select the next manager click on new here we have already made the connection so I gonna use that one so click on ok here we need to select the table so our table is cell info table so we can select and we can preview so we can see we are getting the data now go to on columns now we want all the column then click on ok so here we need to connect with destination now we need to configure our destination double click on this flat file destination then we need to select the flat file connection manager so I need to click on new then we want create delimited file we are ok with it so click on ok so here we need to select the file name so we can browse and here we want to generate file with the name of sale so we need to write sale then click on open columns name in the first data row so we can check this checkbox go to on columns so as we can see we are getting the all columns now click on ok then go to on mapping so mapping succeed now click on ok so once we run this package so it will generate a file with the name of sale so after some time once we run this package again then it will not going to create one more file with date and time so for that we need to create two variables so go to on variables so here I want to use for folder name so this is our folder name data type should be a string so we can select a string and then we need to specify the value so this is our folder path so we can copy and we need to paste that in our value and we can create one more variable with the name of file name and data type should be a string so we can select a string here I am not going to pass any hard code value so we want to generate the file name at runtime so go to on expressions we can click on this here in this expression we need to write some code so first of all we need to write sale and here we can check so we are getting the sale then underscore then we need to concatenate sign which is plus then we want to get date and time so we can use get date for current date and time so this is our date time and sale underscore is our string so we need to convert so here we have to write dt underscore w str then we need to specify the size so I want to use 100 now I need to evaluate so once we evaluate then we can see we are getting the date and time but we want to fetch here then month then date hours and minute so I want to fetch only four characters from starting so service string then we need to specify start value so we want to start from one then how many character we want for factor so we can select four then close the bracket evaluate then we can see we are getting the data now we need to use underscore so we can again evaluate so we are getting the year now we need to configure for month so I am going to copy this code and then plus sign and paste that value this time we want to get the value from 6 and we want to get only two characters for month so as we can see we are getting 12 now again we need to specify our concatenate symbol which is plus then paste this code and this time we start from 9 and we want to get only two characters for date now what one evaluate so we are getting 0 code between this we need to use underscore so we can specify underscore now we can evaluate so as we can see we are getting year month and date now we need to get our so again we need to use underscore so this is our underscore then I want to paste that value so this time we want to start from 12 and we want only two characters now we can evaluate and we can see we are getting the value this is for our now last time we need to use for minute so underscore concatenate symbol then paste that value so here we can see we are getting from 12 then we need to start from 15 and we need to use only two character so we can evaluate and we can see we are getting the minute as well now we need to use the file extension should be txt file now we can evaluate and we are getting the value so we can okay so here this is our file name and this is our folder name so we have done it now we can save this package and go to one flat file connection manager then we need to press F4 to get the properties inside this property we can see we have expressions so click on this expression and here we have connection string so in this connection string we need to go on expressions then here we have folder path so we can drag and drop this folder path within expressions then we need to use concatenate symbol which is plus then we need to drag and drop this file name now we can click on evaluate so this is our folder path inside this folder our flat file name should be this and this flat file would be generated now we can okay again okay so as you can see time is 11 6 so I gonna run this package once we run this package then our file should be generated with the time 11 6 go to one folder so here we can see we are getting the file with 23 underscore 0 6 so this is our 11 6 so I gonna stop this package and again run this package when our time will be changed so as we can see time has been changed I gonna run this package one more time so click on start so new file should be generated go to one folder as we can see we got a new file so this is our dynamic connection manager so it will generate a new file on every execution if the time will be changed because it's dependent on time so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos and don't forget to press the bell icon to get the notification of our newly uploaded videos thank you so much hello welcome to SSUniTech so see this side and this is continuation of SSIS tutorial so today we gonna discuss about what is the row count transformation and how we can use it in our last video we have discussed about the project parameter and package level parameters and how we can use it inside the data flow task so today we gonna discuss about a row count if you haven't watched previous video of this video series so I would strongly recommend to watch that video so let's start with the row count so what is row count so row count transformation is used to count the rows as they pass through data flow task and stores final count into a variable basically row count transformation is used for the auditing purpose so here as we can see first of all we have to use a data flow task and inside data flow task we are going to get the values from flat file source and then we have to use a row count transformation where we can count the number of rows that is going to pass from flat file source and we will store that count into a variable and then by using script task we will see that count so let's start with this case is steady so go to on ssgd and here I am going to create one more practice and rename this practice with row count so this is our row count and as we have discussed we need to use a data flow task so this is our data flow task double click on this it will directly move to data flow panel so inside data flow panel we are going to get the values from flat file so our source is flat file so we can drag and drop this flat file source and need to make the connection for this flat file source double click on this then it will ask for flat file connection manager then click on me so it will open flat file connection manager editor window here we need to select the file we can browse and we can select our file this is our file click on open and go to on columns as we can see we are getting the value so click on ok then click on ok so we have done our source part now we need to use transformation so row count transformation is a label inside the common here we can see row count so we can drag and drop row count and we can map now we need to declare a variable where we will store the value for this count put one variable add a variable here we need to specify count and value should be integer so we have done for it and now double click on this row count then we can see it will ask for variable from which variable we want to store the values so we want to store in this variable click on ok so we have done our row count transformation now we need to check the value then go to on control flow panel so inside this control flow panel we have to use a script task by using you can see the output now map and double click on this script task then it will ask for scripting language I am going to use sysop and we have option for bb as well here we have two options first is read only variable second is read and write only variables so I am going to use read only variable click on this then we can select our user count then click on ok then we need to click on this edit script once we will click then it will take few seconds then it will open a script so inside this script we have main block so inside this main we need to specify our code so we can use message box dot so then DTS dot variables inside the bracket we need to specify our variable dot value dot to string so we have done our code and this code will be available on the description of this video so you can copy if you need then close it click on ok so now I am going to execute this package so as we can see 32 so if we can see our flat file then it should have 32 rows so here we can see so we have total 32 rows so these 32 rows are going to display by row count transformation so I hope you have understand how we can use the row count transformation how we can use in real-time scenario once we will cover auditing of the package then we can understand the real-time use of row count so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos hello welcome to SS Unitec so see this side and this is continuation of SSIS tutorial so today we gonna discuss about one more transformation which is derived column transformation so direct column transformation is very important transformation because here we can apply the expressions according to our business requirement so let's start with derived column transformation so what is derived column transformation so basically it is used to create a new column values or replace existing column values by applying expressions so what is expression so an expression can contain any combination of variables functions and columns from transformation input for example if we are getting data from SQL server table and we want to apply few formula to manipulate the input then we can use derived column transformation by which we can manipulate our input data it also has an option to concatenate data from different columns like if you are getting the data like first name and last name and we want to concatenate first name and last name to get the full name so by using this transformation we can achieve this so we have few examples over here as we can see we have input table and it has few columns so in our first condition we want to concatenate first name and last name so we can do this second if you want to get the only first character from first name then also we can do this if you want to apply round function inside the salary column and we want to only two digit after decimal place so we can do this if you want to extract only here from data birth then we can also do this so how we can do this so let's start so this is our table as we can see it has four rows we have displayed in our slide so let's move to ssgd so here we need to add one more package right click on this SSIS package and add a new SSIS package and rename with derived column so this is what arrived column transformation so first of all we have to use a data flow task and inside this data flow task we have to configure our source so our source is sequence over table so we have to use OLDB source now we need to configure OLDB source here we need to specify the connection manager so click on new as we have created the connection so I'm going to use this one click on ok here we need to select the table so this is our table we can select and go to one columns so we want all the columns click on ok so we have done our source part now we need to use derived column so this is our derived column transformation so we can drag and drop as we can see over here it has effects so we can use expressions so double click on this derived column and go to one columns so first of all we want to concatenate first name and last name as we can see over here so first first name then last name so we can drag and drop this first name inside this expression so this is our first name then we need to specify plus symbol and then we want one space then again concatenate symbol then last name so we can drag and drop this last name and we can rename this with full name this is our full name as we can see derived column inside this derived column we have dropped down what we want we want to add a new column or we want to replace our existing column so we want to add a new column for this so choose our first option so we have done our first part now in our second foundation we want to get only first character from first name so drag and drop this first name inside the expression then we need to write substring so this is our substring and we want only first character so we have to specify one and one so we can close the bracket so we have done our second part and this is for first character from first name so our next is we want around the salary so drag and drop the salary column inside the expression and we have to use around so we can specify round comma two because you want around only two additional so we have done our third and this is for round salary so our next is we want to extract your part from that of birth so we need to drag and drop that of birth this time and so here we need to use that part function so that part then we need to specify what we want to extract we want to extract here so we have to specify here then close the bracket so this is for here so we have done all these so now I am going to use multicast I will explain multicast in upcoming videos for this time I am not going to load this data into any table or any flat file I am just going to populate this data by using enable data viewer it will pause this package and display the data so now I am going to execute this package so once we execute this package then we can see package is paused between these two tasks because data viewer is enabled between them so as we can see we have full name then first character from first name then round salary and then here so we are getting the data as we were expected so we have done all these four examples now as we can see I have explained this so here we can see we can also use if a condition like if the month name is equal equal to one then we want to display Jan if the month name is equal to equal to two then we want to if this value not be satisfied then we want to this number so this is our if then then again if then else so we can use if else condition inside our drag column so how we can achieve this we can now I am going to delete this one and going to add one more column so so I am going to use for employee ID if the employee ID is less or equal to two then we have to use Goshenma and we need to specify low then again column and we have to specify our employee ID less or equal to three then we want to display medium else we want to display large so this is our if and else condition now we can check again I am going to use multicast and going to connect here we need to enable our data where now I am going to execute as we can see last column has been added successfully so for ID 1 and 2 we are getting low for ID 3 we are getting medium for ID 4 we are getting large so we can use if and else condition as well we can also replace the values by using the first condition as we have discussed over here or we have a replace null formula by which we can directly replace the null values now I am going to draw I am going to add one more column and this time if the null value is present in this department name then we want to replace that value so this is a function which is available inside this editor if we need then we can use so go to on null function so we have replaced null so we can use it replace null then if the null value is present in this department name then we want to display and so now I am going to again enable it over so as of now we can see we have the null value in department name if the employee ID is 3 so now I am going to execute this package so null value should be replaced by NA so for this column as we can see null value has been replaced by NA so we can use it if we needed so we have a lab assignment for you check if null and divide by 0 values are available if available then replaced by 0.00 and formula for this lab assignment will be available on the description of this video if you need then you can copy from there so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos hello welcome to SSPiratec so see in this side and this is continuation of SSIS tutorial so today we gonna discuss about conditional split so what is conditional split and how we can use it so first of all what is conditional split so it is MSBI component which is available on SSIS toolbox and next is it's just like a if-as condition or case statement by which we can split our input data into multiple outputs depending on the certain condition which we will be applied next is it has one input and multiple outputs depending on the condition and it also has an default output so that if a rows doesn't match with a specified condition then that row will be redirect to that default output and it also has an error output so let's move to our case study so we have two case study first is based on the string data and second is based on the integer data so let's start with string data so in this scenario as we can see we have basic crossover table that contains different countries sale detail our requirement is to split the data for USA and India in different flat files and rest of the countries will move to a different flat file let's have a look of sequence over table so as we can see this is our table and if we can execute our first query then we can see we have total four countries India USA Australia and Nepal so we want to split data India in different flat file USA in different flat file and in our third flat file we will contain the information of Australia and Nepal so how we can achieve it let's move to SSGT so I am going to create a new package so right click on this and create a new package and then we can rename this package with conditional script case study one now we are dealing with movement of data so we have to use a data for us then double click on this and now here we need to configure our source transformation and destination so our source is already be source so we can drag and drop this already be source now we can configure our source so here we need to select our connection manager as we have created the connection so I am going to delete this one and create anyone here we need to select the server name so this is our server name we can select and then here we need to select our database name so our database is SSIS test now we can test our connection connection succeed click on okay again okay so here we need to select our table as we can see in our sequence over then we can select our second statement and good one SSGT and we can change our data access mode and here we can write our SQL statement I am going to paste that SQL statement now move to columns as we can see we want all the columns then click on okay so we have done our source part now we need to configure our transformation so what one common then we can drag and drop this conditional script and we can connect with source not only from this condition split then it will open condition split transform as an editor here we can specify our conditions so first condition is if the country is India then we need to select double equals to instead of single and then double correct instead of single now here we need to specify our country so here we want to split our data in case one if the country is India so in our second condition again we need to drag and drop and here we need to set for USA this is for USA and rest of the data will move to our default output as we have discussed now click on okay so we have done our transformation now we need to configure our destination so I am going to load data in flat file so we have to use that file destination so we can drag and drop and this flat file for India here it will ask for what input we want to move this destination so in our case one as we can see in our case one we have specified for the India so this is for case one if you want then we can change it as well in our case two it will reflect the output for USA so I am going to drag and drop this flat file destination for USA as well so this is for USA and we need to connect and it will ask what output we want we want case two now we can click on okay then we need to drag and drop for default over it will contain the information of Australia and Nepal so this is our default output so here we need to configure our destination so as we can see inside this folder we don't have any file at yet then click for India and it will ask for flat file connection manager so click on new so here it will ask for flat file format I am going to use delimited now click on okay then here we need to select our file go to this folder and we want to create file with the name of India first row should contain the column names go to one column it will contain the comma separated values so we are okay with it click on okay go to one mapping so mapping succeed again click on okay so we have done for India now I am going to configure for USA again we need to repeat the process we can browse inside this condition is great here we need to create file with the name of USA so click on open go to one columns so we are okay with it now click on okay go to one mapping mapping succeed so click on okay here we need to configure our default output so click on new delimited file we need to browse and here we need to specify the file name so default click on open so column names in the first row go to one columns click on okay now go to one mapping so mapping succeed again click on okay so we have done our package now I am going to execute this package as we can see package has been executed successfully now I am going to stop this package and move to our folder so here as we can see inside this India we have the data for India only and inside this USA we have the data for USA only and inside this default we have the data for Nepal and Australia as well so this is for consistent split now move to our second case is today so in this case is today as we can see we have integer or numeric data types so we need to take care of while choosing their orders for example if we have 57 11 12 and 25 so we have two scenarios by which we can select our order in our case one if we will select if the number is less than 30 then all data will move to that output and rest will not contain any information in our case 2 as we can see this is our correct order so here we need to specify order in increasing order like we want to get the data is less than 10 then 20 then 30 so we have to specify in increasing order first less than 10 second less than 20 then less than 30 so it will return output as we are expected so this is our case is today now we can implement as we can see we have a table with conditions split I'm going to execute and we can see we have all the data now I'm going to create one more package for this case history and this time conditional split case history 2 so again we need to drag and drop over data flow task double click on this here we need to select our source so our source is sequence of our table so we have to use OLDB source not double click on this OLDB source then we have to specify our connection manager so as we have created the connection so I'm going to use that one here we need to select our table so this is our table go to one columns it has only one column now click on ok so now we need to use conditional split so go to inside this common and drag and drop conditional split now we need to select so after mapping directly on this conditional split here we need to specify our conditions so in our first case as we can see I'm going to select the value if less than 30 second if the ID value is less than 20 third if the ID value is less than 10 now click on ok so I am going to use multicast for display data because I am not going to load this data into any destination so this is for case 1 this is for case 2 and this is for case 3 I'm not going to configure over default output so click on this enable data viewer then again enable data viewer and again enable data viewer now I am going to execute this package so once we execute this package as we can see in our case 1 we are getting all the data we don't have any data in our case 2 and in our case 3 because we have selected incorrect sequence so now I'm going to change the sequence first less than 10 less than 20 is okay then less than 30 now click on okay now I'm going to execute this package again so as we can see we have the data in our case 1 which contains the ID less than 10 in our case 2 we have the data when the ID is less than 20 in our case 3 we have the data if ID is less than 30 we are getting the data as we are expected so thank you so much for watching this video I hope you have understand how we can use conditional split and we need to take care while using the conditional split inside the integer and numeric data if you like this video please subscribe our channel to get many more videos hello welcome to SSUnitech so see this side and this is continuation of SSIS tutorial so today we gonna discuss about multi-cast and what are the difference between multi-cast and conditional split in many interviews you can see interviewer wants to ask about the differences between multi-cast and conditional split so we will cover this part in this video if you haven't watched part 13 of this video series so I will strongly recommend to watch that video where you can understand about the conditional split so let's start with multi-cast so what is multi-cast so multi-cast transformation in SSIS is used to distribute every input rows to every rows in one or multiple outputs if you have a scenario by which you want to load the data from any source to multiple destinations and data would be the same as we are getting from input then you can use the multi-cast in our scenario as you can see we have a table and we want to load data from that table to multiple flat files so this is our table as you can see it has 20 rows so we want to load all these 20 rows in multiple files so here as you can see we have three folders file 1, file 2 and file 3 so we want to generate three files and that file will contain all the data which is available in this table so how we can achieve it let's move to ssdt so I am going to create a new package so right click on this and create a new package then rename this package with multi-cast so this is multi-cast now I am going to drag and drop data flow task because we are dealing with movement of data then double click on this data flow task so here we need to configure our source transformation and destination so our source is OLEDB source because we want to get the data from SQL Server table so here we need to configure our OLEDB source so double click on this it will ask for OLEDB connection manager so click on new we have already made the connection so I am going to delete that connection and create a new one so once we click on new then connection manager editor window will be open and it will ask for server name so we need to select the server so this is our server name and then log on to server I am going to use windows authentication so there is no need to enter the user ID and password so here we need to select the database name so this is our database name we can test our connection connection succeed click on okay then again okay so we have done our connection manager now we need to select the table so this is our table go to one columns we want all the column then click on okay so we have done our source path now we need to use multi-cast so go to inside this common and here we have multi-cast so we need to drag and drop this multi-cast now connect with source and then we want load the data into three different files so we need to select the flat file destination so flat file 1 flat file 2 and flat file 3 so in our flat file 1 we need to connect with multi-cast then 2 and then 3 as well now I am going to make the connection for flat file destination 1 it will ask for flat file connection manager so we need to click on new then we need to select the flat file format I am going to generate the file with the delimited format now click on okay so here we need to select the file name we can browse so here we can see we have three folders so I am going to generate the file inside this file 1 folder this is for one so click 1 then open column names in the first row we need to select this checkbox then move to columns we are okay with it then click on okay now go to on mapping so mapping succeed now click on okay now we need to configure our second file right click on this click on new then again delimited file here we need to select the file and this time I am going to load the file inside this file to folder click on open column names in the first data row then move to columns so we are okay with it click on okay go to on mapping mapping succeed now click on okay now we need to configure our third file so that double click on this again click on new we want to generate the delimited file so we are okay now we need to select the file and this time I am going to load the file inside this file 3 folder 3 then open select this checkbox go to one columns okay got one mapping mapping succeed now click on okay so we have done our package so initially we don't have any file in this folder if we can open then we can check it we don't have any file so now I am going to execute our package once we have executed our package then we can see 20 rows 20 rows and 20 rows and in our input we can see 20 rows so 20 rows we are getting from our input and all the 20 rows have been moved to in our file now we can stop our package and go to our folder in file 1 as we can see we have the file and it contains 20 rows now move to our second folder then again we have 20 rows then moved to our third and last folder as we can see we are getting 20 rows so as we can see we have redirect all the rows which we are getting from our input to all the outputs then what are the difference between multicast and conditional expect and this is very important so first is multicast so replicate source data to multiple destination so that different logic can be implemented to to the same source data in case of conditional split distributed the source data into multiple destination based on the condition in our multicast we cannot apply any condition but in case of conditional split we can implement the condition in case of multicast redirect all the rows to every output but in case of conditional split direct row to single output in case of multicast it doesn't have any error output but in case of conditional split we have an error output in case of multicast we cannot apply any expressions because we can't use any condition go to our package then double click on this multicast we don't have any option by which we can apply any expression or condition but in case of conditional split we can specify our conditions by which we can apply our expressions so in case of multicast it doesn't have any default output but in case of conditional split it provides default output so that if rows doesn't match with expression then it redirect to default output as we have discussed in our last video so these are the differences between multicast and conditional split and you need to remember all these differences so that you can explain in your interviews so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos hello welcome to SSU detect so still decide and this is continuation of SSIS tutorial so today we gonna discuss about the short transformation and this is very useful transformation while you are going to use the merge statement and merge join so what is short transformation so short transformation is used to sort source data in either ascending or descending order which is very similar to order by statement in tcql command merge transformation and merge join transformation need data to be sorted before using them so if you are getting the data from the source which is not in sorted so by using short transformation we can sort the data and it's a fully blocking transformation so we need to avoid this transformation while using the big table and it's also used to remove the duplicate records from the source data it only look for the source key column value if it found duplicates then remove any of them and get unique values like if we have four columns in our table like id name data birth and gender and we are going to sort on the graph of id and in id column we have the duplicate record and rest of the column we don't have the duplicate values so it will look only id column and if duplicate value will be found then one will be deleted and unique value will be available so we have a sequence over table for our testing purpose so let's have a look of that table so this is our table with the name of sort as we can see it has only one column which is named and it contains the values so in our first row in our last row we can see it seems to be duplicate so one values will be deleted so how we can achieve this let's move to ssdt to create our package so right click on this and create a new package and then I want to rename with sort then we need to drag and drop dataflow task double click on this and here we need to select our source so our source is OLDB source so we can drag and drop OLDB source and then we need to configure here we need to select our collection manager click on new as we have already made the connection so I am going to use that one click on ok here we need to select the table so our table is sort as we can see this is our table got one columns we have only one column which is named so we are ok with it click on ok so we have done our source part now I am going to use the sort transformation so this is our sort transformation we can drag and drop this and connect with the source now we need to click on sort once we click on sort then we can see available input columns so we can select our column and we want to order by with that column in ascending order or descending order according to our requirement we can set the sort type here we have option for remove rows with the duplicate sort values once we select this checkbox then our duplicate value will be removed now click on ok as we can see in our input we have a duplicate value and that value will be removed I am not going to load this data into any destination I am going to display this data by using multicast so we can drag and drop this multicast then we can connect to the sort now we can enable our data viewer then we can execute our package so here we can see 1 2 3 we are getting only once so duplicate value has been removed next is sorting with the string data SQL server sort and SSIS sort treats as a differently because SQL server uses the case in sensitive and SSIS uses the case sensitive so as we can see in our SSIS sort and you know SQL server sort so in our first room we have the same data in our second row and third row we are getting the same data in our fifth room and your sixth room so we have the different values in case of SSIS sort and SQL server sort let's have a look in SSGT and SQL server so here as we can execute our package now I am going to copy this data so we can open Excel and paste that data now go to SQL server and I am going to use order by 1 and copy this data and paste that data so as we have removed the duplicate value so we can delete our 1 2 3 and here we can see so this is our SSIS sort and this is our SQL server sort so we are getting the different values in case of fourth row fifth row in case of seventh row eighth and ninth row because SQL server uses case in sensitive and SSIS uses case sensitive so how we can remove this different behavior so once we use the cases to in our SQL server then that will be removed so go to our SQL server and paste that query here we can execute and copy the data and go to on Excel now I am going to paste that data and delete one row as we can see in our SSIS sort and SQL server sort both are treated as a similar so you need to remember while using sorting with the string data types so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos thank you so much hello welcome to SS unit X so see this side and this is continuation of SSIS tutorial so today we gonna discuss about much transformation so what is much transformation so much transformation is used to combine two sorted data sets into a single data set the rows from each data set are inserted into output based on the value in their key columns it also requires that the must column in its should have matching metadata like if you want to merge to input data sets column 1 in our data set 1 in column 1 in data set 2 these two should have the matching data types we cannot use the more than two data sets this is our case 1 where we need to use short transformation second we will understand without using short transformation because thought transformation is fully blocking transformation so we need to avoid if we can so here we can see we have two input seats seat 1 contains the information of male employees second seat 2 contains the information of female employees so we want to merge and load in our ledp destination so let's have a look of input seats so this is our male employees as we can see it has employee ID name gender and salary we have total four columns go to one female employees here again we have employee ID name gender and salary so we have total four columns so we want to merge these two seats data and upload in our ledp destination so let's have a look of equals our table where we want to load the data so this is our merge statement table so initially we don't have any data if we can execute over first query then we can see we don't have any data and it also contains the four columns first is employee ID second name gender and salary so we have total four columns now go to one SSDT to implement this package here we need to add one more package so right click on this SSIS package and create a new package and then we need to rename with merge we are going to use short so we can use short now inside this control flow panel we need to use data flow task so we can drag and drop this data flow task and double click on this data flow task so here we need to configure our source transformation and destination our source is Excel file so we need to use Excel source double click on this Excel source Excel source editor window will be open so here we need to make the connection manager here we need to select the Excel file path so go to one merge first of all I am going to make connection with main employees click on open click on okay here we need to select the Excel seed so see to one it contains the data so we need to select the seed one go to one columns you want all the column then click on okay so we have done with male employees we can rename this Excel source with male second we need to configure for female so we can rename with female not double click on this female here we need to make one more connection for female employees select the file click on open then okay again we need to select Excel seed seed one contains the data go to one columns we want all the column click on okay so we have done our source now we need to configure our transformation so here we need to use short transformation but I am not going to use short transformation at yet I am going to use directly mulch if we can drag and drop mulch then we can see over here we have down arrow and up arrow it shows that we need to use sorted input if we are going to use unsorted input this is for merge one click on okay for merge two if you can click then we can see error message it shows me that the input merge one and input merge two has sort key position assigned to their columns incorrectly it means we need to use short transformation to sort the input data so I am going to use short transformation we can drag and drop short transformation for male and female as well then we can connect with male and female now we need to double click on this short transformation for male here we need to sort with employee ID and click on okay then double click on this female we want to sort with employee ID then again click on okay so now we have sorted input data for male and female as well now I am going to use much transformation so we can connect with merge input one here we can see we don't have option for input three we have option only for input one and input two so it has the limitation where we cannot use more than two inputs so as we can see error has been gone once we double click on this then we can see we have output column name merge input one and merge input two so we are okay with it now we need to configure our destination so our destination is well adb destination so we can drag and drop well adb destination and connect with merge now double click on this well adb destination so well adb destination editor window has been open we need to make the connection manager so as we have already made the connection so I am going to use that one here we have merge statement table so this is our merge statement table got one mapping so mapping succeed now click on okay so initially we don't have any data as we have displayed now execute our package as we can see 19,000 rows has been inserted in our table if we can execute our select a statement then we can see 19,932 rows has been inserted this is our case one so in our case two without using sort transformation so here we can sort data at source by using it sorted property true and set sort key position so how we can implement create one package SSIS package new SSIS package we need to rename our package with merge so here we need to drag and drop our dataflow task then double click on this dataflow task now we need to configure our source transformation and destination so our source is excel source so we can drag and drop excel source double click on this excel source click on new we can browse and select for main we click on okay now we can select our excel seed seed one go to on columns we want all the columns click on okay now we can right click on this so advanced editor here we have input output properties here we have an option for is sorted we can select this property with true once we click on true then go to on output columns in this column we need to set the sort key position sort key position should be one click on okay for female employees we need to make connection browse female click on okay now we need to select our excel seed seed one go to on columns we are okay with it now again then so advanced editor input output properties excel source output here we need to select this is sorted with true then we can expand output columns employee ID sort key position should be one then click on okay so now we are getting the data is sorted at source so we can use this now we can use our merge transformation drag and drop this merge transformation for male this is merge input one click on okay then merge input as we can see the click on this merge and we are getting the output column name merge input one and merge input two we want to load this data in our only be destination so we can set up already be destination now we can connect with merge click on this click on new as we have already made the connection so I am going to use that one and again we are going to use data in merge transformation table go to on mapping mapping succeed click on okay so we have done our package now go to on SSMS here we need to truncate our table so as we can see is we don't have any data in this table so I am going to execute this package so package has been executed successfully now we can stop this package go to on SSMS and we can execute our selected statement as we can see in nineteen thousand nine hundred and thirty two rows has been inserted successfully in our table so this is our case two so I hope you have understand how we can use the merge transformation with short transformation and without using short transformation so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos thank you so much here is welcome to SSQT and this is continuation of SSIS tutorial so today we are discussing about union all transformation and what are the differences between union all and merge transformation so let's start with union all so what is union all transformation so union all transformation in SSIS is used to combine data from multiple sources and that source could be excel files, flat files or anything else or multiple sequence over tables and produce one output that could be used in your destination table next is union all transformation doesn't follow any specific order while merging the data and sorting into all just so when we mainly use union all transformation if the transformation input is not sorted as we have discussed in our last video and we have seen that much transformation needs data sorted before using it next is combined output doesn't need to be sorted and next is when we are having more than two inputs then we cannot use much transformation we have to use the union all transformation so these are the cases where we need to use our union all transformation this is our case study as we can see we have three input sheets first sheet contain the information of male second female and third unknown so by using union all transformation we want to merge data between three sheets and then we want to load in our OLDB destination so let's have a look of excel sheets so this is our female implanted as we can see it has four columns employee ID name gender and second next is for male employee again we are having same number of columns now unknown employee as we can see we have employee ID name gender and second so all three sheets contains same number of columns and data types are also same as you can see in case of employee ID we have integer in case of name we have string data in case of in the again we have string data and in case of cell V we have integer data now let's have a look off the crossover table where we want to load the data so this is our table as we can see it has employee ID name gender and second so it has total four columns so now I'm going to SSDT to implement this practice so here I want to create one more practice so right click on this SSIS practice and new SSIS practice then I want to rename the stack as union all this is our union now we have to use data flow task inside the control panel so let's drag and drop this data flow task then double click on this data flow task once we deliberately on this data flow task then it will move to data flow panel inside this data flow panel we have to configure our source transformation and destination as discussed we have Excel file so we have to use Excel source let's drag and drop three Excel source first is for male then female and then and rename this with male rename this with female and this is for unknown so we can rename them so I want to set up our source for male so double click on this male then as we can see Excel source editor window has been open so click on new here we need to select our Excel file path so you can browse and go to inside union all and then I'm going to select male then open so first row has column names we are okay with it click on open here we need to select our seat where data is available so data is available in our seat 1 so we have to select seat 1 then go to one column inside this column you want all the volume so you can click on open so as you can see error has gone now double click on this female now we need to select our female employee seat so this is our female employee seat click on open here we need to select our Excel file so seat 1 contains the data columns click on open now we need to configure for unknown names here we can click on new then select our unknown name by seat and click on open here again we need to select our seat so seat 1 contains the information so we need to select seat then go to one columns inside this column as we can see employee ID name gender and so we can click on open so we have done our source now we need to configure our transformation so our transformation is union all transformation so we can use union all drag and drop this union all transformation then we need to connect with male then female and then now double click on this union all as we can see output column names by ID name gender and gender we may not column then union all 2 then union all 3 so we are getting the inputs now we need to set up our destination so our destination is across our table so we have to use already be destination we can drag and drop this already be destination and connect then double click on this already be destination here we need to set up our already be correction manager so click on new once we click on new then we can see we have made the connection so I am going to use that one here we need to select our table so table name is union all table so we can select union all table now go to on mapping so inside this nothing nothing succeed so we need to click on okay once we click on okay then you want a system is now I am going to execute then we can see initially we don't have any data now I am going to execute these methods then we can see 19,951 rows should be inserted in our table now go to all SSMS and execute our selected statement 19,951 records have been inserted successful so next is what are the differences between union all and merge transformation so first is union all so data should be sorted order before applying much transformation in case of union all transformation there is no need to be sorted data before applying union all transformation next is output data could be sorted in case of our transformation and outcome data will not be sorted in case of union all transformation next is as we have discussed must transformation can accept only two inputs and in case of union all transformation we can use more than two inputs next is its bad for performance because it except only sorted data and in case of union all transformation it's good for the performance as compared to much transformation because there is no need to be sorted input so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos and press the bell icon to get the notification of our newly uploaded videos hello welcome to ss unity so see this side and this is continuation of ssis tutorial so today we want to discuss about aggregate transformation and what are the difference between sql aggregate and ssis aggregate so let's start with aggregate transformation so what is aggregate transformation so it is used to perform aggregate operations on groups in a data set like we have a data set and it contains many columns and we want to do aggregate by any specific column then we can use aggregate transformation to do the same thing the aggregate functions or label are count sum average so these are the functions available in sql server as well as in ssis aggregate transformation has one input and one or more than one output so let's move to our case study so this is our case study as we can see we have input table and it contains the information of phone number we have id phone type and phone number in id id 123 in phone type business phone number cell phone number phone number so we have three type of phone numbers and we want to do the aggregation on id and then phone type so first of all we want to do the aggregation on phone type second with the id let's move to sql server management studio here we can see we have the same metadata with this phone aggregate table and we want to do the aggregate by using aggregate transformation so as we can see first of all we want to do aggregation with the phone type so we can execute our first query then we can see business phone has three count cell phone again has three count and home phone has four second we want to do the aggregation with id then we can execute our second query here we can see id1 contain 2 id2 contain 3 and id3 contain 5 so we want to do this aggregation by ssis so let's move to ssdt then we need to create one more practice right click on this ssis practice create a new practice then we need to rename this practice with aggregate here we need to drag and drop our data flow task so we can drag and drop then we need to double click on this data flow task it will directly move to data flow panel inside data flow panel we need to configure our source transformation and destination so our source is oledb source so go to on other sources then we can drag and drop our oledb source now we need to configure our oledb source so double click on this oledb source oledb source editor window will be open now we need to configure our connection manager so click on new here we have already made the connection so delete this connection and create a new connection click on new here we need to select our server name so this is our server name i'm going to use window authentication so there is no need to enter user id and password here we need to select our database name so database is ssis test now we can check our connection connection succeed now click on okay so we have done with connection manager now we need to select the table so as we can see we have already typed the query so we can copy this query we need to change data access mode with simple command here we need to paste the query now go to on columns we want all the columns so click on okay so we have done our source now we need to configure our transformation so our transformation is aggregate transformation inside this common we need to drag and drop aggregate transformation and connect with source now we need to double click on this aggregate here we have the columns so first of all we want to do the aggregation with phone type we need to select the phone type and select phone number so here we can see we have input columns output areas then operation so we want do the aggregation with phone type so our first operation is okay in our second operation we want count so we can change with count now click on okay so we have done our aggregation with phone type now we need to set up our destination so we don't want to load data any destination we want to just check our data so we need to drag and drop multicast go to on aggregate connect with multicast here we need to enable our data where so enable data where now i'm going to execute this package so as we can see phone type b c and h 3 3 and 4 now move to sico server management studio and execute our first query so once we execute our first query then we can see we are getting the same metadata as we are expected now we can stop our package so here we can see it doesn't support any error output so here we can check we don't have any error output so aggregate transformation doesn't support error output next is so aggregate transformation has one output and in advanced mode we can configure more than one output each will contain different aggregation so first we have done with phone type second we want to do the aggregation with id so double click on this aggregate so here we can see we have this advance so click on this advance as we can see we have only one aggregate here we can configure our second aggregate so we can click with aggregate 2 and this is for id so here we need to configure our second so this time we want to do the aggregate with id and we want to count with phone number so here we can change this phone number click on okay now we need to drag and drop one more multicast and here we can see we have one more output so we can select so this is for id and this is for phone type so we need to enable data for id as well then we need to execute our package so as we can see this is for id and this is for phone type so we are getting the multiple aggregate at a single time so this is the difference between sico server and ssis in ssis we can do multiple aggregate at a single time but in sico server we cannot do the multiple aggregate at a single time we can also use aggregate to load unique data by applying group by two all columns so thank you so much for watching this video if you like this video please subscribe our channel to get many more videos thank you so much hey guys welcome to ssis video tech so see on this side and this is continuation of ssis tutorial so today we are going to discuss about row sampling and percentage sampling transformation so let's start so what is row sampling and what is percentage sampling so these transformations in ssis will take whole data from the source and it will randomly extract the selected number of rows from it next is it will return two outputs one from the selected data and other from the unselected data like we have 1000 rows in our table and we want to extract only five rows from that so we will return two outputs first we'll return only five rows and second we'll return 995 rows next is in row and percentage happening it will block the source table and write the random sample so it's fully blocking transformation and it is used only for the testing purpose and we have also an option to fix the random sample on every execution by checking the random seek option and this is useful if the source data is not already issued like we have flat file source and it has the millions of records and we want to extract only 1000 records to verify the data so there is no need to load that data in our table we can use a row sampling or percentage sampling transformation and then we can extract only selected data according to our requirement so next is what are the difference between row sampling and percentage sample in row sampling transformation we can set how many number of rows we want for sampling and in percentage sampling we can set the percentage of source data how many percentage number of rows we want for the sample so this is the only difference between row sampling and percentage sample so let's develop a practice where we can understand about row sampling and percentage sample so here we need to add one more practice so the common practice then we can rename this with the row sampling then we can use data flow task inside the control flow panel then double click on this data flow task so inside this data flow panel we can set our source transformation and destination so our source is already the source as we can see in our ssms we have a sampling table and it has 1000 records so we want to extract only 5 records from this table for our sampling let's configure our source double click on this already the source then we can set up our already the connection as we have already made the connection so I am going to use that one then we can select our table so this is our sampling table now go to one columns so we want to extract all the column so click on okay so we have done our source now we need to configure our transformation a row sampling transformation is available inside this other transformations so we can drag and drop this row sampling transformation and then we can connect with source once we connect with source then we can double click on this row sampling transformation here we can specify the number of rows so we want five rows so we can set up five here we can see sample output name this is our sample output name it will return only five rows second unselected output name so in unselected output name we will get 995 rows then go to one columns so we want all the column click on okay so we have done our row sampling now we need to configure our destination so we don't want to load data in any destination so we can use multicast test our data here as we can see we have two outputs so we want to see selected sampling output click on okay now we can use one more multicast and now we can configure our unselected output then we can enable data now we can execute our practice so selected sample we will get only five rows as we can see we are getting only five rows in our unselected output we are getting 995 rows on every execution we will get the different data as we can see so number two two three we are first and then we can execute one more time then we can check our data will be checked so our data has been changed now we can fix this output by selecting a random c function click on okay now I am going to execute our process so we can