I have an issue with VS SSDT 2017, SSIS Express Edition. My Source is Oracle 12G and Destination is SQL Server 2017. My package consist of simple Data Load task; Source Connection and Destination connection. And data is coming from SQL script. Using OLE DB connection. Count in source says 238674 rows but SSIS loads and inserts only 144254 rows and there is no any error. Package ends with success (green check marks). Destination table has no issues with types or something. All types and sizes are identical with source. When I delete most of the columns from SQL script and load only 1 or 2 columns it extracts and inserts all 238674 rows. But when there are more columns (8-9 columns), inserted row number is getting smaller.
0
votes
Which Oracle OLEDB connection do you use? Try using: Oracle Provider for OLE DB Also try using fast load in your destination if you're not
– holder
Can you start with 1 or 2 columns and then work up to see which column is causing the problem? Sounds like you're getting rows ignored or truncated. Also, do you exactly how many rows you're expecting from your source procedure?
– Jim Jimson
@holder for source conn I am using Oracle Provider for OLE DB. And I tried both load and fast load. Result is same.
– Nail_Mustafa
@JimJimson Count(*) returns 238674 rows in source, but only 144254 is being extracted and inserted to destination. When I change script and remove some of the columns, inserted number is increases to something 226000 after a certain number of columns removed from script, it extracts all 238674 rows. Seems like it is related with the total volume of extracted data.
– Nail_Mustafa
My hunch is it's going to be some values violating some constraint. I'd advise cutting back the columns until it loads all the records and then add columns 1 at a time until the rowcount drops. Then take each offending column out to see if it's one or more column causing the problem.
– Jim Jimson
2 Answers
0
votes
