Why does SSIS take so long to "build" before running?

  • I have a ETL project that has about 15 packages in it, each package loads data into a SQL Server via delimited files. These individual packages are nothing fancy, small script to check if a file exists, a data-flow task that loads data from the file into the database (some having a few derived columns, some with none), a SQL script task (updating a table), and a delete file task.

    When I press F5 to test a new change or right-click and choose "execute task" it takes several minutes for it to build and actually execute.

    Is there some setting I can change to speed this up?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • It's compiling and parsing all the components. You can see all the moving parts in the last tab (Execution Results/Progress), and take a look at timing. It'll help you see what in particular is taking the most time.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Thanks for the reply Craig. This only shows the execution times and steps for everything "after" the build process completes...which runs exceptionally quick (< .5 secs)

    It's the actual build that's taking several minutes.

    Any ideas on the build aspect?

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Serge Mirault (2/22/2011)


    Thanks for the reply Craig. This only shows the execution times and steps for everything "after" the build process completes...which runs exceptionally quick (< .5 secs)

    It's the actual build that's taking several minutes.

    Any ideas on the build aspect?

    Hm, another brainfart for me, you're right, it doesn't show times for the validation components.

    Hrmmmm...

    I assume you do see things like this, in particular the Validation component?

    Task Data Flow Task 2

    Validation has started (2)

    Progress: Validating - 0 percent complete

    Progress: Validating - 100 percent complete

    Validation is completed (2)

    Start, 1:44:44 PM

    Finished, 1:44:44 PM, Elapsed time: 00:00:00.094

    If some of those break down to 10% increments or the like, that'll be a hint. Also, in your Output tab (after you run it once), you'll see a dropdown that reads as debug. If you switch it to build, do you get anything other than:

    Build started: SQL Integration Services project: Incremental ...

    Build complete -- 0 errors, 0 warnings


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I have been searching for an answer to this also. I have a package that takes a few seconds to run. It outputs about a dozen rows to a CSV file. But it takes a minute or two for it to build before it starts running. Is there a way to reduce the build time?

    I checked the output tab using the instructions in the previous comment. The result is below.

    ------ Build started: Project: SSIS development, Configuration: Development ------

    Build started: SQL Server Integration Services project: Incremental ...

    Build complete -- 0 errors, 0 warnings

    ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

  • This is a pretty old post, but the root cause of my issue was that I was opening the project from a Network location. Once I moved it locally to the server, it ran in seconds. Not sure if that applies to your scenario or not...

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • The other time consuming build activity in SSIS is when you're building a deployment utility on every build. Depending on how many packages in your solution this could potentially be causing your issue.

    Typically when developing/testing in BIDS i'll keep the CreateDeploymentUtility property set to false, to reduce the response time from starting the debug run and the package actually starting.

  • Thanks for the replies. It's a minor inconvenience. It would be nice if the package would run without waiting two minutes for it to build.

    The project is on a network drive because there are a few of us who develop packages in this project file. I have noticed that it takes more time to build as more packages are added to the project.

    Also, I do have the deployment utility set to false.

  • I have no doubt that the network location is the issue. If you copy it locally and run it I would bet a round of beers that the issue goes away 😀

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Hi,

    I agree with Pau Key and I would check the configuration of the solution.

    Does your solution contain multiple projects?

    You can check this post to see if it helps you:

    Preventing projects from deploying when debugging a SSIS package [/url]

    Kind Regards

    Paul Hernández

Viewing 10 posts - 1 through 9 (of 9 total)

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