{"id":636,"date":"2014-03-11T22:49:05","date_gmt":"2014-03-12T03:49:05","guid":{"rendered":"http:\/\/www.cognitiveinfo.com\/?p=636"},"modified":"2014-03-11T22:49:05","modified_gmt":"2014-03-12T03:49:05","slug":"ssis-sort-ole-db-source","status":"publish","type":"post","link":"http:\/\/cognitiveinfo.com\/?p=636","title":{"rendered":"How to set OLE DB Source data in SSIS"},"content":{"rendered":"

Sometimes I have to learn things repeatedly before it sticks.\u00a0 I re-learned this tip from Tim Costello at last night’s Oklahoma City SQL User Group<\/a> presentation.\u00a0 We will get his presentation up soon.<\/p>\n

First, a big thank you to Tim Costello<\/a> for coming to Oklahoma City to speak at our user group.\u00a0 Check out his site for more tips.\u00a0 This tip was just one tidbit from his presentation “Pro Tips: Tuning the data flow in SSIS”.\u00a0\u00a0 Watch for him at a SQL Saturday<\/a> near you.<\/p>\n

 <\/p>\n

Why you need to set IsSorted property on the OLE DB Source<\/h2>\n

When preparing data for a Merge Transformation, the data has to be sorted.\u00a0 The database sending the data can usually sort data faster than SSIS.\u00a0 This works much faster if you already have data sorted in the table, but also if you use an ORDER BY clause.<\/p>\n

The IsSorted<\/strong> property in the OLE DB Source informs all the downstream transformations that that data from a given data source is coming in already sorted.\u00a0 I used merge joins at a project over a year ago and we really had to dig around to find these properties and get the data flow working.\u00a0 I thought I better write it down on something besides post-it notes before it pops out of my head again.
\n<\/em><\/p>\n

NOTE:\u00a0<\/strong> The properties listed below do not sort the data.\u00a0\u00a0 See TechNet article listed below for more caveats to these properties.<\/em><\/p>\n

How to set the IsSorted property on an OLE DB Source:<\/h2>\n
    \n
  1. Start with an OLE DB Source and write a query using an ORDER BY statement to sort the data.<\/li>\n
  2. Close the OLE DB Source.<\/li>\n
  3. On the OLE DB Source, Right Click and select to the ‘Advanced Editor’.<\/li>\n
  4. \"SSIS<\/a>Click on the ‘Inputs & Output Properties’ tab.<\/li>\n
  5. Set the ‘Data Sorted’ property to ‘True’.\"SSIS<\/a><\/li>\n
  6. Next we have to tell all downstream transformations how the data is sorted.<\/li>\n
  7. Expand the plus sign ‘+’ by the ‘Input Columns’<\/li>\n
  8. For each column, set the SortKeyPosition property.
    \nIt needs both a rank and a direction.
    \nUse an integer to indicate the order of the list in your ORDER BY clause.
    \n1 for first sort column, then 2, 3, etc…
    \nTo set the sort direction, add a plus sign ‘+’ for Ascending or a minus ‘-‘ for Descending.<\/li>\n
  9. Repeat for the ‘Output Columns’.\u00a0 Use zero ‘0’ for output columns which are not part of the ORDER BY.
    \nFor example, if the BusinessEntityID column is the first column sorted descending, we use a -1 as shown below.<\/li>\n

    \"SSIS<\/a>\n<\/ol>\n

    Well, that is a quick photo tutorial on how to set the sort properties for an OLE DB Source in an SSIS package.\u00a0 I hope this is as helpful to you as it was for me.\u00a0 Again, thanks to Tim Costello.<\/p>\n

    Reference: http:\/\/technet.microsoft.com\/en-us\/library\/ms137653.aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    When preparing data for a Merge Transformation, the data has to be sorted. The database sending the data can sort data faster than SSIS. This works if you already have data sorted in the table or if you use an ORDER BY clause. The IsSorted properties just inform sall the downstream transformations that that data from this data source is coming in already sorted. <\/p>\n","protected":false},"author":2,"featured_media":640,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14],"tags":[64,75,91,101],"class_list":["post-636","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-ssis","tag-microsoft-sql","tag-properties","tag-sql","tag-ssis"],"_links":{"self":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/636","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=636"}],"version-history":[{"count":0,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/wp\/v2\/posts\/636\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=\/"}],"wp:attachment":[{"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=636"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/cognitiveinfo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}