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

bulk insert operation through SSIS package. Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 7:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:28 AM
Points: 50, Visits: 143
Hi All,

I am getting problem on bulk insert issue through SSIS package.

when bulk of the record inserting in the particular table from excel through SSIS package and at the same time the other want to read the data
so they are not able to read the data because of the blocking issue.

Can anyone please provide me script on this ?

Thanks...
Post #1521887
Posted Wednesday, December 11, 2013 7:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
I think it is quite normal that readers are blocked when a bulk insert operation is going on. This is to prevent dirty reads.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1521909
Posted Wednesday, December 11, 2013 8:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
Koen Verbeeck (12/11/2013)
I think it is quite normal that readers are blocked when a bulk insert operation is going on. This is to prevent dirty reads.


+1 agreed this is sensible behaviour.

Maybe your package can be optimised - typically how many rows are you inserting and how long are the locks lasting?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1521921
Posted Wednesday, December 11, 2013 10:39 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 1:28 AM
Points: 50, Visits: 143
Thanks for the reply...
I want to clear my question , I think this will be easy to understand.

we have a production DB. we have millions of data which client is keep sending and we have created SSIS package to copy data from excel to the DB.

At the same time when data is loading into the DB, the end user want to catch the data, because some time data is loading into the working hour also, at the same time end user can not sit idle. so that time the deadlock occurs.
for that reason I want the script or any alternate solution so that i can resolve the issue.

Thanks.
Post #1522148
Posted Wednesday, December 11, 2013 11:08 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:25 AM
Points: 4,828, Visits: 11,184
arooj300 (12/11/2013)
Thanks for the reply...
I want to clear my question , I think this will be easy to understand.

we have a production DB. we have millions of data which client is keep sending and we have created SSIS package to copy data from excel to the DB.

At the same time when data is loading into the DB, the end user want to catch the data, because some time data is loading into the working hour also, at the same time end user can not sit idle. so that time the deadlock occurs.
for that reason I want the script or any alternate solution so that i can resolve the issue.

Thanks.


Your client is sending 'millions of rows' ... in Excel? As the latest version of Excel has a max number of rows per worksheet of 1,048,576, this sounds like a cumbersome multi-worksheet solution and I'd seriously be looking into moving to a CSV load instead. Anyway, I am getting sidetracked.

Are we dealing with updates, inserts or both?

Some sort of partitioning solution may be possible and there would be almost zero deadlocks - how is the data organised, is it by some sort of transaction date?

-edit: fix typo



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1522151
Posted Wednesday, December 11, 2013 11:55 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:35 AM
Points: 12,208, Visits: 9,172
arooj300 (12/11/2013)
Thanks for the reply...
I want to clear my question , I think this will be easy to understand.

we have a production DB. we have millions of data which client is keep sending and we have created SSIS package to copy data from excel to the DB.

At the same time when data is loading into the DB, the end user want to catch the data, because some time data is loading into the working hour also, at the same time end user can not sit idle. so that time the deadlock occurs.
for that reason I want the script or any alternate solution so that i can resolve the issue.

Thanks.


Insert into a staging table and then partition switch the new data into the final destination table.
(if you have enterprise edition...)




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1522157
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse