SSIS Data Flow Transformation stopped

Question

Post reply

SSIS Data Flow Transformation stopped

berto

SSCommitted

Points: 1878
More actions
February 28, 2008 at 7:34 am

#128003

Hello,
I've created several data flow transformation and I found out that one of them behaves strange. When I started it, it changed to yellow and then when I go to Data Flow tab after a while I saw green coluor with number of 8000 rows but when I returned to Control flow view was still yellow. There is no errors and last information from packege execution is:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at PQC_RuleFile, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at PQC_RuleFile, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at PQC_RuleFile, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at PQC_RuleFile, DTS.Pipeline: Execute phase is beginning.
Information: 0x402090DF at PQC_RuleFile, OLE DB Destination [975]: The final commit for the data insertion has started.
Information: 0x402090E0 at PQC_RuleFile, OLE DB Destination [975]: The final commit for the data insertion has ended.
This is big problem for me because after this transformation there are many others which are waiting for this one to finish.
Any ideas?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005

Viewing 6 posts - 1 through 5 (of 5 total)

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

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 1

how many rows do you expect to process? Is 8000 all the data, or is that perhaps just 1 buffer?

how long did you leave it 'running' for?

what destination are you writing too? I've seen problems with flat-file output - esp. if there is antoher process accessing that file.

Chinna SSCertifiable Points: 5408 More actions · Answer 2

hi berto ...did u solved the issue if so plz tell me whats was the issue n how did u overcome that thkz bye

Thanks,
Chinna

Its the Journey which gives you Happiness not the Destination-- Dan Millman

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 3

I had a similar issue, and after playing around with the DefaultBufferSize and DefaultBufferMaxRows (in my case, increasing them), this has resovled the issue. I should state I am running on a machine with plenty of RAM, and after these changes my process completes, and runs faster than before, BUT uses quite a bit more RAM ( I peaked at 1.8GB - but I am reading almost 5 million rows from different sources, and writing over 15 millions rows to my destinations)

berto SSCommitted Points: 1878 More actions · Answer 4

Regan Galbraith (2/28/2008)
how many rows do you expect to process? Is 8000 all the data, or is that perhaps just 1 buffer?

I expect 8680. I've got 8623.

Regan Galbraith (2/28/2008)
how long did you leave it 'running' for?

For couple of minutes. It's strange because all other tasks were running fast without problems.

During execution I found interesting thing. When I went to Data Flow Tab only OLE DB Source was yellow and then after a while both we yellow. I see that today is different. They are not green afterwards and stay yellow and additionally in Output I have only:

SSIS package "Package.dtsx" starting.

Information: 0x4004300A at PQC_RuleFile, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at PQC_RuleFile, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at PQC_RuleFile, DTS.Pipeline: Pre-Execute phase is beginning.

Information: 0x4004300C at PQC_RuleFile, DTS.Pipeline: Execute phase is beginning.

Regan Galbraith (2/28/2008)
what destination are you writing too? I've seen problems with flat-file output - esp. if there is antoher process accessing that file.

I write from SQL Server 2000 to SQL Server 2005.

Details about table:

CREATE TABLE [dbo].[PQC_RuleFile](

[Ref] [int] IDENTITY(1,1) NOT NULL,

[Type] [char](2) NOT NULL,

[RuleFileName] [varchar](30) NOT NULL,

[Description] [varchar](100) NULL,

[DraftStatus] [UT_BOOLEAN] NOT NULL,

[DeletedStatus] [UT_BOOLEAN] NOT NULL,

[ValidFrom] [datetime] NOT NULL,

[ValidTo] [datetime] NULL,

[Definition] [ntext] NULL,

[CreationDate] [datetime] NOT NULL,

[CreatedBy] [varchar](20) NOT NULL,

[LastUpdate] [datetime] NOT NULL,

[LastModifiedBy] [varchar](20) NOT NULL,

[DeletionDate] [datetime] NULL,

[DeletedBy] [varchar](20) NULL,

[SampleFile] [UT_BOOLEAN] NOT NULL,

[WDescription] [nvarchar](100) NULL,

CONSTRAINT [PK_PQC_RuleFile] PRIMARY KEY CLUSTERED

(

[Ref] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Edit

Just short additional info:

So currently behaviour is different. I didn't get:

Information: 0x402090DF at PQC_RuleFile, OLE DB Destination [975]: The final commit for the data insertion has started.

Information: 0x402090E0 at PQC_RuleFile, OLE DB Destination [975]: The final commit for the data insertion has ended.

and I have no data in destination table.

Edit 2

I was patient and after 17 mins it went through. So it runs but very very slowly.

Any ideas why?

Edit 3

I keep you updated 🙂

I checked processes and found out that mentioned process has status suspended:

SPID kpid blocked waittype waittime lastwaittype opentran status cmd

97 4236 0 0x0063159515ASYNC_NETWORK_IO 2suspended BULK INSERT

MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005

bluepaperbags SSCommitted Points: 1738 More actions · Answer 5

bluepaperbags

SSCommitted

Points: 1738

April 14, 2009 at 11:16 am

#977456

Thank, it helped alot.