none
limit the number of rows to flow from ole db in ssis DFT RRS feed

  • Question

  • Hi,

    I have a situation where I have a DFT with :

    • Ole db source from a variable contains MDX query
    • Derived column
    • Excel Destination Plus (cozyroc)

    The query runs for each iteration of employee and dumps all rows in to excel file with their names.I am stuck at a place where ,for an employee,there are numerous rows outputted from the query > 1,048,576 .It exceeds the number of rows permitted by Excel 2010 version.I want to check if the rows exceeds 50% of this number or greater than a specific number of rows,the flow shouldnt proceed downstream to derived column and cozyroc component 

    (OR)

    Somehow the DFT should fail upon on error of the ole db query so i can handle this with on task failed handler and proceed with next iteration

    I tried the below but it doesnt work as expected :

    • ole db->rowcount->conditonal split->excel destination  : Rowcount calculates the rows as they pass through and allow the rows downstream to conditional split ,here the condition that rowcount>100000(for testing)doesnt seem to have any effect.Irrespective of conditions applied here ,the rows flow downstream into the Excel.
    • I have applied a on error event handler to try and see if the error fails the DFT,but unfortunately,the on error handler does write the message that the rows exceed but it doesnt fail the DFT and DFT keeps running indefinitely without stopping whole day.
    Tuesday, October 15, 2019 7:09 PM

All replies

  • I guess you could 1st dump the rows into a staging table, count and deal with them. But Excel as a destination is obviously very limiting in your case overall.

    Arthur

    MyBlog


    Twitter

    Tuesday, October 15, 2019 7:37 PM
    Moderator
  • Table wont be necessary because these are merely for reporting purposes by users.Do you think there is some other way within ssis ?
    Tuesday, October 15, 2019 7:41 PM
  • Try using Script component with Counter and fail once you hit the limit 

    For Example in the Script Component add this code to error out when you hit the limit

    Note: Code1 and Code2 in the same ScriptComponent

    CODE1:

    public class ScriptMain : UserComponent
    {
        int RowCounter = 0;

    CODE2:

     public override void Input0_ProcessInputRow(Input0Buffer Row)
        {

          
             RowCounter = RowCounter + 1;
            if (RowCounter == 5)// Replace 5 with your limit
            {
                int i = 1;
                int j = 0;
                float z = i / j;
            }
        }


    mohammad waheed

    Tuesday, October 15, 2019 11:17 PM
  • Hi msdnpublic1234,

    Thanks for your question.

    Per your scenario, you could consider using Row Sampling Transformation to extract the selected number of row data from it.

    For more details, please refer:

    Row Sampling Transformation in SSIS

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 16, 2019 3:18 AM
  • Will,

    I already tried this but it doesnt work.

    Wednesday, October 16, 2019 12:25 PM
  • Will,

    I already tried this but it doesnt work.

    So why it doesn't work? Is it not suitable for your expected design? Could you please show us where it doesn't work?

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 17, 2019 6:56 AM
  • I tried to set up 200,000 as the sampling rows and when the rows exceeds this limit ,it doesnt fail the DFT but keeps running.
    Thursday, October 17, 2019 1:46 PM
  • Failing SSIS on purpose is bad design.

    I think I have an easy solution for you: https://www.infoq.com/news/2011/06/Balanced-Data-Distributor/

    With it you can specify a bucket of 200K rows and always take the data from the 1st bucket.


    Arthur

    MyBlog


    Twitter

    Thursday, October 17, 2019 5:33 PM
    Moderator