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 : SSDT 2017(15.8,15.9.3,15.9.7) 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)

I have the same exact issue, but it is not happening on all tables only some of them. I do not see a correlation or pattern in row count either between tables. I have a table with 750000 rows only importing 400000, but another table with 950000 rows importing 950000. I have increased the buffer in SSIS and that made it worse. Any suggestions are appreciated. Newest driver installed. SSDT 2017. SQL Server 2019. Standard Oracle Home Driver via a linked server imports the correct row count. I am trying to speed up the ETL via an ODBC in SSIS. *** Update I fixed this by setting the Data Flow Task Property: AutoAdjustBufferSize to true. Problem resolved if anyone else is looking for a solution to this.

Hi @Kumar, A. be , 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.

Hi @Kumar, A. be , 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

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.

Thanks Kumar for posting the fix. My package also ran fine after changing the targetserverversion. Saved me so much time. Appreciate it. :)

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

Kumar, A. be 16

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 :

SSDT 2017(15.8,15.9.3,15.9.7)
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)

Erland Sommarskog 112.8K Reputation points MVP

2021-04-05T12:06:05.183+00:00

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.
Tom Phillips 17,741 Reputation points

2021-04-05T17:59:52.367+00:00

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.
Kumar, A. be 16 Reputation points

2021-04-06T12:56:21.687+00:00

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.
Arturo Rodriguez Cobo 151 Reputation points

2021-05-27T10:36:41.433+00:00

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?
David Fields 1 Reputation point

2021-06-13T22:56:48.14+00:00

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.
Arturo Rodriguez Cobo 151 Reputation points

2021-06-14T07:03:16.777+00:00

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.
CF 0 Reputation points

2024-02-18T08:42:25.26+00:00

Hi, I have similar issue importing data from Oracle to SQL server. Total records should be 450k, but SSIS only imports around 190k records and it always says execution is successful. I've changed AutoAdjustBufferSize = TRUE and it's even worse. When I run direct OPENQUERY via linked server on SQL server, I could import all 450k records. Not sure if this is a bug in SSIS or something else, very frustrating. Any suggestions or solutions would be much appreciated.

5 answers

Jenkins, Jennifer 10 Reputation points

2023-03-18T00:12:55.1733333+00:00

I have the same exact issue, but it is not happening on all tables only some of them. I do not see a correlation or pattern in row count either between tables. I have a table with 750000 rows only importing 400000, but another table with 950000 rows importing 950000. I have increased the buffer in SSIS and that made it worse. Any suggestions are appreciated. Newest driver installed. SSDT 2017. SQL Server 2019. Standard Oracle Home Driver via a linked server imports the correct row count. I am trying to speed up the ETL via an ODBC in SSIS.

***Update I fixed this by setting the Data Flow Task Property: AutoAdjustBufferSize to true. Problem resolved if anyone else is looking for a solution to this.
Please sign in to rate this answer.

2 people found this answer helpful.
CF 0 Reputation points

2024-02-16T20:10:40.38+00:00

Hi, I have same issue. Just a simple SSIS to import data from Oracle to SQL server. Changing AutoAdjustBufferSize = TRUE made it worse. Using default setting, it runs successfully but only pulls in 199260 records, though I am expecting 420k records. When I ran the same script using SQL Server OPENQUERY via linked server, all records are imported. Any suggestions are appreciated.

Maxwell Hartono 0 Reputation points

2024-11-14T06:34:08.11+00:00

AutoAdjustBufferSize = TRUE works for me.

But I also set the batch size to 10,000 rows.

Higher batch size = Higher RAM requirement (Buffer) = faster

I think thats how this works.

I would be interested to know if anyone have tested this on VS2022?
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Monalv-MSFT 5,901 Reputation points

2021-04-05T08:16:46.807+00:00

Hi @Kumar, A. be ,

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.
Please sign in to rate this answer.
Monalv-MSFT 5,901 Reputation points

2021-04-12T07:09:50.56+00:00

Hi @Kumar, A. be ,

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Yitzhak Khabinsky 25,961 Reputation points

2021-04-05T14:10:56.747+00:00
Hi @Kumar, A. be ,

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
Please sign in to rate this answer.
Kumar, A. be 16 Reputation points

2021-04-05T17:07:38.01+00:00

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

Yitzhak Khabinsky 25,961 Reputation points

2021-04-05T17:54:46.597+00:00

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.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Arturo Rodriguez Cobo 151 Reputation points

2021-05-31T10:48:51.89+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Goutham T. Rajesh 6 Reputation points

2023-05-31T14:59:53.26+00:00

Thanks Kumar for posting the fix. My package also ran fine after changing the targetserverversion. Saved me so much time. Appreciate it. :)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

5 answers

Your answer