<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
Why not processing any records further?
Why not processing any records further?
Rate Topic
Display Mode
Topic Options
Author
Message
Charmer
Charmer
Posted Friday, August 17, 2012 5:38 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672,
Visits: 725
Hi Friends,
I have come up with a weird problem....what i do is very straight forward process...
i have a table containing 4.2 million records and there is a column name called SeqNo which is '1' (for all the rows)....
what i am trying to do is inserting '0' as SeqNo into the table..
source as well as destination is same table....
4.2 million records having Sequence number 1 and now i again try to insert all the records with Sequence number 0..
In the data flow , OLEDB starts to process 50,000 records but it is not going further....it remains still....i don't know what is the problem and why it is not processing further.....the tasks are not becoming red ,and it still maintaing yellow color since it is not processing further for more than one hour....
Help me firends....
Thanks,
Charmer
Post #1346487
ThomasRushton
ThomasRushton
Posted Friday, August 17, 2012 6:15 AM
SSC Eights!
Group: General Forum Members
Last Login: 2 days ago @ 6:30 AM
Points: 966,
Visits: 372
Are there any locks in evidence on the source or destination tables?
It might be worth trying to reduce the transaction batch size - the number of updates / inserts that are queued before being commited.
Post #1346498
ChrisM@Work
ChrisM@Work
Posted Friday, August 17, 2012 6:40 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602,
Visits: 10,950
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1346510
Charmer
Charmer
Posted Friday, August 17, 2012 7:06 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672,
Visits: 725
ThomasRushton (8/17/2012)
Are there any locks in evidence on the source or destination tables?
It might be worth trying to reduce the transaction batch size - the number of updates / inserts that are queued before being commited.
source and destination is same table...
there are no locks....
Thanks,
Charmer
Post #1346519
Charmer
Charmer
Posted Friday, August 17, 2012 7:08 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672,
Visits: 725
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.
yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....
Thanks,
Charmer
Post #1346520
ChrisM@Work
ChrisM@Work
Posted Friday, August 17, 2012 7:14 AM
SSCertifiable
Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602,
Visits: 10,950
Charmer (8/17/2012)
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.
yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....
Why not do it by the simplest and most transparent method available to you?
INSERT INTO Yourtable (columnlist)
SELECT Columnlist FROM Yourtable
Hardcode
seqno
in the SELECT list.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.”
- Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read
this article
.
Understanding and using APPLY, (I)
and
(II)
Paul White
Hidden RBAR: Triangular Joins
/
The "Numbers" or "Tally" Table: What it is and how it replaces a loop
Jeff Moden
Exploring Recursive CTEs by Example
Dwain Camps
Post #1346526
Charmer
Charmer
Posted Friday, August 17, 2012 7:50 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672,
Visits: 725
ChrisM@Work (8/17/2012)
Charmer (8/17/2012)
ChrisM@Work (8/17/2012)
Are you inserting new rows into a table or updating existing rows? It's not clear from your description.
yes i am inserting new rows by taking all the rows which is already inserted with modified SeqNo....
Why not do it by the simplest and most transparent method available to you?
INSERT INTO Yourtable (columnlist)
SELECT Columnlist FROM Yourtable
Hardcode
seqno
in the SELECT list.
but i have to do in SSIS only....that's why...if we do in data flow task, it would be more tranparent right? or else i have to use SQL task it not works...
Thanks,
Charmer
Post #1346550
Lynn Pettis
Lynn Pettis
Posted Friday, August 17, 2012 8:38 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 21,588,
Visits: 27,384
Two questions.
First, why are you copying the records with a different sequence no (really just curious about the business requirement)
Second, why does it have to be done in SSIS? A simple T-SQL statement can do it just as easily. The only thing that bothers me is that you are doubling the number of records in the table, and that could explode you t-log on the table.
There are ways to manage this possibility, but it means knowing more about the data and the table in particular (unique or primary keys to be exact).
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #1346584
Charmer
Charmer
Posted Friday, August 17, 2012 8:42 AM
Say Hey Kid
Group: General Forum Members
Last Login: Monday, February 18, 2013 2:08 AM
Points: 672,
Visits: 725
Lynn Pettis (8/17/2012)
Two questions.
First, why are you copying the records with a different sequence no (really just curious about the business requirement)
Second, why does it have to be done in SSIS? A simple T-SQL statement can do it just as easily. The only thing that bothers me is that you are doubling the number of records in the table, and that could explode you t-log on the table.
There are ways to manage this possibility, but it means knowing more about the data and the table in particular (unique or primary keys to be exact).
i am sorry Lynn, i don't know why that need to do...but this is what my manger ordered me to do so..
we are going to work on the production server ....my higher authorities does not want to look any queries over there....they want everything under SSIS package(only the .dtsx file to be executed)....
that is why i am working on it now...
FYI...
the main reason behind this is , since we are dealing with million of rows in all the tables....we don't want to load any tables using direct T-SQL statements...becaue due to huge data, the sql server needs to be opened for more than a day....if some one accidently closed the server, then everything will be blown off....so they don't want anything to be transparent...so they came up with SSIS...
Thanks,
Charmer
Post #1346588
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
