SSIS Package Consuming Excessive Disk Space on Local Drive (C:)

  • Hello everyone,

    I’ve developed an SSIS process to compare two relatively large tables. When I run the package from Visual Studio, the processing takes up space on my local PC's hard drive and continues until the drive is full (C:).

    Does anyone know why this

    Thanks in advance for your feedbac

    Sans titre

  • best advise is DON'T use SSIS for this.

    Load data from one of the db's into a staging (or even tempdb) and do the merge on the SQL server side.

    if you insist in using SSIS - ensure the 2 sources are sorted on the required fields (e.g. on the ones you have on your Sort steps, then remove the sort steps from the package and on the 2 sources you set the IsSorted to true - as well as the sortkeyposition (see https://learn.microsoft.com/en-us/sql/integration-services/data-flow/transformations/sort-data-for-the-merge-and-merge-join-transformations?view=sql-server-ver16)

  • That's good advice from Frederico.

    Sorts in SSIS are to be avoided if possible, as they are blocking components and perform very much worse than in-database sorts.

    If your tables reside on different servers, using SSIS may not be the worst idea, but if the tables are on the same instance, this is not a job for SSIS.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • YES but merge join requires SORT operation

    Attachments:
    You must be logged in to view attached files.
  • abdalah.mehdoini wrote:

    YES but merge join requires SORT operation

    if you read my post and the link I gave you it states what you have to do to address that requirement.

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

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