Scheduled SSIS package not pulling all data

  • I have a scheduled SSIS package that is not including all information every morning into the flat file it generates. It's a really simple SSIS package. It does a data extract to a comma delimited file, renames it with the current date then it uploads the file. After that it moves it to an archive folder.

    It seems that 2/3's of the time it gets all the data it needs to but the other part of the time, it skips records. When I run the same script in SSMS after the fact, the results (within SSMS) are spot on but the file it created might be 5 to 15 records short. Normally there are anywhere from 20-40 records every day and it's a really simple SQL script. When running the script in SSMS it takes less than 1 second.

    What would be causing the script to skip over records? The filter criteria is such that it pulls on information that is never changed once it's entered into the system so I could go back and do day by day screen shots if I wanted to and running my SQL script matches perfectly with what the application shows. I'm thinking this has something to do with a bad connection or something along those lines.

    I went through all 15 packages that run on this server and none of them overlap. The AV scan takes place 11 hours before this script fires off. The backups take place 7 hours before this job fires off. I'm not sure what could be interfering with this SSIS package. I don't have any issues with any of the other SSIS packages on that server. The packages that pull data from our database pull complete data.

    Does anyone have any troubleshooting tips to suggest?

    TIA,

    John

  • Is there anything in the package configured to redirect rows by error or truncation?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (8/7/2013)


    Is there anything in the package configured to redirect rows by error or truncation?

    No I don't. It's a pretty simple SSIS package. It has an OLE DB Source object and a Flat File Destination object in the Data Flow tab...that's it.

  • 100% sure the source is pointing to the correct server?

    Any package configurations?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (8/7/2013)


    100% sure the source is pointing to the correct server?

    Any package configurations?

    I'm 100% positive it's pointing to the right server. I even double checked after reading your first question.

    As of right now, the Package Configurations isn't even active...never has been. (no checkmark next to 'Enable package configurations').

    Thanks for asking those questions though.

    --John

  • I don't think this is going to help but just in case, these are screenshots of the package in it's entirety....

    http://oi43.tinypic.com/10r5zyw.jpg

    http://oi42.tinypic.com/2zz288x.jpg

  • You're right, it didn't help 🙂

    Normally SSIS wouldn't just lose rows. Even if it's a bad connection. TCP/IP connections are used, so they are pretty reliable.

    A bad connection could slow down your package, but lose rows? Especially when the dataset is so small (a mere 40 rows), it seems unbelievable.

    What does the SQL statement in the script look like?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I am also facing the same issue. Any suggestions?

  • I would think it unlikely that would be losing rows in this way.

    You might try posting the Sql Statement that pulls the rows... maybe someone might spot something.

    Is there a Where Clause? Does it depend on any flags, dates etc to pull data...

    Some other process could be interferring in a way that might not be obvious.

Viewing 9 posts - 1 through 8 (of 8 total)

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