SSIS Package timouts - how to prevent

I have an SSIS package I built to transfer data from one SQL Server table to another. I have about 8 mln records that needs to be transferred. The package runs fine for a while but eventually times out before it reaches 8 mln records. Is there any way to prevent the timeouts?

I checked the Connection Manager and both Connection Timeout and General Timeout are set to 0
LVL 35
YZlatAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
When this times out, does it appear to be stuck on a single record?

I am assuming that this is erroring out because a record that is being read is locked somewhere else. One way to test this is to make the source a query, and use a NOLOCK hint, e.g.

SELECT 
    Field1,
    Field2,
    Field3,
    Field4,
    TransactionDate
FROM MyTable WITH(NOLOCK)

Open in new window


If this fixes the issue, then the timeout is due to locks on the source table. Generally, the process should not time out when a datapump is actively transferring records.  This is why I am suggesting a lock as a probable cause.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A typical fix I've seen is to throw this data pump into a For Loop, pumping 50,000 rows (or whatever number you want to make up) at a time.  This means that in the source there's another column like is_loaded (bit), that can be set to 1 so that the package knows not to pump rows already loaded.

To make this more spiff at the end of the loop add a SQL task that does a simple INSERT into a 'log' table you create, that logs that start date, end date, and row count of the pump.   Then instead of wondering 'When the eff will this package be completed?', you can keep selecting from this table and watch it add rows 50,000 at a time.

>I checked the Connection Manager and both Connection Timeout and General Timeout are set to 0
This isn't going to fix a poor-performing package, only potentially block other processes until it's eventually completed.  If you have any DBA's in your company that are on the cranky side, this is a near-guarantee to make you a target of their wrath.

Good luck.
Jim
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
And there's the obvious  stuff...
Are there triggers or foreign keys in the source that can be turned off, then do the pump, then turned back on?
Are the data types correct or at least modeled correctly between source and target?
What is the width in bytes in this pump?  If we're talking alot of nvarchar(1000) like columns, then that's going to go over the DefaultMaxBufferRows value of 10,000, and should be changed.
Are there a lot of rows that are failing some kind of data type / constraint validation?  Perhaps a better approach is to import into a staging table of all varchar's, then validate like hell, then import the good rows and gracefully handle the bad rows.
When is this package running?  If during the day you may be competing with production processes, better to schedule it at night when nothing's going on.
Is 8mil rows a full-load file?  If yes, consider a redesign and only pumping in changed rows, which will be a significantly smaller number of rows.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Are you processing any data transformation or you're only exporting and importing the data as is? If no transformation is needed you can use BULK COPY instead of SSIS package.
bcp is an utility than can let you perform bulk copies without SSMS.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.