Unpivot very slow SSIS

E L 1 Reputation point
2021-03-10T09:57:24.11+00:00

I have three CSV files, that each one contain 462 rows and 56 columns. I created the unpivot task and this is very slow for 9 hours and not finished. The last status in progress is the count of rows of the first file.

Any suggestion, alternative?

@Olaf Helper
@Tom Phillips

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,437 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Russel Loski 421 Reputation points
    2021-03-10T14:01:25.833+00:00

    The first thing would do is to get a basic idea of where the problem is. I would test a simple package that reads one file and shows the output in a data viewer. How long does that take?

    You could see if using the script component works for you. Check out https://www.sqlmovers.com/ssis-flexible-unpivot/.

    0 comments No comments

  2. Monalv-MSFT 5,891 Reputation points
    2021-03-11T09:41:23.07+00:00

    Hi @E L ,

    Please divide the CSV files into different small files.

    Then use Unpivot Transformation in SSIS Data Flow Task.

    Please refer to Integration Services Performance Best Practices – Data Flow Optimization.

    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.