Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4862 Visits: 7365
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" ;-)
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4862 Visits: 7365
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" ;-)
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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
Sector7G
Sector7G
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 783
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 ==========


MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4862 Visits: 7365
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" ;-)
Paul Keys
Paul Keys
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 1793
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.
Sector7G
Sector7G
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 783
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.
MyDoggieJessie
MyDoggieJessie
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4862 Visits: 7365
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 :-D

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Paul Hernández
Paul Hernández
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 654
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search