Why not processing any records further?

Question

Why not processing any records further?

Charmer

SSChampion

Points: 14903
More actions
August 17, 2012 at 5:38 am

#266196

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

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.

Thomas Rushton
blog: https://thelonedba.wordpress.com

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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

Charmer SSChampion Points: 14903 More actions · Answer 3

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 SSChampion Points: 14903 More actions · Answer 4

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 SSC Guru Points: 186127 More actions · Answer 5

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

Charmer SSChampion Points: 14903 More actions · Answer 6

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 SSC Guru Points: 442467 More actions · Answer 7

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).

Charmer SSChampion Points: 14903 More actions · Answer 8

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