asked on

SSIS Stalling

Hello,

I used the export/import wizard to create a package that uses a view to load a text file with fixed width format. The view has ~75k rows and takes 15 seconds to fully load in enterprise manager (sql server 2005). The text file is located on the same server. For some reason I get stuck on "executing" (have waited 20+ minutes). I have created a different package with the import/export wizard on a different table (same method) with ~1000 rows and it worked instantly.

Not sure how to troubleshoot this problem?

Thanks

-Navicerts
untitled.JPG

Navicerts

ASKER

Additional Information: If I hit "Stop" it never actually stops (have waited up to 15 minutes). I need to stop the sql service and it will error out to get it to stop. Here is the sql (if that matters).

SELECT
		Str((Cast(Year(wf.[Hatch Date]) As Varchar) + Cast(DatePart("y",wf.[Hatch Date]) As Varchar)), 7, 0) As HDJUL,
		Str((Case When wf.[Sex Code] = 'F' Then 2 Else 1 End), 1, 0) As SexCod,
		Str(wf.[Bird ID], 10, 0) As ANIMAL,
 
		Case When one.[RawDate] > '6/25/2007' AND wf.[Line Number] = '3' AND wf.[Sex Code] = 'F' Then
			Str(0,4,0) 
		Else
			Str(Case When [Weight] Is Null Then 0 Else [Weight] End,4,0) 
		End As BWT1,
		--Str(Case When [Weight] Is Null Then 0 Else [Weight] End,4,0) As BWT1,
 
		Case When one.[RawDate] > '6/25/2007' AND wf.[Line Number] = '3' AND wf.[Sex Code] = 'F' Then
			Str(Case When [Weight] Is Null Then 0 Else [Weight] End,4,0) 
		Else
			Str(Case When [Out of Cage Weight] Is Null Then 0 Else [Out of Cage Weight] End,4,0) 
		End As BWT2,
		--Str(Case When [Out of Cage Weight] Is Null Then 0 Else [Out of Cage Weight] End,4,0) As BWT2,
		
		Case When one.[RawDate] > '6/25/2007' AND wf.[Line Number] = '3' AND wf.[Sex Code] = 'F' Then
			Str(Case When one.[Body Conformation] Is Null Then 0 Else one.[Body Conformation] End,2,0) 
		Else
			Str(Case When two.[Body Conformation] Is Null Then 0 Else two.[Body Conformation] End,2,0) 
		End As BC2,
 
 
		Str(Case When o.[Oximeter] Is Null Then 0 Else o.[Oximeter] End,2,0) As OXI,
		Str(Case When FCR.[FCR] Is Null Then 0 Else FCR.[FCR] End,7,2) As FCR,
		Str(Case When [GoodEgg] Is Null Then 0 Else [GoodEgg] End, 3, 0) As [GoodEgg]
FROM
		WingbandFlock As wf
LEFT JOIN
		Blup_Line3_Pedigree As b
ON
		wf.[Bird ID] = b.[Bird ID]
LEFT JOIN
		[Process (Out of Cage)] As two
ON
		b.[Bird ID] = two.[Bird ID]
LEFT JOIN
		[Process (In Cage, Floor)] As one
ON
		b.[Bird ID] = one.[Bird ID]
Left Join
		WingbandFlock As wb
ON
		wf.[Mother ID] = wb.[Bird ID]
LEFT JOIN
		Oximeter As o
ON
		wf.[Bird ID] = o.[Bird ID]
LEFT JOIN
		Line3Blup As l
ON
		wf.[Bird ID] = l.ANIMAL
LEFT JOIN
		Blup_FCR As FCR
ON
		wf.[Bird ID] = FCR.[Bird ID]
Where
		wf.[Line Number] = 3
AND		wf.[Hatch Date] <= (SELECT Max(wf.[Hatch Date]) FROM Process As p INNER JOIN WingbandFlock As wf ON wf.[Bird ID] = p.[Bird ID] WHERE p.[Process ID] in (7,6) AND wf.[Line Number] = 3)
AND		wb.[Hatch Date] Is Not Null
AND		(wf.[Mother ID] Is Not Null OR wf.[Father ID] Is Not Null)
AND		(
			one.[Weight] Is Not Null 
		OR	[Out of Cage Weight] Is Not Null 
		OR	two.[Body Conformation] Is Not Null 
		OR	o.[Oximeter] Is Not Null 
		OR	FCR.[FCR] Is Not Null
		OR	[GoodEgg] > 0
		)

Open in new window

PedroCGD

Do you tried inside a SSIS package?

Regards,
Pedro

Navicerts

ASKER

Not sure what you mean?

I have opened the package via business intelligence studio and I can view it and "test connection". However, the "play" button is not available when I open the package so I didn't run it from there.

PedroCGD

You can run/excute an SSIS package inside BIDS
check this link
http://books.google.pt/books?id=N7tzbajkNccC&pg=PA59&lpg=PA59&dq=execute+SSIS+package+in+BIDS&source=bl&ots=NDcJ-A3Rkh&sig=pV60rrS-Xq4P_jDAn7boAViv2s0&hl=pt-PT&ei=A6seSuzXAceE_Qb9uJ3NBA&sa=X&oi=book_result&ct=result&resnum=10#PPA61,M1

Navicerts

ASKER

Ah Ok, that got me to the point where I can open the package and attempt to run it in a Integration Services Project. The result seems the same so far with no additional trouble shooting options.
untitled.JPG

ASKER CERTIFIED SOLUTION

PedroCGD

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Navicerts

ASKER

Never found the problem. I guess it could have been memory because when I use a SP to port the info to a table and then export it the results are good. However according the to the server task manager there is a ton of memory free when the job is stalling.