• AndrewSQLDBA (2/18/2013)


    Hello Everyone

    I hope that your day has been better than mine.

    I have been working most of the day with a SSIS Package that is giving me a really strange effect.

    I will keep this really simple.

    I have two tables, the SSIS package is filling table1 from a flat text file source. Table2 is being filled with the data from Table1 using an Execute SQL Step, in which I call a stored procedure. Both tables have the same columns.

    When I execute the package, the data from the text file is loaded into Table1, and then the next step is calling the sproc to load the data into Table2. Which it does, but there are a large number of different rows in table1 than are in Table2. Different every time, but approx 16000 rows are inserted into Table1, which is the exact number in the text file. But there are approx 14,000 up too 15,500 rows that are inserted into Table2. The query is a simple. This is a straight data insert from one table to another.

    INSERT INTO dbo.Table2

    SELECT

    Column1

    , Column2

    , Column3

    , Column4

    , Column5

    FROM dbo.Table1

    I have no idea what is causing some rows to not be inserted. Oh, I created the tables using the same script, changing only the name for each table.

    I have tried this same package on multiple servers, picking up the same text file(s). There are different files per day. I have take some that are waiting to be processed, but were created up too a couple weeks ago. I have created multiple packages, one package exactly the one that is running, and others with only a couple steps in them. Just enough to pump the data from one table to another.

    I have never seen anything like this before. I am hoping that someone has some advice, or a suggestion as to what to look for that may be causing this rather odd insert.

    Thanks You in advance for all your assistance, suggestions and advice.

    Andrew SQLDBA

    Have you adjusted the "Rows Per Batch" or "Maximum Insert Commit Size" properties of your Destination connection? If so, SSIS is sending the data to SQL Server in chunks that SQL Server handles as separate transactions. If your package sends 10 batches of rows to SQL Server then executes the stored procedure, SQL Server may have only completed and committed 8 or 9 of those batch transactions when the stored procedure begins to read data from that table. Isolation ensures that the stored procedure will only see rows that are committed when it begins its SELECT (unless you've tinkered with isolation levels or used hints like NOLOCK). That may explain why you get varying numbers of rows in the second table each time you run the package.

    Jason Wolfkill