![]() ![]() Since we are loading from a source file to make sure you have assigned proper data-type to it. To know about configuration step by step read this article - Data Conversion. Since our source file is Flat File so we will use Flat File Source component if you want you can use different modes like Excel and so on.įor now just drag and drop Flat File Source Component from SSIS toolbox and configure it. If you are new to MSBI request you to read all our previous article click to navigate. Once done just drag and drop Data Flow task from toolbox and double click on it. Open up MSBI studio and create SSIS project. Let's create our source file here for this example we will use flat file source and add up some dummy data as shown in below image. Here in this step we will create CustomerMaster table with columns : CustomerID, CustomerName, CustomerAmount, CustomerAddress, CustomerCountryID, CustomerISActive respectively in SQL management studio as shown in below image. In this step we will go to our SQL management studio and create country master table with columns (CountryID, CountryName) respectively.After that we will some country names to this table.Īs you saw in above images we have successfully created country master table and added some country names to it. ![]() So let's do this complete example step by step #Download sample ssis package updateOn matching key values we will replace with country name and same we will update it to destination table. ![]() When we load data from source file which contains "Customer Records with country name" before it reaches to destination table (Customer Table) in between we will apply LookUp component to compare source records with existing Country Table and filters matching ones and un-matching ones. We will add up some country names to this master table. We will also create master table for Country List name it as Country with columns : CountryId, CountryName. So we will take up a Customer Table with columns : CustomerID, CustomerName, CustomerAmount, CustomerAddress, CustomerCountryID, CustomerISActive. Here we will do a simple example to make you understand quickly how LookUp works. Now let's understand same component using real time example that we will do step by step. ![]() Hope you have understood about #LookUp component. So to get this foreign key value we need to use #LookUp component which compares source records with City master table to get matching key values and same can be updated to Customer table. Now if you see in the destination Customer Table we have CustomerCityID which is foreign key and here in the source file we have string type value and for proper insert we need its Foreign key value. And let's say you have a sample source data in this format : "1001 as CustomerID", "Shaam as CustomerName", "R-no 202 - mulund naka as CustomerAddress" & "Mumbai as CustomerCity". It compares source data with existing table dataset and filters matching ones and un-matching ones.įor example let's say you have customer table with columns CusomerID, CustomerName, CustomerAddress,CustomerCityID where CusomerID is a primary key and CustomerCityID foreign key for City Table. LookUp is very useful transformation SSIS component it performs lookup operation by connecting input value with data-table or table dataset columns. #Download sample ssis package freeNET training (AngularJS, MVC, ASP.NET, MSBI, Sharepoint and many more) feel free to touch base with us.Ĭoming back to article in today's session we will learn and understand SSIS lookup using an example step by step. #quetpond + ME we love to deliver quality training and till now we succeeded and want to continue more same in coming future session. I love working with this company simply because it only cares about quality teaching, videos, books, online subscription. ShivPrasad Koirala with a vision to spread tech knowledge. Little About my Company : #Questpond is an e-learning started by Mr. I'm your host Gurunatha.D a simple enthusiastic developer hunt to learn new technologies, learning and working is my passion that i do daily. Hello Friends, #MSBI Learners, welcome to step by step msbi tutorial. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |