Post reply

Varying # of rows moved in Data Flow when underlying select is static!!!

  • June 17, 2009 at 9:38 am

    #384789

    This is blowing my mind right now. The last step of my package is a data flow. The OLE DB source is generated from a SELECT statement that draws from 2 tables and should produce 250K rows every time. When I run the package, however, I get a different number of rows (only between 1 and 36) with a successful completion!

    The derive step simply calls a User Variable so that I can assign it in the Mapping for the OLE DB Destination. There exist no non-deterministic functions along the way. The underlying data is NOT being edited. When I run my custom SELECT statement in SSMS replacing the column list with a COUNT(*), I invariably get 250K rows.

    ??? This is a total anomaly to me. I'm dumbfounded. What could possibly be keeping 99.9% of the rows that are normally returned from that query to 36?!?!? What would make the actual # vary from run to run?

    Please check out the attachments! Thanks SSC!

  • June 17, 2009 at 12:22 pm

    #1011194

    It seems the package was actually failing after the varying number of rows, but it was showing that it had succeeded. The source was providing strings like '"25000"' (including the double quotes) and implicitly converting them to INTEGERs at the OLE DB Destination object. I wonder if the double quotes had something to do with it, but I'm past this issue now.

    Tried deleting this post to no avail. Hopefully this helps someone in a similar sitch.

  • June 17, 2009 at 8:57 pm

    #1011394

    You never know when someone else might have the same issue as this in future - that's why not deleting your posts is a good idea, IMO. It's better that you posted back, explained the resolution and left it as a record.

    It sounds like you should be checking the 'Fail package on task failure' option (or similar - not got SSIS open right now) to make it more obvious in future that there has been a problem.

    Cheers

    Phil

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply