SSIS not copying all rows in data flow task

  • I'm trying to create a basic SSIS package to copy almost all the columns of a table from production down to QA, for one of the developers to run some reports against.  While the package does what I want it to, it's not copying all the rows in the source table.
    Currently, when I execute it, it copies about 20220 rows of 23667 rows.  I've added Error outputs, which we'll come to in a minute, I've compared rows that get copied to rows that don't get copied and not seen anything that should cause a problem.

    I created the destination table by scripting out the source table, then removing a few columns that point to Foreign Keys which the Dev told me are not needed for his purposes, there are no columns defined as Identities, nor are there any indexes on either side.
    I've set up my OLE DB Source item to use an SQL Command (with the columns explicitly named) and the "Table or View" method.  There are no transformations being applied to the data in the data flow.
    I've set the OLE DB Destination item to load using both the "Table or view - fast load" and "Table or view."  This is where things get a bit interesting.  If I have it set to "Table or view," I get about 6200 rows in the destination table and about 13000 rows in the error table, which still puts me about 3447 rows short.

    Both connections go to SQL Server 2014 Enterprise servers, and the account I'm using to establish the connections has db_owner on both the source and destination.

    Ideas, suggestions on how to further troubleshoot this?

  • By any chance, are all of the columns implicitly convertible to all of the other columns in the destination table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, September 19, 2017 7:30 PM

    By any chance, are all of the columns implicitly convertible to all of the other columns in the destination table?

    Jeff, if I'm understanding you, then no.  That's presuming you mean something along the lines of, can column A in the source (nvarchar(X)) be converted implicitly to the data types of columns A->F in the destination (some nvarchar(X), ints, and bits)
    If you mean, can column A in the source be implicitly converted to the data type of column A in the destination, then absolutely, as all the columns are defined exactly the same between the source and destination (ie, column A.s is nvarchar(50), column A.d is nvarchar(50))

  • An problem solved.
    Searching yesterday I'd come across a post from someone with a sort of similar issue, which was resolved by switching from the OLEDB provider to the ADO.NET provider for the source and destination.

    I just finished changing my package to use ADO.NET to talk to the servers and test-ran it, worked like a champ and grabbed and transferred all the rows to the destination.

  • Confirmed.
    I had the same problem and it was corrected by using the ADO.net Source and Destination.

  • Doesn't quite explain why the OLEDB provider didn't work.

  • I am pleased that changing to ADO.net fixed your problem

    Unfortunately, I use ADO.net already and it fails!

    It might help if the error message was more meaningful

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ADO NET Destination" (305) failed with error code 0xC020844B while processing input "ADO NET Destination Input" (308).

  • Have you tried searching the internet for the error "0xC020844B"?

    It looks like a timeout.

  • It was indeed a timeout!

    I've changed the timeout from 30 to 60 seconds, and it now loads my data - many thanks!

    I was googling the whole error message, rather than the specific code - mea culpa

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

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