{"id":3792,"date":"2021-11-26T09:43:03","date_gmt":"2021-11-26T17:43:03","guid":{"rendered":"https:\/\/SUMMALAI.COM\/?p=3792"},"modified":"2021-11-26T09:43:05","modified_gmt":"2021-11-26T17:43:05","slug":"how-to-download-odata-source-to-sql-or-a-txt-file-in-ssis","status":"publish","type":"post","link":"https:\/\/SUMMALAI.COM\/?p=3792","title":{"rendered":"How to Download OData Source to SQL or a TXT File in SSIS"},"content":{"rendered":"\n

Applies to:<\/strong> \"yes\"SQL Server (all supported versions) \"yes\" SSIS Integration Runtime in Azure Data Factory<\/p>\n\n\n\n

This tutorial walks you through the process to extract the\u00a0Employees<\/strong>\u00a0collection from the sample\u00a0Northwind<\/strong>\u00a0OData service (https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/<\/a>), and then load it into a flat-file.<\/p>\n\n\n\n

<\/a>1. Create an Integration Services project<\/h2>\n\n\n\n
  1. Launch SQL Server Data Tools<\/strong> or Visual Studio.<\/li>
  2. Click File<\/strong>, point to New<\/strong>, and click Project<\/strong>.<\/li>
  3. In the New Project<\/strong> dialog box, expand Installed<\/strong>, expand Templates<\/strong>, expand Business Intelligence<\/strong>, and click Integration Services<\/strong>.<\/li>
  4. Select Integration Services Project<\/strong> for the type of project.<\/li>
  5. Enter a name<\/strong> and select a location<\/strong> for the project, and click OK<\/strong>.<\/li><\/ol>\n\n\n\n

    <\/a>2. Add and configure an OData Source<\/h2>\n\n\n\n
    1. Drag-drop a Data Flow Task<\/strong> from the SSIS Toolbox<\/strong> on to the control flow design surface of your SSIS package.<\/li>
    2. Click the Data Flow<\/strong> tab, or double-click on the Data Flow Task<\/strong> to open the Data Flow design surface.<\/li>
    3. Drag-drop OData Source<\/strong> from the Common<\/strong> group in the SSIS Toolbox<\/strong>.<\/li>
    4. Double-click the OData Source<\/strong> component to launch the OData Source Editor<\/strong> dialog box.<\/li>
    5. Click New…<\/strong> to add a new OData Connection Manager.<\/li>
    6. Enter the OData service URL for Service document location<\/strong>. This URL can be the URL to the service document, or to a specific feed or entity. For the purpose of this tutorial, enter the URL to the service document: https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/<\/a>.<\/li>
    7. Confirm that Windows Authentication<\/strong> is selected for the authentication<\/strong> to use to access the OData Service. Windows Authentication<\/strong> is selected by default.<\/li>
    8. Click Test Connection<\/strong> to test the connection, and click OK<\/strong> to finish creating an instance of OData Connection Manager.<\/li>
    9. In the OData Source Editor<\/strong> Dialog Box, confirm that Collection<\/strong> is selected for Use collection on resource path<\/strong> option.<\/li>
    10. From the Collection<\/strong> drop-down list, select Employees<\/strong>.<\/li>
    11. Enter any additional OData query options or filters for Query Options<\/strong>. For example, $orderby=CompanyName&$top=100<\/code>. For the purpose of this tutorial, enter $top=5<\/code>.<\/li>
    12. Click Preview<\/strong> to preview the data.<\/li>
    13. Click Columns<\/strong> in the left navigation pane to switch to the Columns<\/strong> page.<\/li>
    14. Select EmployeeID<\/strong>, FirstName<\/strong>, and LastName<\/strong> from Available External Columns<\/strong> by checking the check boxes.<\/li>
    15. Click OK<\/strong> to close the OData Source Editor<\/strong> dialog box.<\/li><\/ol>\n\n\n\n

      <\/a>3. Add and configure a Flat File Destination<\/h2>\n\n\n\n
      1. Now, drag-drop a Flat File Destination<\/strong> from SSIS Toolbox<\/strong> to the Data Flow design surface below the OData Source<\/strong> component.<\/li>
      2. Connect OData Source<\/strong> component with the Flat File Destination<\/strong> component using blue arrow.<\/li>
      3. Double-click on Flat File Destination<\/strong>. You should see the Flat File Destination Editor<\/strong> dialog box.<\/li>
      4. In the Flat File Destination Editor<\/strong> dialog box, click New<\/strong> to create a new flat file connection manager.<\/li>
      5. In the Flat File Format<\/strong> dialog box, select Delimited<\/strong>. Then you see the Flat File Connection Manager Editor<\/strong> dialog box.<\/li>
      6. In the Flat File Connection Manager Editor<\/strong> dialog box, for the File name<\/strong>, enter c:\\Employees.txt<\/code>.<\/li>
      7. In the left navigation pane, click Columns<\/strong>. You can preview the data on this page.<\/li>
      8. Click OK to close the Flat File Connection Manager<\/strong> Editor dialog box.<\/li>
      9. In the Flat File Destination Editor<\/strong> dialog box, click Mappings<\/strong> in the left navigation pane. Review the mappings.<\/li>
      10. Click OK to close the Flat File Destination Editor<\/strong> dialog box.<\/li><\/ol>\n\n\n\n

        <\/a>4. Run the package<\/h2>\n\n\n\n

        Run the SSIS package. Verify that the output file is created with ID, First Name, and Last Name for five employees from the OData feed.<\/p>\n\n\n\n

        Ref: https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/data-flow\/tutorial-using-the-odata-source?view=sql-server-ver15<\/p>\n","protected":false},"excerpt":{"rendered":"

        Applies to: SQL Server (all supported versions)  SSIS Integration Runtime in Azure Data Factory This tutorial walks you through the process to extract the\u00a0Employees\u00a0collection from the sample\u00a0Northwind\u00a0OData service (https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/), and then load it into a flat-file. 1. Create an Integration Services project Launch SQL Server Data Tools or Visual Studio. Click File, point to New, and click Project. In the New Project dialog box, Read More<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[10,625],"tags":[957,958],"class_list":["post-3792","post","type-post","status-publish","format-standard","hentry","category-microsoft","category-sql","tag-odata-source-to-sql","tag-odata-source-to-txt-file"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3792","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3792"}],"version-history":[{"count":1,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3792\/revisions"}],"predecessor-version":[{"id":3793,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=\/wp\/v2\/posts\/3792\/revisions\/3793"}],"wp:attachment":[{"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3792"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3792"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/SUMMALAI.COM\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3792"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}