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
LVL 7
NavicertsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NavicertsAuthor Commented:
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

0
PedroCGDCommented:
Do you tried inside a SSIS package?

Regards,
Pedro
0
NavicertsAuthor Commented:
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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

NavicertsAuthor Commented:
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
0
PedroCGDCommented:
Maybe you dont have sufficient memory to do this job...
try another machine with more capabilities!
Regards,
Pedro
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NavicertsAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.