2

I have an Execute SQL Task placed before a Foreach Loop Container so that for every row returned by the Full Result Set a Script Tasks Inserts this into an Excel row.

However, on one particular column I would like to sort the Full Result Set in ASC order before iterating though each row so that I can have the Full Result Set in a particular order.

I've tried using the Order By Clause in the SQL Script inside the Execute SQL Task but it doesn't sort as expected.

Is there a way I can sort the query results in the Result Set object in order by a single column before passing onto another process i.e. the Script Task?

Shaye
  • 179
  • 13
  • 1
    What's the reason for Execute SQL Task -> FELC => Script Task to Excel versus a Dataflow Task with OLE DB Source -> Excel Destination? – billinkc Sep 30 '19 at 15:27
  • I agree with @billinkc. Using a dataflow is what SSIS is made for. If you are trying to make an Excel Report. I would suggest using SSRS – KeithL Sep 30 '19 at 15:53
  • 1
    Hi billinkc...I can't use DB Source to Excel Destination because I need to insert the rows from the result set object returned by Execute SQL Task not to the first row but to row 7 onward. Also I need to insert some specific values in certain ranges in the header of the spreadsheet. This is currently working quite nicely but I've been told that they need the result set from the object in a specific order as you would in a typical TSQL order by clause. I've looked at DB Source to Excel Destination via mapping but this just doesn't work. – Shaye Sep 30 '19 at 15:58
  • @ KeithL. The previous developer took a query directly from the SSRS report site and created the reports needed in Excel. It turns out this is very messy. You get all sorts or merged and hidden rows and columns which the users find frustrating to work with – Shaye Sep 30 '19 at 16:00
  • @Shaye In the vein of what PausePause is suggesting, look at creating a Dataflow Task, OLE Source (with an explicit sort) to destination of (https://learn.microsoft.com/en-us/sql/integration-services/data-flow/use-a-recordset-destination?view=sql-server-2017)[Recordset Destination] and then shred that - does that solve the problem? – billinkc Sep 30 '19 at 16:41
  • @billinkc. The reason why I used the Execute SQL Task was because I could place it in a FELC and since the FELC allows you to iterate each row in the Full Result Set Object I could then easily pick out the rows and columns using SSIS variables and transferring each row during each iteration of the loop into the desired Excel location. I'm not sure you can do that with a Source>>Sort>> etc but I'll try it out. The bottom line is whether this configuration would allow me to not only return a record set object but also allow me to iterate on each row... – Shaye Sep 30 '19 at 23:07

1 Answers1

1

As it seems you've learned, you cannot use ORDER BY in an Execute SQL task. Instead, use a Sort task to order the data after it's been loaded from the Execute SQL task.

Using the Sort Transformation Editor you can choose what columns are sorted and how you want to view them, in addition to determining "pass-through" columns or removing them from the Data Flow. enter image description here

This image uses an OLE DB Source, but the actual effect of the Sort task is the same if you use an Execute SQL task

enter image description here

This does pose the question: Why are use using an execute SQL Task? An OLE DB source would be much easier and more flexible to use. Consider rewriting your SSIS package to use OLE DB data sources when possible.

PausePause
  • 746
  • 2
  • 9
  • 21
  • So after you insert the new records and have to re-access them, you'll run into the same issue, no? Perhaps I misunderstand the approach you're proposing. There is no guaranteed ordering in a query unless you provide an explicit order by. – billinkc Sep 30 '19 at 16:36
  • In another comment @Shaye wrote "This is currently working quite nicely but I've been told that they need the result set from the object in a specific order as you would in a typical TSQL order by clause." SSIS Sort task works the same as TSQL ORDER BY, I don't understand why it wouldn't work. Wouldn't it be order in, order out? – PausePause Sep 30 '19 at 17:05
  • It doesn't sound like the data is being modified after it's entered into Excel. If ordered data is being put into Excel, it should remain where it was put when Excel is loaded later. Is there something else I'm missing? – PausePause Sep 30 '19 at 17:07
  • Your "Insert New Records" is an OLE DB Destination and thus my confusion – billinkc Sep 30 '19 at 17:10
  • Right. But within a data flow task the actual process of flowing the data is identical regardless of source or destination. Loading, sorting, and inserting should achieve the same result regardless if its OLE DB Src, Sort, OLE DB Dest or Execute SQL Src, Sort, Excel Dest – PausePause Sep 30 '19 at 18:14
  • Correct, an OLE DB Dest will add data in the order it is delivered. But then what? You need to get the data back out to use it which puts the OP back at square one. I think you're headed in the correct direction, just trying to put it all together – billinkc Sep 30 '19 at 18:24
  • If you think you have a more suitable answer, maybe you should post it because I'm not really understanding what you mean. – PausePause Sep 30 '19 at 19:30
  • @billinkc, PausePause. Thanks for your contribution on this. I'll experiment when I get into the office. However, I'm not sure whether Source>>Sort>>...configuration would work inside a FELC. The Execute SQL Task specifically returns a Full Record Set object and it allows me to loop through each row in the Full Record Set object which I then assign to column variables. Remember I just can't map from source to Excel columns because the headings are not in the first row in Excel and there some header information that I insert before hand in another SSIS process. – Shaye Sep 30 '19 at 23:23
  • @billinkc and PausePause. I tried the above this morning. The issue is that the Data Flow Task does not return an object (i.e. the result set from a query) for the Foreach Loop Container to use. So therefore I cannot access every row in the query set as I can with Execute SQL Task (unless any knows how this is possible?). It looks like the Data Flow Task just outputs only to a given source and cannot output to a Result Set variable object which Foreach Loop Container needs. – Shaye Oct 01 '19 at 10:05
  • @billinkc and PausePause. I tell a lie. You can actually connect from the [OLEDB source] to [Sort] to [Recordset Destination]. However, I get an error: "User::objBrTransaction does not contain an enumerator" from the Foreach Loop Container (in the FELC I'm setting the ADO Object Source variable to the variable object I defined in [Recordset Destination]) – Shaye Oct 01 '19 at 10:39
  • @billinkc and PausePause. Arrrghhh! Yes I got it now following this instruction: https://learn.microsoft.com/en-us/sql/integration-services/data-flow/use-a-recordset-destination?view=sql-server-2017 My mistake was putting the [Data Flow Task] inside the FELC rather than connecting it from outside – Shaye Oct 01 '19 at 10:46