 Hello, welcome to SSUniTech Society's side and this is continuation of SSIS interview questions and answers. So today we are going to see one more real-time SSIS case study and interview question. So before going forward, if you haven't watched SSIS tutorial part 3, 8, 13, 22 and 50, then I would strongly recommend to watch all these videos before going forward. I will provide the link of these videos in the description of this video. So you can directly watch those videos from here. So go to the next slide and we will see about the case study. So here as you could see, like first we need to get the data from the CSV file and loading that data into the SQL Server table and after loading the data into the table, we need to move the source that is the CSV file to the archive folder and parallelly we need to match the data that we have loaded in the table with the other table. If that is going to match, then we need to move the matching data into the Excel file from the SQL Server. So this is the scenario that we need to do. So first go to on the Excel file for the source data. We are having the data for the business entity ID, national ID number, login ID, job title, date of birth. So these are the employee related information that we have in the source. Now go to on the SSGT and we'll try to implement the package over there. So this is the SSGT and here we don't have anything as of now. So what we need to do, we need to design the package, but that package should be dynamic. Like our source will be the dynamic and your destination will be the dynamic. So at the runtime we can make the changes. Let's get started with the design of the package. So first like we need to load the data from the CSV file to the SQL Server table. So first we need to use the Dataflow task. So let me try to drag and drop the Dataflow task. Let me rename this Dataflow task as load data from CSV to SQL Server table. Now let me double click on this and it will directly jump to the Dataflow panel. So inside the Dataflow panel we could see the source and transformation along with the destination. So here our source is the CSV file. So we can go in the other sources and here we could see the flat file source. So we can drag and drop the flat file source right here. We need to load the data into the SQL Server table. So for that we need to use the OLEDB destination. So we can drag and drop that. So here we require two connection managers first for the source and second for the destination. But that should be dynamic we could say. So for the source as we can go inside the folder. So this is the source folder. So for that we can create the package level parameter. So we can directly go in the project.params and here we need to add a variable for the parameter. So inside the parameter let me call this as source file path and the data type that should be string and the value that we can directly copy from here and provide that right there. Now so we are okay with the source connection. Now go to on the package let me double click on this source and let me create the connection for the first time. So we can click on the new and it will open a window. So first here we need to choose the file name. So we can browse. So here instead of the text file we need to select the CSV file. So here is the source we can click on open. Now go to on the columns. So inside the columns so everything looks good. We can directly click on okay. So we can check this checkbox as return null values from the source as null values in the data flow. Now go to on the columns. So we require to have all these column. Now we can click on okay. So here this connection manager is the static. So every time it is going to pick that path. Now we can click on that and we can directly go inside the property window and here we should see the option for the expression. So we can click on that and here we are having a property with the name of connection string. So we can go on the connection string and inside the expression we can use the project level parameter that we have declared. We can evaluate everything looks good. We can click on okay and again okay. So we are done with the flat file source. Now we need to connect this with the destination. So our destination is the OLDB destination. So go to on the sequence over and here we need to load the data on this employee data table. Let me try to execute this and here we could see we are having the columns like business entity ID, national ID number login ID along with all these columns. So on this table we want to load the data. So go to on the SSDT and here first we need to create the connection manager. So the connection managers go to on this solution explorer here we could see the connection managers. Right click on that and click on the new connection manager. So here we want to create the connection manager for the OLDB. So here we should see the option for the OLDB connection manager. We can click on add and here I have already created the connection for the Adventure Works 2K14 but let me try to delete that and click on new. Here we need to specify the server name. So for that we could use this query like select add the red server name. Let me try to complete this go to on the SSDT and paste that. So here I am going to use the windows authentication. You can use the use sequence of authentication. So as per access you can use those. Now we can see this database option. So I want to connect with the Adventure Works 2K14. We can test the connection everything succeed we can click on OK and again OK. So the connection manager that we have created which is the project level connection manager. Not double click on this OLDB destination and connection manager is already selected. So we are good with that. Inside the names of the tables or views we need to select the employee data table and go to on the mapping. So inside the mapping everything looks good and we can click on OK. So now we have done with the loading data from the CSV file to the SQL Server table. So once that part is done then we need to move the source file to the archive folder. So for that we need to use the file system task. So we can drag and drop the file system task here. And we need to use the one more data flow task and on that data flow task we need to match the source data that is we have already loaded into the SQL Server table that is employee data with the existing data table. So that we will do later first I am going to consider this flat file task and parallelly we can connect with the data flow task. Now let me double click on this file system task and here what we need to do. We need to move the file from source to the destination. So inside the source we already have a project level parameter. So we can select that. So this is our source but inside the destination like on which folder we want to move that file that we need to declare either we can specify the static value by creating the connection or we can declare a variable. So I am going to declare a variable and that should be the project level parameter. Let me click on OK go to on the project level parameter. Let me add a new parameter and this is for the archive folder path and this value should be string and here we need to specify the value. So here you could see this is the archive one. So let me try to copy this and go back to here and paste that value and here we can use backslash like that. Now we can save this now go to on the package again and let me double click on this file system task here we can use as a variable so we can select as true and in the destination we need to select this folder path and if is already available then we can replace or overwrite that file. So we are OK with that now we can click on OK. So the movement of the file has been done now we need to consider this data flow task. So let me double click on that and here first we need to get the data from the OLEDB source. So for the OLEDB source we can drag and drop the OLEDB source and second we want to match that data with one of the existing table. So for that we need to use the lookup transformation. So once data is going to match then we need to load that data into the excel. So for that we need to use the excel destination but if we go inside the sequence over and check the data type inside the employee data table. So here you could see we are having the wire card data type. So it is not going to support the Unicode characters but while we are going to move the data from any source to excel so that should be the Unicode characters. So for that what we need to do we need to use the data conversion so that we need to drag and drop in between lookup and excel destination. So now everything is OK now we need to configure all these. So for the OLEDB source our connection manager which we have created for the movement of the data from the CSV file to the destination of the sequence over. So that can be utilized over here as well. So let me do a click on this OLEDB source connection manager is selected automatically. Now we need to select the table. So our table is the employee data table where we have loaded the data put on the columns. So we require to have all these columns. Now we can click on OK. Now we need to put that data pipeline with the lookup transformation now go to inside the lookup transformation and inside the lookup transformation I am OK with the full cassette so our reference or the lookup that is inside the sequence over table. So we can use the OLEDB connection manager. So first we look about that table. So here we can go and this is our table which is the human resource employee table we can execute and here we are having all these values now go to on the SSDT again and here go to on the connections. So connection that we have created for the connection manager is automatically selected for the table we need to select the human resource employee table go to on the columns here we need to do the lookup on which column so that is the business entity ID so we can drag and drop from your source to our label lookup columns. So everything looks good and we can click on OK. So let me go in the lookup again and here you can see specify how to handle the rows with no match entries. So in the no match entries we can use the ignore failure or we can redirect rows to the no match output we can click on OK. Now let me connect this data pipeline with the data conversion and here we only consider lookup match output let me click on OK here double click on this and first let me select all these. So here you could see we are having national ID number which is string value login ID string value job title string value material status string value gender string value. So only these columns are having string values. So we need to convert all these from non-unicode to the unicode. So let me unselect business entity ID and burst date after that higher date then all these columns. So here convert this with the unicode string unicode string unicode string unicode string unicode string now we can click on OK. Let me connect this with the Excel destination. So as of now we have not created any Excel destination go to on the folder and here we have this final. So inside the final XYZ.xls so this is the Excel file where we want to dump the data. So having all these columns right here. So let me use this and go back to here. Let me try to copy this go back to SSGT. So for making this as dynamic again we need to go inside the project parameters. Let me add a parameter call this as Excel destination. This would be string press the value right here save this and close that double click on this Excel destination and here first we need to create this Excel connection manager. So click on new we can browse and inside the final we can select that and click on open click on OK. In the Excel seat we want to load the data into the Excel destination go to on the mapping. So inside the mapping everything is OK. But if you have remember we are having the national ID number. So national ID number should be the copy of the national ID number because national ID number containing non-unicode characters and for the login ID again for the job title for the material status for the gender. So we are OK with all these now let me click on OK. So Excel destination is also configured successfully but as of now it is static. So go to on the Excel connection manager and go to on the properties of this and here we should have the option for the expression. So click on that here we should see the option Excel file path. So this property we need to make as dynamic so go to on the expression and here let me try to drag and drop this Excel destination evaluate and click on OK and OK. So everything looks good only we need to execute the package and we'll see the output that we are expecting. So first go to on the folder. So data should be available on this Excel file go to on the source. This source file should be going to move inside the archive and data should be going to loaded on this MP data table. So this should be done while we are going to execute the package. So go to on the SSD and here let me click on the start. So package should be executed successfully. So as you can see this source file has been moved from this to the archive folder. So that part is OK. Now go to on the source and inside the final we are having this Excel file. So the data which is matching with the existing table should be available on this Excel file. So that you could also see. So that looks good. Now go to on the SQL server and in this employee data table we should have the data. Those are available inside the source. Let me try to execute this. So this should have the data as you can see. So now go to on the SSD team. Let me try to stop this package. So this is let me try to recap this. So first in this data flow task we are going to load the data from the CSV file to the SQL server table. And after loading this we are going to move the source to the destination of that file by using this file system task. And in the second data flow task here we are going to match the loaded data with the existing tables data. If we are going to found matching the data then moving that data into the Excel file and parallely we have done the file system task as well. Now if you want then you can deploy this package and at the runtime you can change the values of all these project level parameters and the connection managers that we have created. So that you can do on your own and if you will face any issue then you can drop the comments on the comment section of this video. So I will try to response on your comments. So thank you so much for watching this video. If you really like this video please subscribe our channel to get many more videos. Don't forget to press the bell icon to get the notification of our newly uploaded videos. See you in the next video with a new interview question and answers.