SSRS Duplicate records

Question

Post reply

SSRS Duplicate records

pricca

SSC Enthusiast

Points: 172
More actions
March 10, 2023 at 4:56 pm

#4159354

Hello,
I recently migrated from 2012 to 2016 SQL server. When comparing SSRS reports, the new server is showing the records as exactly double.
Troubleshooting/Background
-Data is being pulled from a flat file via SSIS jobs.
-Each job runs 1 time successfully in SSMS. If a job fails, the records in SSRS will not duplicate. This makes me think the SSIS Jobs are running a second time out of view.
-I initially thought the packages might be pointing to the old server so I verified each package's data connection in visual studio.
-I have also since shutdown the old server and the data is still duplicating.

Any suggestions on how to fix this record duplication issue?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Emperor100 SSCrazy Points: 2173 More actions · Answer 2

You should also upgrade to SSRS 2016.... on the reporting side to support 2016 database.

-- Open the report and check for SQL is correct in the report.

-- Compare the table data using SSMS.

Try installing latest patch(es):

https://support.microsoft.com/en-us/topic/kb3207512-important-update-for-sql-server-2016-sp1-reporting-services-546a492b-de10-01be-fb55-943b5c574432

https://learn.microsoft.com/en-us/answers/questions/810215/ssrs-hide-duplicate-rows

=======================================================================

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

pricca wrote:

Hello,
I recently migrated from 2012 to 2016 SQL server. When comparing SSRS reports, the new server is showing the records as exactly double.
Troubleshooting/Background -Data is being pulled from a flat file via SSIS jobs.
-Each job runs 1 time successfully in SSMS. If a job fails, the records in SSRS will not duplicate. This makes me think the SSIS Jobs are running a second time out of view.
-I initially thought the packages might be pointing to the old server so I verified each package's data connection in visual studio.
-I have also since shutdown the old server and the data is still duplicating.
Any suggestions on how to fix this record duplication issue?

How did you migrate? Is there a possibility of the data from the old system being in the new system twice?

What are the primary key(s) on the affected tables? If there are not natural primary keys, meaning not something like an identity, create a unique index on the table(s). Assuming that there is an issue with an SSIS package running twice, or some process inserting double rows, that will cause an error to be thrown and may make it easier to track down.

Also, think about this. I'm betting only one table has duplicate rows. A simple lookup table with duplicate rows, if there is an inner join to it, will cause the tables joined to it to be duplicated. Take the SQL statement, and run it in an SSMS query. Comment out the joins, and execute only the SELECT ... FROM TableName. Add in each table one by one, and see where the duplicates appear.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/