September 19, 2017 at 2:04 pm
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?
September 19, 2017 at 7:30 pm
By any chance, are all of the columns implicitly convertible to all of the other columns in the destination table?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2017 at 5:49 am
Jeff Moden - Tuesday, September 19, 2017 7:30 PMBy 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))
September 20, 2017 at 6:01 am
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.
October 4, 2018 at 10:23 am
Confirmed.
I had the same problem and it was corrected by using the ADO.net Source and Destination.
October 4, 2018 at 10:34 am
Doesn't quite explain why the OLEDB provider didn't work.
January 8, 2020 at 10:36 am
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).
January 8, 2020 at 10:46 am
Have you tried searching the internet for the error "0xC020844B"?
It looks like a timeout.
January 9, 2020 at 9:24 am
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