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 issue Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 3:51 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
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 #1521807
Posted Wednesday, December 11, 2013 3:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
try to make a copy of the excel file and then use bulk insert. by doing so you can avoid the blocking source issue.
Post #1521810
Posted Wednesday, December 11, 2013 4:05 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
No i dont want to do like this...there are millions of records and I can't copy from excel to db everytime
so that I have run SSIS package, it will insert the data in a bulk but at the same time when we want to select the table
that are not allowing. After completion of bulk insert its allowing to view the data.

Post #1521814
Posted Wednesday, December 11, 2013 4:48 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
in bulk insert Task options, what is the value of 'TabLocK' and the value of batchSize?

By the way, why you want to view the data in that table? is for the application or its just to see if there are any rows in that table ?
Post #1521834
Posted Wednesday, December 11, 2013 6:40 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:08 AM
Points: 100, Visits: 328
Thanks for the reply.

This is 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, but the deadlock occurs in the same time.
for that reason I want the script or any alternate solution so that i can resolve the issue.

Thanks.
Post #1521870
Posted Thursday, December 12, 2013 12:13 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:13 AM
Points: 747, Visits: 1,315
Okay , what is the Row Per Batch value configured on your destination level?

Post #1522165
Posted Thursday, December 12, 2013 12:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 AM
Points: 13,741, Visits: 10,716
This is exactly why cross posting is a bad idea. Now you have two similar threads with different people participating, possibly wasting time for some of them.

http://www.sqlservercentral.com/Forums/Topic1521887-364-1.aspx




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 #1522168
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse