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


Why not processing any records further?


Why not processing any records further?

Author
Message
Charmer
Charmer
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1019
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
ThomasRushton
ThomasRushton
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: Moderators
Points: 1823 Visits: 2170
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8959 Visits: 19018
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
Charmer
Charmer
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1019
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
Charmer
Charmer
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1019
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
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8959 Visits: 19018
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
Charmer
Charmer
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1019
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24179 Visits: 37948
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).

Cool
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)
Charmer
Charmer
SSC Eights!
SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)SSC Eights! (836 reputation)

Group: General Forum Members
Points: 836 Visits: 1019
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
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