Share via

Corrupted Source Data Issue in SSIS 4.6 with Excel Source via Data Flows in VS 2019

Adel Mansour 0 Reputation points
2024-11-26T17:40:53.01+00:00

Using SSIS 4.6 on VS 2019, with an Excel (Prof Plus 2016) Data Source, and I have a Package with 6 Data Flows that run consecutively, each one will access a different sheet from the same Excel Workbook. The Destination is a SQL Server 2022.

If we use Enable/Disable to enable each Data Flow individually, it will run in debug mode with no problem. But if we enable a set of the data flows or all of them in the same time, the run will look like successful (getting green on all of them), but if you drill in each Data Flow you will find no data are actually loaded.

When we added Data Viewers to see what is going on, we found that the Data being read into SSIS is completely corrupted by misplacing the data under wrong column names. We could not figure out the logic, like why these column data now appears under this specific name.

As we have multiple Packages in the same SSIS Solution, we tested this behavior to happen in all Packages. Running a separate Data Flow will run OK, trying to run the entire Package gets corrupted data starting the second Data Flow in the sequence.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,619 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. meghana sharma 0 Reputation points
    2024-11-27T06:05:38.3533333+00:00

    Hi Adel...Happy to answer. No worries!

    "Please verify whether the issue is related to the data or the server. I believe we can consider two possible scenarios:

    1. Data Issue: a. Review the column mappings (in the advanced editor) for both input and output, ensuring that the data types are correctly aligned between the source and destination. b. Ensure that all source connections are properly closed. c. Conduct troubleshooting by using trial-and-error techniques with the Error Output settings, enabling/disabling and setting the value to Redirect Row or Fail Component for each data flow.

    Note: As you mentioned that the data flow is running correctly, please leverage the Precedence Constraints to help further isolate the issue."

    2.Server issue: In VM, check in task manager if any unnecessary tasks are running for visual studio

    0 comments
  2. Adel Mansour 0 Reputation points
    2024-11-29T22:19:21.86+00:00

    Thanks for all the suggestions. Really appreciate all the help extended.

    We isolated the issue to be with the Script component.

    We have noticed that the 6 Data Flows in the same Control Flow, were copied from each other. So, we put a Debug Point inside the Script component, and amazing enough the scripts ran Ok. Without the debug, we get Data Corruption as well as some other funny Exception errors about Data is in unexpected format.

    So, we deleted all 6 Script components from the 6 Data Flows and recreated them individually (No Copy/Paste) and the errors disappeared and we now can run the entire data flow in a single run (all Enabled).

    It is a pity that we can not use copy/paste to copy over Script components across Data Flows, as it is in a way pain in the neck to open the truly slow script component and insert in each the same c# logic again and again, as we have over 450 of those in the entire data transformation pipeline. Just imagine maintaining these !

    I am expecting this something to do with the Meta Data of the script not being able to cope well with the copy/paste across different data flows, with differing meta data.

    Thanks again with the help.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.