Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why does SSIS take so long to "build" before running? Expand / Collapse
Author
Message
Posted Tuesday, February 22, 2011 1:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1067890
Posted Tuesday, February 22, 2011 1:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 6,256, Visits: 7,441
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1067895
Posted Tuesday, February 22, 2011 2:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1067909
Posted Tuesday, February 22, 2011 2:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 6,256, Visits: 7,441
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1067919
Posted Friday, February 14, 2014 6:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 26, Visits: 513
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 ==========

Post #1541584
Posted Friday, February 14, 2014 7:22 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1541594
Posted Friday, February 14, 2014 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:58 AM
Points: 239, Visits: 1,186
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.
Post #1541614
Posted Friday, February 14, 2014 8:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 10:05 AM
Points: 26, Visits: 513
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.

Post #1541615
Posted Friday, February 14, 2014 8:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 9:12 AM
Points: 3,931, Visits: 7,158
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; They'll drag you down to their level and beat you with experience"
Post #1541618
Posted Wednesday, February 19, 2014 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 12:46 AM
Points: 119, Visits: 484
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

Kind Regards


Paul Hernández
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1543021
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse