question

KumarAbe-5561 avatar image
1 Vote"
KumarAbe-5561 asked ArturoRodriguezCobo-2803 commented

SSIS not inserting all rows from ODBC (oracle) to SQL server 2019

I have an issue with migration to SQL server 2019.
My Source is Oracle and my Destination is Microsoft SQL Server 2019. My package consists of a simple Data Load task; ODBC Source Connection(oracle)/ and OLE DB Destination connection(2019 SQL server). And data is coming from oracle SQL script.

Issue -> The package is successfully executing but not inserting all rows. If the oracle SQL script is satisfying 100 records the insert is happening for 80 only with no errors or warning.

Have tried :
1) SSDT 2017(15.8,15.9.3,15.9.7)
2) Visual Studio Community 2019 with SQL Server Integration Services Projects (3.10 and 3.12.1)
but facing the same issue.

Note - this issue is resolved when we are changing "TargetServerVersion" from 2019 to 2017 or lower, but when the package is run through the job from SQL server 2019 the count is back to less.


As we need to migrate a lot of packages, we need a solution.

Images of the same package execution below:(correct count from VS 2015 version)

84896-image.png





sql-server-generalsql-server-transact-sqlsql-server-integration-servicessql-server-migrationsql-server-migration-assistant
image.png (419.7 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't anything about SSIS, but I would use Profiler to see what statements that SSIS emits to SQL Server in the two cases. That can give at least a small clue of what is going on.

0 Votes 0 ·

How exactly did you determine the number of records do not match?

What you describe is not really possible in a straight pull/push data flow task. It is possible you are ignoring errors at some point of your package, which is causing your issue. Or you have a transformation which is restricting the records.

The first thing to do is look at the execution log and see the number of records for the source and destination.

0 Votes 0 ·

When I am setting DataFlow setting AutoAdjustBufferSize = True or increasing ODBC Source BatchSize from 1000 to 10,000 getting the correct count.

Can anyone explain why?

For example - if the total rows to be inserted is 60000 with AutoAdjustBufferSize = False(default) it was loading only around 25000.

0 Votes 0 ·

We have a similar issue trying to extract data from a DB2 database with an ODBC Source. Have you been able to fix this issue?

0 Votes 0 ·

Did you find a resolution to this? I am having the same problem. 110 million rows on the source. Pulls 500K and stops. No error messages displayed. Just stops moving data and spins.

Visual Studio 2019. SQL 2019. Oracle 11g. I happen to be using OLEDB driver from Oracle instead of ODBC.

0 Votes 0 ·

Hi!
I found a solution with an ODBC driver for DB2 database. I had a similar problem. First I installed the last version of the driver for my DB2 database, but this didn't solve the problem for all the cases. The problem was the type of the columns. Some columns were CHAR (fixed length) and contained strange characters. I used the TRIM() function in the source query and this solved the problem. In other cases, the problem was the precision of the NUMERIC type. None of those problems occurred in SSIS 2017. I think there is a change in the form that SSIS 2019 manage the source types.

My advice is: try the source query column by column (avoid "select * from" ) and detect which column causes the problem.

Hope this helps.

Art.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft commented

Hi @KumarAbe-5561 ,

May I know if you use other transformations between source and destination?

Please refer to the following links:
Upgrade Integration Services Packages

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KumarAbe-5561 ,

The size of the buffer is determined by two properties: DefaultBufferMaxRows - with a default of 10,000 rows - and DefaultBufferSize - with a default of 10MB. However, those defaults are very small compared to the amount of memory that modern servers have available. So every time you developed a new SSIS package, you had to bump up the size specifications of the buffer (for example to 40MB and 100,000 rows). But this could lead to a bit of trial and error, since buffers that are too big might slow down performance.

Hope the following links will be helpful:
1.Improving data flow performance with SSIS AutoAdjustBufferSize property
2.SQL Server Integration Services and Auto Adjust Buffer Size

Best Regards,
Mona



0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @KumarAbe-5561,

It is not clear how you are using SSDT for SSIS development in VS2019.
There is no more SSDT for SSIS, SSRS, and SSAS development in VS2019.
SSDT role is designated for Database Projects only in VS2019.

You need to install SQL Server Integration Services Projects extension for VS2019.
Its download link: SQL Server Integration Services Projects

Please make sure that you have latest versions installed for both:

  • VS2019 v.16.9.*

  • SQL Server Integration Services Projects v.3.12.1


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Doing the same when using VS 2019.
Also tried with SSDT VS 2017.

0 Votes 0 ·

Doing the same when using VS 2019.

Please confirm that you are using both versions specified in my answer.

Also, please edit your original question, and add a screen shot of the Data Flow Task at the end of the execution.
0 Votes 0 ·
ArturoRodriguezCobo-2803 avatar image
0 Votes"
ArturoRodriguezCobo-2803 answered

I had a similar problem using an ODBC driver and a DB2 database. I solved it installing the latest version of the ODBC Driver. Hope it helps.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.