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
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.