{"id":1619,"date":"2011-03-10T23:39:44","date_gmt":"2011-03-10T18:09:44","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1619"},"modified":"2011-03-10T23:39:44","modified_gmt":"2011-03-10T18:09:44","slug":"merge-two-data-in-etl-project-of-ssis","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/merge-two-data-in-etl-project-of-ssis\/","title":{"rendered":"Merge Two Data in ETL project of SSIS"},"content":{"rendered":"

Before reading this tutorial, please go through the first article on creating ETL project in Business\u00a0Intelligent\u00a0\u00a0(BI) tool of visual studio for SSIS<\/a>.<\/p>\n

For this tutorial we will need two data sources. I am taking one flat file and another excel file so that new user can understand that extraction can be done from different types of datasources.<\/p>\n

Text file contains “CustomerId, Subscription Start Date, Subscription End Date” and Excel file Contains “CustomerId, Segment, CustomerIdSpace” columns.<\/p>\n

Observe that both data sources contains one common column named “CustomerId”. \u00a0For a while don’t count the column “CustomerIdSpace” of Excel Data Source.<\/p>\n

Create New Business Intelligent project in Visual Studio 2005 and Drag “Data Flow Task<\/strong>” from tools to package.<\/p>\n

Double click on “Data Flow Task” and new tab will opened. Drag “Flat File Source<\/strong>” and “Excel source<\/strong>” from tool box.<\/p>\n

Double click on “Flat File Source” and “Excel Source” and create new Connection for excel file and text file respectively.<\/p>\n

Drag two “Sort<\/strong>” control from “Data Flow Transformation” section of the toolbox below both sources.<\/p>\n

Double click on “Sort” control and select the column “CustomerId” for both “Sort” Control.<\/p>\n

Now Drag the “Merge Join<\/strong>” control from tool box and drop green arrow from both “Sort” Control to the “Merge Join” control. Select the type of Join, in this case we have selected “inner join<\/strong>“, also select the columns which should be exported in Output .<\/p>\n

Here, we are not going to write the result in file, but we will use “derived column<\/strong>” control after merge join and add “data Viewer<\/strong>” as discussed in previous article to view the output.<\/p>\n

The final snapshot of the ETL package will look like:<\/p>\n

\"Merge<\/a>
Merge Two Data in ETL project of SSIS<\/figcaption><\/figure>\n

Merge Data from different sources in which the common column is not well formatted:<\/strong><\/p>\n

In above example, we have considered that “CustomerId” in both sources have same value. But what will happen if the column is not same and needs some modification. for example if one source have extra space in values of column “CustomerId”.<\/p>\n

In Excel file we have one column named “CustomerIdSpace” which i said to forget in previous section.<\/p>\n

To work in this type of situation, before sorting data we will need to change\/format the\u00a0inconsistent\u00a0column. here we will need “Derived Coulmn<\/strong>” control from the toolbox.<\/p>\n

\"Derived<\/a>
Derived Coulmn Transformation - Trim Column Values<\/figcaption><\/figure>\n

As you can see in formula editor of derived column , one new column is added named as “Removed Id” and the value is calculated by expression TRIM<\/strong>(CustomerIdSpace).<\/p>\n

The Final snapshot of the package is shown in below image:<\/p>\n

\"Merge<\/a>
Merge Two InConsistent Data in ETL project of SSIS<\/figcaption><\/figure>\n

Download source code for Merge Two Data in ETL project of SSIS <\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

Example of Merging two data in ETL project of SSIS<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[15],"tags":[91,334],"class_list":["post-1619","post","type-post","status-publish","format-standard","hentry","category-sqlserverintegrationservices","tag-etl","tag-sqlserverintegrationservices"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1591,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/create-simple-etl-project-in-ssis-filter-records\/","url_meta":{"origin":1619,"position":0},"title":"Create Simple ETL Project in SSIS – Filter Records","author":"Jitendra","date":"February 28, 2011","format":false,"excerpt":"Creating First ETL (Extract, Transform and Load) project in SSIS (SQL Server Integration Services) - Filter Records","rel":"","context":"In "SSIS"","block_context":{"text":"SSIS","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserverintegrationservices\/"},"img":{"alt_text":"Create Business Intelligence Projects in Visual Studio","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/02\/New-Business-Intelligence-Projects.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1359,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/etl-dts-and-ssis-introduction\/","url_meta":{"origin":1619,"position":1},"title":"ETL , DTS and SSIS Introduction","author":"Jitendra","date":"December 4, 2010","format":false,"excerpt":"Introduction to ETL Services, Data transformation services, SQL Server Integration Services, Advantages of SSIS over DTS, New features of SSIS 2008","rel":"","context":"In "SSIS"","block_context":{"text":"SSIS","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserverintegrationservices\/"},"img":{"alt_text":"What is ETL Extraction Transformation Loading","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/What-is-ETL-Extraction-Transformation-Loading.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":6449,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/salesforce-integration-with-sql-server-integration-services\/","url_meta":{"origin":1619,"position":2},"title":"Salesforce Integration with SQL Server Integration Services","author":"Jitendra","date":"April 15, 2018","format":false,"excerpt":"Video - Connecting SQL Server Integration Services (SSIS) with Salesforce using COZYROC Connector","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/img.youtube.com\/vi\/RA5azDuubU4\/0.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":6451,"url":"https:\/\/www.jitendrazaa.com\/blog\/others\/definition-of-frequently-used-database-architecture-related-terms\/","url_meta":{"origin":1619,"position":3},"title":"Definition of Frequently Used Database Architecture Related Terms","author":"Jitendra","date":"December 10, 2017","format":false,"excerpt":"Definitions of Data warehouse, Data lake, Data Mart, Operational Data Store","rel":"","context":"In "Others"","block_context":{"text":"Others","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/others\/"},"img":{"alt_text":"Data warehouse overview (From Wikipedia)","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/04\/Data_warehouse_overview.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":4431,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/using-dataloader-and-ant-to-backup-salesforce-data-on-ftp-server-video\/","url_meta":{"origin":1619,"position":4},"title":"Using Dataloader and ANT to backup Salesforce data on FTP or SFTP server – Video","author":"Jitendra","date":"May 4, 2015","format":false,"excerpt":"Tutorial on how to use command line dataloader to save exported Salesforce backup file on FTP or SFTP server","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"Use Dataloader and ANT to back CSV file on FTP Server","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/05\/Use-Dataloader-and-ANT-to-back-CSV-file-on-FTP-Server-1024x391.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/05\/Use-Dataloader-and-ANT-to-back-CSV-file-on-FTP-Server-1024x391.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/05\/Use-Dataloader-and-ANT-to-back-CSV-file-on-FTP-Server-1024x391.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":5094,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/10-salesforce-integration-design-considerations-from-architect-point-of-view-mind-mapping-included\/","url_meta":{"origin":1619,"position":5},"title":"10 Salesforce Integration design considerations from Architect point of view – Mind Mapping included","author":"Jitendra","date":"December 21, 2015","format":false,"excerpt":"After working on multiple Salesforce implementation project as an Architect, its time to share\u00a0what I learned from those implementations and would strongly suggest to be considered before designing any \"Salesforce Integration\". Below image shows \"integration mind mapping\" used by me. I use it to consider some major aspects\u00a0while discussing integration\u2026","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"Salesforce Integration Mind mapping diagram","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/12\/Salesforce-Integration-Mind-mapping-diagram.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/12\/Salesforce-Integration-Mind-mapping-diagram.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/12\/Salesforce-Integration-Mind-mapping-diagram.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2015\/12\/Salesforce-Integration-Mind-mapping-diagram.png?resize=700%2C400&ssl=1 2x"},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1619","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=1619"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1619\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1619"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1619"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1619"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}