{"id":1591,"date":"2011-02-28T01:27:31","date_gmt":"2011-02-27T19:57:31","guid":{"rendered":"http:\/\/JitendraZaa.com\/blog\/?p=1591"},"modified":"2011-02-28T01:27:31","modified_gmt":"2011-02-27T19:57:31","slug":"create-simple-etl-project-in-ssis-filter-records","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/create-simple-etl-project-in-ssis-filter-records\/","title":{"rendered":"Create Simple ETL Project in SSIS – Filter Records"},"content":{"rendered":"

In Previous article, we have seen the basics of SSIS<\/a>. In this article, we will create our first basic application of filtering data from csv file.<\/p>\n

As stated, Business intelligence tool is installed with SQL Server 2005 and best part is that it is integrated with Visual Studio 2005. So developer get the benefit of SSIS feature in Visual Studio power to work in disconnected\u00a0environment.<\/p>\n

In this article, we will read the csv file and get the required records and write back into another csv file. \u00a0csv file is attached in the source code in this article.<\/p>\n

To start the project, click new Project in Visual Studio and select “Business Intelligence Projects” and then “Integration Services Project” as shown in below figure.<\/p>\n

\"Create<\/a>
Create Business Intelligence Projects in Visual Studio<\/figcaption><\/figure>\n

After creating the BI (Business Intelligent) project, “package.dtsx” is created by default. “Package<\/strong>” is the term used in BI tool of SQL Server to represent the DTS project. one package can run many other packages as we have done in our project. the extension of the package is “dtsx<\/strong>“.<\/p>\n

Every package have four Tabs as shown in below image:<\/p>\n

\"Default<\/a>
Default Tabs in Package of BI Tool<\/figcaption><\/figure>\n
    \n
  1. Control Flow : <\/strong>Build and modify the control flow in Package.<\/li>\n
  2. Data Flow :<\/strong> Build and modify the data flow for the selected “Data Flow Task”.<\/li>\n
  3. Event Handlers : <\/strong>Build and modify the control flow foe the selected “Event Handler”.<\/li>\n
  4. Package Explorer : <\/strong>Displays the content of package in tree view.<\/li>\n<\/ol>\n

    In this project we will create only Data Flow Task.<\/p>\n

    Select tab “Control Flow” in package and from toolbox, select “Data Flow Task<\/strong>“.<\/p>\n

    Double click on Data Flow Task, Data Flow tab will open.<\/p>\n

    Now Drag the “Flat File Source<\/strong>” onto the “Data Flow” Panel.<\/p>\n

    Double click on “Flat File Source” and in “Connection Manager” option, select on “New” flat file connection Manager as shown in below image.<\/p>\n

    \"Flat<\/a>
    Flat File Source New Connection in SSIS<\/figcaption><\/figure>\n

    Select the source file from which we have to extract the record and click on the OK button. Now we have made the source connection\u00a0successfully. You may also have noticed that one connection is created in connection manager panel located at the bottom of the Visual studio\u00a0environment. Select the checkbox saying that “Column names in the first data row” <\/strong>if the first row is column name in csv file.<\/p>\n

    Now we have the source file, to extract the required records, select “Conditional Split<\/strong>” from the toolbox.<\/p>\n

    Select “Flat File Source” and drag the Green arrow to the “Conditional Split” tool. Green arrow represents the next step to be\u00a0executed\u00a0after successful execution of current block and Red arrow represents the next step to be executed after operation fail or error occurs (Can be used as exception handling).<\/strong><\/p>\n

    <\/strong><\/p>\n

    \"Success<\/a><\/strong>
    Success and error steps in SSIS<\/figcaption><\/figure>\n

    Now, Double click on “Conditional Split”, “Transformation editor” will open. Select Column from columns list when cursor is in Condition textbox \u00a0and write College == “UCOE”<\/strong>. This means get only those rows in which the college columns value is “UCOE”.<\/p>\n

    \"Conditional<\/a>
    Conditional Split Transformation Editor<\/figcaption><\/figure>\n

    Now select the “Flat File Destination<\/strong>” from the toolbox and drag the green arrow from “Condition Split” to this control. When you will drag the arrow from “Conditional Split” to “Flat File Destination” one popup will open to ask for output and input as shown in below image:<\/p>\n

    \"Input<\/a>
    Input Output Selection in Conditional Split<\/figcaption><\/figure>\n

    As shown in image, we have selected the Output named “UCOEStudents” which we had specified in above step and input is provided to the “Flat File Destination”<\/p>\n

    Now double click on “Flat File Destination” and in “File Connection Manager”, click on New.<\/p>\n

    It will ask for the flat file format, select delimited and provide the path where file should be saved. In current project, i have provided the file name as “Output”.<\/p>\n

    We can also add the “Data Viewer”<\/strong> after Conditional split, (Right click on Green arrow and select the Data Viewers) which is useful while debugging the application.<\/p>\n

    Press F5 or click on run, one new file will be created which contains all the records in which college name is “UCOE”. In case you have provided any “Data Viewer”, then popup will appear displaying the results.<\/p>\n

    Download source code from here<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    Creating First ETL (Extract, Transform and Load) project in SSIS (SQL Server Integration Services) – Filter Records<\/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_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":"","jetpack_post_was_ever_published":false},"categories":[15],"tags":[91],"class_list":["post-1591","post","type-post","status-publish","format-standard","hentry","category-sqlserverintegrationservices","tag-etl"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1619,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserverintegrationservices\/merge-two-data-in-etl-project-of-ssis\/","url_meta":{"origin":1591,"position":0},"title":"Merge Two Data in ETL project of SSIS","author":"Jitendra","date":"March 10, 2011","format":false,"excerpt":"Example of Merging two data in ETL project of SSIS","rel":"","context":"In "SSIS"","block_context":{"text":"SSIS","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserverintegrationservices\/"},"img":{"alt_text":"Merge Two Data in ETL project of SSIS","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2011\/03\/Merge-Two-Data-in-ETL-project-of-SSIS.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":1591,"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":1591,"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":4431,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/using-dataloader-and-ant-to-backup-salesforce-data-on-ftp-server-video\/","url_meta":{"origin":1591,"position":3},"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":5152,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/using-test-loaddata-to-import-records-with-relationship\/","url_meta":{"origin":1591,"position":4},"title":"Using Test.loadData to import records with relationship","author":"Jitendra","date":"January 6, 2016","format":false,"excerpt":"There are many resources and documents available around how to use Test.loadData to create test records in Apex class. As per best practice of writing Test classes in Apex, Its good idea to store master data (aka Seed, Reference data) in static resource and load\u00a0it in Test classes using \"Test.loadData\"\u2026","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"Test.loadData and Static resource in Salesforce","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/01\/Test.loadData-and-Static-resource-in-Salesforce.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/01\/Test.loadData-and-Static-resource-in-Salesforce.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/01\/Test.loadData-and-Static-resource-in-Salesforce.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/01\/Test.loadData-and-Static-resource-in-Salesforce.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":5835,"url":"https:\/\/www.jitendrazaa.com\/blog\/salesforce\/considerations-and-best-practices-for-salesforce-dataloading\/","url_meta":{"origin":1591,"position":5},"title":"Considerations and Best practices for Salesforce Dataloading","author":"Jitendra","date":"January 29, 2018","format":false,"excerpt":"Although we are doing data loading in Salesforce from ages, its very common to miss some steps or considerations. Therefore thought to list some of best practices and considerations I follow before or during data load process. Developer note field - I mostly create a text field on every object\u2026","rel":"","context":"In "Salesforce"","block_context":{"text":"Salesforce","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/salesforce\/"},"img":{"alt_text":"Salesforce Dataloader","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2018\/01\/Salesforce-Dataloader.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\/1591","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=1591"}],"version-history":[{"count":0,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/1591\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=1591"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=1591"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=1591"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}