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


Batch Processing by reading data from a table to a ole db destination


Batch Processing by reading data from a table to a ole db destination

Author
Message
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
Hi,

I have 1000 records in a table....
I need to process 1000 records in batch(100 records per batch)..
Read 100 records from the table and insert into another table in the db...
Mark these 100 records as processed...
Again read the next 100 records which are not processed...
and so on...

How can it be done using SSIS Controls...

Can i use a For loop control for the looping purpose..how to make it then to 100 records ?

Thanks in advance,

Naveen
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11028 Visits: 14858
I wouldn't use SSIS for this. This is one of those instances where I'd use a loop. Can you provide table structures and some more info as to why you need to move the data and why you need to limit to 100 per batch? 1000 rows should move quickly.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
100 records was just an example...Actually by 1000 records i meant was to insert large number of records....

Explaining the working of the application is a below...

There is a application from which a user will upload an excel file...
The max size of the file allowed to upload is 2gb...So in turn there can be large amount of records in the excel file...When the user uploads the excel file the SSIS starts working and inserts the data from the excel sheet to the tables in the database..So during the time of this insertion the application can be used
by other users also..
ie if the data is being upload to a user table there will be other users who can access the application and create records for users..so which means parallel insertion should be done to the tables...without the table getting locked for one insertion....So thats why i wish to insert data batch by batch say 100 or
1000 or more...
The table structure of the table where we insert data from the excel and from that table to the main table is also as below


[tblUserTemp]
(
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](75) NULL,
[Password] [nvarchar](200) NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[Processed] [bit] NOT NULL DEFAULT ((0))
)

Thanks,
Naveen
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
You should be able to accomplish this by seting the Maximum Insert Commit Size setting on your OLE DB Destination Fast-Load component.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11028 Visits: 14858
John Rowan (8/10/2009)
You should be able to accomplish this by seting the Maximum Insert Commit Size setting on your OLE DB Destination Fast-Load component.


Duh, why didn't I think of that?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
Jack Corbett (8/10/2009)
John Rowan (8/10/2009)
You should be able to accomplish this by seting the Maximum Insert Commit Size setting on your OLE DB Destination Fast-Load component.


Duh, why didn't I think of that?


That's why we all work together here, right! I've been "duh'd" too many times:-).

Anyway, that is assuming that the OLE DB Destination w/ fast-load is being used.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
Hi,

Adding some more information....

Actually im planning to use an execute sql task so that i can call a stored procedure with that parameters....

The reason im not using the ole db destination is that im having master-detail relationship for these tables...

i.e when im inserting data to tblUsers after inserting say 1000 records for tblUsers ill have to insert the corresponding records ( recently inserted 1000 records) of tblUsers from another table ( say tblUserPersonalDetailsTemp) to the main table ( say tblUserPersonalDetails). ie im having identity property for the primary key of these tables..So inserting a record for tblUsers will generate a identity value..This identity value ( say UserId ) is a foreign key in the table tblUserPersonalDetails.

The value supplied for id from the excel is a temp value just for understanding purpose...So the real id value is generated after insertion of each row as identity value...and i use this identity value as the foreign key column for the other table...

Im not sure whether this can be preformed without using a sp and using a control in ssis....
Please let me know if there is another way..

In short what i need to do..

1) Read the data from different data sheets in a excel file to a temp table...
2) Each sheet represents a table..
3) Insert data from each table in batch to its master tables.


What i tried
------------

1) Read data from the excel file ( data sheet 1 for tblUsers) to a temp table tblUserTemp -- It is a data flow task.
2) Read data from the excel file ( data sheet 2 for tblUserPersonalDetails) to a temp table tblUserPersonalDetailsTemp-- It is also a data flow task.
3)Get all data from the tblUserTemp which are not processed - execute sql task - Using a sp for this..
4) Using a for loop to iterate through resultset...- FOR EACH loop Container
5) Inside the for loop use a execute sql task ( call a sp for the purpose of insertion to the master detail tables.) - Sp written as a transaction..
So each row treated as a transaction...

Limitations
----------

The process seems to be slow as its takes more than an hour to process 30000 records for the tblUser table...

Is there any alternative so that i can increase performance ?


Thanks in advance,
Naveen
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8381 Visits: 19501
Yuk, I don't like the sound of that. If you are bringing all of the details into a temp table first and then processing from there out to master/child tables, what I would do is use the PK on the temp table to help you avoid RBAR updates ....

Add another indexed field to your master table and, when you create your master records, populate this field with the PK from the temp table.

Then, when you create your child records, you can look up the 'proper' FK from the master table via the new temp PK field on the master table. You should be able to do this as a set-based create, vastly speeding things up.

Hope that makes sense.


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

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
John Rowan
John Rowan
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4072 Visits: 4511
Naveen,

And all of this becasue you don't know how to do this in the data flow? How long does it take the data flow to run and populate the 30,000 rows into your temp table? Surely not more than a few seconds, right? I would be the vast majority of the time spent during that hour run is looping through the rows.

Can you tell us how long the data flow portion of you package takes to load the temp tables?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
navenis4u
navenis4u
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 56
Hi,

It just take a few seconds...
If im doing it after running the cache then its takes just 2-3 seconds only..

Thanks,
Naveen
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